remove temporary table

MySQL & MariaDB Other MySQL® Questions
1

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.

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

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