| 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 |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
|
|
|
| Re: remove temporary table [message #1590 is a reply to message #1582 ] |
Thu, 16 August 2007 06:51  |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
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.
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
| Re: remove temporary table [message #1599 is a reply to message #1582 ] |
Thu, 16 August 2007 08:43 |
mzupan
Messages: 4
Registered: August 2007 |
Junior Member |
|
|
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 | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
[Updated on: Thu, 16 August 2007 08:44]
|
|
|
|
| Re: remove temporary table [message #1700 is a reply to message #1631 ] |
Mon, 27 August 2007 13:53 |
mzupan
Messages: 4
Registered: August 2007 |
Junior Member |
|
|
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
|
|
|
Members
Search
Help
Register
Login
Home
