GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

remove temporary table

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • remove temporary table

    I have an issue with a query. This is a stripped down version of it that gets right to the problem

    Slow and creating the temp table


    mysql> EXPLAIN SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS entryID,title FROM friends_test INNER JOIN entries ON userLink = userid WHERE friendLink =2 ORDER BY entryID -> ;+----+-------------+--------------+------+---------------------+------------+---------+---------------------------------+------+---------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------------+------+---------------------+------------+---------+---------------------------------+------+---------------------------------+| 1 | SIMPLE | friends_test | ref | userLink,friendLink | friendLink | 3 | const | 491 | Using temporary; Using filesort | | 1 | SIMPLE | entries | ref | userid | userid | 4 | photoblog.friends_test.userLink | 11 | Using where | +----+-------------+--------------+------+---------------------+------------+---------+---------------------------------+------+---------------------------------+


    now if i change friendLink=2 to userLink=2 there is a BIG difference.


    mysql> EXPLAIN SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS entryID,title FROM friends_test INNER JOIN entries ON userLink = userid WHERE userLink =2 ORDER BY entryID ;+----+-------------+--------------+------+---------------+----------+---------+-------+------+-----------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------------+------+---------------+----------+---------+-------+------+-----------------------------+| 1 | SIMPLE | entries | ref | userid | userid | 4 | const | 62 | Using where; Using filesort | | 1 | SIMPLE | friends_test | ref | userLink | userLink | 3 | const | 491 | Using index | +----+-------------+--------------+------+---------------+----------+---------+-------+------+-----------------------------+

    The query runs almost 100x faster the the one above and no temp table created.

    I have been pulling out hairs over this issue.

    Here is my friends_test table


    mysql> describe friends_test;+------------+--------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+------------+--------------+------+-----+---------+----------------+| friendID | mediumint(8) | NO | PRI | NULL | auto_increment | | userLink | mediumint(8) | NO | MUL | NULL | | | friendLink | mediumint(8) | NO | MUL | NULL | | | status | tinyint(1) | NO | | 1 | | +------------+--------------+------+-----+---------+----------------+4 rows in set (0.26 sec)mysql> SHOW INDEX FROM friends_test;+--------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |+--------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| friends_test | 0 | PRIMARY | 1 | friendID | A | 78392 | NULL | NULL | | BTREE | NULL | | friends_test | 1 | userLink | 1 | userLink | A | 7839 | NULL | NULL | | BTREE | NULL | | friends_test | 1 | friendLink | 1 | friendLink | A | 7839 | NULL | NULL | | BTREE | NULL | +--------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+Here it is from my entries tablemysql> SHOW INDEX FROM entries;+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| entries | 0 | PRIMARY | 1 | entryid | A | 188124 | NULL | NULL | | BTREE | NULL | | entries | 1 | userid | 1 | userid | A | 17102 | NULL | NULL | YES | BTREE | NULL | | entries | 1 | date | 1 | date | A | 2090 | NULL | NULL | | BTREE | NULL | | entries | 1 | created | 1 | created | A | 188124 | NULL | NULL | YES | BTREE | NULL | | entries | 1 | ts | 1 | ts | A | 188124 | NULL | NULL | YES | BTREE | NULL | | entries | 1 | title | 1 | title | NULL | 188124 | NULL | NULL | YES | FULLTEXT | NULL | | entries | 1 | title | 2 | text | NULL | 188124 | NULL | NULL | YES | FULLTEXT | NULL | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

  • #2
    When you have

    entries ON userLink = userid WHERE userLink =2

    MySQL can convert it to

    userLink=2, userid=2

    Which allows different execution path in which case entries tables comes first and as you sort by column from this table it allows to avoid temporary table. When you sort by second table in join it requires temporary table.

    if you would have userid,entryId index on entries you would get rid of filesort too.

    Comment


    • #3
      So are you saying there is no way to get rid of the temp table creation? I added the index on user,entryid

      I still have the temp table and filesort


      mysql> EXPLAIN SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS entryID,title FROM entries INNER JOIN friends_test ON friendLink = userid AND userLink=2 ORDER BY entryID ;+----+-------------+--------------+------+---------------+----------+---------+-----------------------------------+------+----------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------------+------+---------------+----------+---------+-----------------------------------+------+----------------------------------------------+| 1 | SIMPLE | friends_test | ref | userLink | userLink | 3 | const | 1 | Using index; Using temporary; Using filesort | | 1 | SIMPLE | entries | ref | userid_2 | userid_2 | 4 | photoblog.friends_test.friendLink | 4 | Using where | +----+-------------+--------------+------+---------------+----------+---------+-----------------------------------+------+----------------------------------------------+2 rows in set (0.00 sec)




      mysql> SHOW INDEX FROM entries;+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| entries | 0 | PRIMARY | 1 | entryid | A | 8 | NULL | NULL | | BTREE | | | entries | 1 | date | 1 | date | A | 8 | NULL | NULL | | BTREE | | | entries | 1 | created | 1 | created | A | 8 | NULL | NULL | YES | BTREE | | | entries | 1 | category | 1 | category | A | 2 | NULL | NULL | YES | BTREE | | | entries | 1 | modified | 1 | modified | A | 8 | NULL | NULL | YES | BTREE | | | entries | 1 | userid_2 | 1 | userid | A | 2 | NULL | NULL | YES | BTREE | | | entries | 1 | userid_2 | 2 | entryid | A | 8 | NULL | NULL | | BTREE | | | entries | 1 | title | 1 | title | NULL | 1 | NULL | NULL | YES | FULLTEXT | | | entries | 1 | title | 2 | text | NULL | 1 | NULL | NULL | YES | FULLTEXT | | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

      Comment


      • #4
        I'm not saying that I'm just saying why there is temporary table )

        You need Entires table to be first in join order one to avoid temporary table.

        However as the clause you have only limits rows from friends_table you may have hard time doing so.

        You may split the query though and do one select and second query on entries table with IN clause

        Comment


        • #5
          I got the entries table to be shown first but it still doesn't speed anything up

          Also I have used an IN() but if a member has a lot of friends the query is very slow.

          Here is a small sql dump of an example table if it helps

          http://zcentric.com/db.sql

          Comment

          Working...
          X