# remove temporary table **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [August 9, 2007, 12:01pm UTC](https://forums.percona.com/t/remove-temporary-table/410 "2007-08-09T12:01:44Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![mzupan](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/mzupan/32/761_2.png) [@mzupan](https://forums.percona.com/u/mzupan)\ **Post date:** [August 9, 2007, 12:01pm UTC](https://forums.percona.com/t/remove-temporary-table/410/1 "2007-08-09T12:01:44Z")
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 | ±--------±-----------±---------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------+ ---
**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\ **Post date:** [August 16, 2007, 5:21am UTC](https://forums.percona.com/t/remove-temporary-table/410/2 "2007-08-16T05:21:14Z")
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. ---
**Author:** ![mzupan](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/mzupan/32/761_2.png) [@mzupan](https://forums.percona.com/u/mzupan)\ **Post date:** [August 16, 2007, 7:13am UTC](https://forums.percona.com/t/remove-temporary-table/410/3 "2007-08-16T07:13:11Z")
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 | | ±--------±-----------±---------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------+ ---
**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\ **Post date:** [August 16, 2007, 10:58am UTC](https://forums.percona.com/t/remove-temporary-table/410/4 "2007-08-16T10:58:19Z")
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