MySQL Performance: MySQL => remove temporary table

remove temporary table [message #1582]

Thu, 09 August 2007 13:31

mzupan
Messages: 4
Registered: August 2007

Junior Member

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 table

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         |      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    | 
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Report message to a moderator