It is bit panic to see ‘Using filesort‘ from the extra field when one runs a explain of select query on MySQL server. At times it is bit annoy why MySQL optimizer does not avoid this as you can see from the following cases…
This is the information (Extra field) that scares a lot for many users from Explain output:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
id: 1 select_type: SIMPLE table: t1 type: index possible_keys: NULL key: col1key key_len: 5 ref: NULL rows: 1000000 Extra: Using index; Using temporary; Using filesort |
Lets consider the following simple table (MyISAM in this case, does not matter what storage engine it is) populated with distinct random 1000000 (1M) rows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
Create Table: CREATE TABLE `testtab` ( `col1` int(11) DEFAULT NULL, `col2` text, `col3` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`col3`), KEY `col1key` (`col1`) ) ENGINE=MyISAM mysql> select count(*) from testtab; +----------+ | count(*) | +----------+ | 1000000 | +----------+ 1 row in set (0.00 sec) mysql> show table status like 'testtab'\G *************************** 1. row *************************** Name: testtab Engine: MyISAM Version: 10 Row_format: Dynamic Rows: 1000000 Avg_row_length: 88 Data_length: 88000000 Max_data_length: 281474976710655 Index_length: 26619904 Data_free: 0 Auto_increment: 1000001 Create_time: 2007-11-29 20:32:19 Update_time: 2007-11-29 20:35:03 Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) |
And lets run a explain for a simple select query from the above table:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
mysql> explain select * from testtab where col1 > 50000 AND col1 < 150000 order by col3\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: testtab type: range possible_keys: col1key key: col1key key_len: 5 ref: NULL rows: 91609 Extra: Using where; Using filesort 1 row in set (0.00 sec) mysql> EXPLAIN SELECT * FROM testtab WHERE col1 > 50000 AND col3 < 20000 ORDER BY col3\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: testtab type: range possible_keys: PRIMARY,col1key key: PRIMARY key_len: 4 ref: NULL rows: 21209 Extra: Using where 1 row in set (0.00 sec) mysql> EXPLAIN SELECT * FROM testtab WHERE col3 < 20000 AND col1 > 50000 ORDER BY col3\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: testtab type: range possible_keys: PRIMARY,col1key key: PRIMARY key_len: 4 ref: NULL rows: 21209 Extra: Using where 1 row in set (0.00 sec) |
The only difference from the first query (which has filesort) to that of other two are having the col3 in the where clause which appears in the ORDER BY clause…. and another example…
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
mysql> EXPLAIN SELECT * FROM testtab WHERE col3 < 20000 order by col3\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: testtab type: range possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 21209 Extra: Using where 1 row in set (0.00 sec) mysql> EXPLAIN SELECT * FROM testtab WHERE col3 < 20000 order by col1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: testtab type: range possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 21209 Extra: Using where; Using filesort 1 row in set (0.00 sec) mysql> EXPLAIN SELECT * FROM testtab WHERE col1 < 20000 order by col1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: testtab type: range possible_keys: col1key key: col1key key_len: 5 ref: NULL rows: 19892 Extra: Using where 1 row in set (0.00 sec) mysql> EXPLAIN SELECT * FROM testtab WHERE col1 < 20000 order by col3\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: testtab type: range possible_keys: col1key key: col1key key_len: 5 ref: NULL rows: 19892 Extra: Using where; Using filesort 1 row in set (0.00 sec) |
The above indicates that any column which appears in the ORDER BY clause should also appear in WHERE clause in order to avoid filesort. Consider yet another query for the same table using a self join this time…
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
mysql> explain select t1.col1, t2.col2 from testtab t1 left outer join testtab t2 on (t1.col1=t2.col1) order by t2.col1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: index possible_keys: NULL key: col1key key_len: 5 ref: NULL rows: 1000000 Extra: Using index; Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t2 type: ref possible_keys: col1key key: col1key key_len: 5 ref: test.t1.col1 rows: 11 Extra: 2 rows in set (0.00 sec) mysql> explain select t1.col1, t2.col2 from testtab t1 left outer join testtab t2 on (t1.col1=t2.col1) order by t1.col1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: index possible_keys: NULL key: col1key key_len: 5 ref: NULL rows: 1000000 Extra: Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t2 type: ref possible_keys: col1key key: col1key key_len: 5 ref: test.t1.col1 rows: 11 Extra: 2 rows in set (0.00 sec) |
The only difference from the above case is “Order by column is from the left side of the join clause”… and here is yet another example..
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 |
mysql> explain select t1.col1, t2.col2 from testtab t1 left outer join testtab t2 on (t1.col1=t2.col1) order by t2.col1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: index possible_keys: NULL key: col1key key_len: 5 ref: NULL rows: 1000000 Extra: Using index; Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t2 type: ref possible_keys: col1key key: col1key key_len: 5 ref: test.t1.col1 rows: 11 Extra: 2 rows in set (0.00 sec) mysql> explain select t1.col1, t2.col2 from testtab t1 right outer join testtab t2 on (t1.col1=t2.col1) order by t2.col1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t2 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1000000 Extra: Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t1 type: ref possible_keys: col1key key: col1key key_len: 5 ref: test.t2.col1 rows: 11 Extra: Using index 2 rows in set (0.00 sec) mysql> explain select t1.col1, t2.col2 from testtab t2 right outer join testtab t1 on (t1.col1=t2.col1) order by t1.col1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: index possible_keys: NULL key: col1key key_len: 5 ref: NULL rows: 1000000 Extra: Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t2 type: ref possible_keys: col1key key: col1key key_len: 5 ref: test.t1.col1 rows: 11 Extra: 2 rows in set (0.00 sec) mysql> explain select t1.col1, t2.col2 from testtab t1 left outer join testtab t2 on (t1.col1=t2.col1) order by t1.col1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: index possible_keys: NULL key: col1key key_len: 5 ref: NULL rows: 1000000 Extra: Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t2 type: ref possible_keys: col1key key: col1key key_len: 5 ref: test.t1.col1 rows: 11 Extra: 2 rows in set (0.00 sec) |
So, the conclussion:
- You can avoid the filesort by making order by column appear in the where clause
- When using join, make sure the left side join table column is used in the ORDER BY clause or change the join type
There are so many other ways to avoid, and the complete order by optimization can be found from the MySQL documentation.
Venu,
your suggestions could work in some cases, but they will not work in general. Let me show you some simple examples.
1. You cannot avoid filesort by simply including the order by column(s) into WHERE:
SELECT col1 FROM testtab WHERE col1 > 1 OR col3 > 1 ORDER BY col3;
2. You not necessarily need to have ORDER BY column(s) in the WHERE clause:
ALTER TABLE testtab ADD INDEX ix1 (col1, col3);
SELECT col3 FROM testtab WHERE col1=1 ORDER BY col3;
Also, storage engine does matter… 🙂
BR
Hi,
Thanks for the information.
Nice to know about you.
All the Best
Ramakrishna
explain SELECT SQL_CALC_FOUND_ROWS ops.node.node_id, ops.node.name AS nodename, ops.node.ytag, ops.node.status, ops.node.site_id, ops.node.parent_id, ops.site.name AS sitename, ops.property.prop_id, ops.property.name AS property_name, c.code AS country_code, d1.name AS node_type, ops.node.locroom, ops.node.loccage, ops.node.locarea, ops.node.locrow, ops.node.locrack, ops.node.locside, ops.node.locshelf, ops.node.bootbox_id, ops.node.bport, ops.node.bmodule, ops.node.bplug, ops.node.bootbox2_id, ops.node.bport2, ops.node.bmodule2, ops.node.bplug2, d2.name AS osname, ops.os.version AS osver, ops.node.racksize, ops.node.serialno, ops.node.console_id, ops.node.cport, ops.node.model_id, ops.model.name AS model_name, model.company_id, ops.company.name AS com_name, GROUP_CONCAT(DISTINCT ops.mac.mac ORDER BY ops.mac.mac SEPARATOR ‘|’) AS mac, ops.node.c_time AS bornondate, UNIX_TIMESTAMP(ops.node.m_time) AS atime, FROM_UNIXTIME(ops.node.c_time) AS c_time, FROM_UNIXTIME(UNIX_TIMESTAMP(ops.node.m_time)) AS m_time, ops.node.ponumber, bp.name backplane, ops.node.pdu, ops.node.notes, nswitch.name as mswitchname, ops.node.sport as msport, nvs.validation_status_id, d3.name AS validation_status_name, d3.label AS validation_status FROM ops.node LEFT JOIN ops.property ON (node.prop_id = property.prop_id) LEFT JOIN ops.country c ON property.country_id = c.country_id LEFT JOIN ops.site ON (node.site_id = site.site_id) LEFT JOIN ops.dictionary AS d1 ON (node.type_id = d1.dict_id) LEFT JOIN ops.os ON (node.os_id = os.os_id) LEFT JOIN ops.dictionary AS d2 ON (os.dict_id = d2.dict_id) LEFT JOIN ops.model ON (node.model_id = model.model_id) LEFT JOIN ops.mac ON (mac.node_id = node.node_id) LEFT JOIN ops.company ON (model.company_id = company.company_id) LEFT JOIN ops.node AS nswitch ON (ops.node.netswitch_id = nswitch.node_id) LEFT JOIN ops.nodeValidationStatus AS nvs ON (node.node_id = nvs.node_id) LEFT JOIN ops.dictionary AS d3 ON (nvs.validation_status_id = d3.dict_id) LEFT JOIN ops.backplane as bp on (bp.backplane_id = node.backplane_id) WHERE ops.node.name ” AND ops.node.status = ‘active’ GROUP BY ops.node.node_id ORDER BY node.name LIMIT 0, 25;
+—-+————-+———-+——–+——————-+———–+———+——————————+——+———————————————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———-+——–+——————-+———–+———+——————————+——+———————————————-+
| 1 | SIMPLE | node | range | name | name | 257 | NULL | 4 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | property | eq_ref | PRIMARY | PRIMARY | 4 | ops.node.prop_id | 1 | |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | ops.property.country_id | 1 | |
| 1 | SIMPLE | site | eq_ref | PRIMARY | PRIMARY | 4 | ops.node.site_id | 1 | |
| 1 | SIMPLE | d1 | eq_ref | PRIMARY | PRIMARY | 4 | ops.node.type_id | 1 | |
| 1 | SIMPLE | os | eq_ref | PRIMARY | PRIMARY | 4 | ops.node.os_id | 1 | |
| 1 | SIMPLE | d2 | eq_ref | PRIMARY | PRIMARY | 4 | ops.os.dict_id | 1 | |
| 1 | SIMPLE | model | eq_ref | PRIMARY | PRIMARY | 4 | ops.node.model_id | 1 | |
| 1 | SIMPLE | mac | ref | node_id,node_id_2 | node_id_2 | 4 | ops.node.node_id | 18 | |
| 1 | SIMPLE | company | eq_ref | PRIMARY | PRIMARY | 4 | ops.model.company_id | 1 | |
| 1 | SIMPLE | nswitch | eq_ref | PRIMARY | PRIMARY | 4 | ops.node.netswitch_id | 1 | |
| 1 | SIMPLE | nvs | ref | node_id_2,node_id | node_id | 5 | ops.node.node_id | 15 | |
| 1 | SIMPLE | d3 | eq_ref | PRIMARY | PRIMARY | 4 | ops.nvs.validation_status_id | 1 | |
| 1 | SIMPLE | bp | eq_ref | PRIMARY | PRIMARY | 4 | ops.node.backplane_id | 1 | |
+—-+————-+———-+——–+——————-+———–+———+——————————+——+———————————————-+
I chnaged the orader by column with let joined table’s field but does not works for me..still taking file sorting.
[…] also https://venublog.com/2007/11/29/mysql-how-to-avoid-filesort/ Categories: MySQL Tags: Comments (0) Trackbacks (0) Leave a comment […]
[…] Reference […]
MySQL – Simple ways to avoid filesort – https://t.co/hdQZM3RV
Could you please help me to avoid filesort in the following query
SELECT DISTINCT(storages.id),
IF(admin_approve = 1 OR storage_free_auctions.user_id = 167 OR ISNULL(storage_free_auctions.user_id),
auction_date, NULL) AS auction_date1 FROM user_list_storage
JOIN storages ON
storages.id = user_list_storage.storage_id
JOIN states ON
states.id = storages.state
LEFT JOIN storage_auctions
ON storage_auctions.id= (
SELECT storage_auctions.id FROM storage_auctions LEFT JOIN storage_free_auctions ON storage_free_auctions.storage_auctions_id=storage_auctions.id
WHERE storage_auctions.storage_id = storages.id AND storage_auctions.status = ‘Active’ AND (ISNULL(storage_free_auctions.user_id)
OR admin_approve = 1
OR storage_free_auctions.user_id = 167)
AND CONCAT(auction_date,’ ‘,start_time) >= CURDATE() ORDER BY auction_date ASC LIMIT 1)
LEFT JOIN storage_free_auctions ON storage_free_auctions.storage_auctions_id=storage_auctions.id
LEFT JOIN storage_auction_units ON storage_auction_units.storage_auction_id = storage_auctions.id
AND storage_auction_units.status=’Active’ WHERE storages.storage_status = ‘Active’
AND user_list_storage.user_id = 167 AND user_list_storage.user_list_id = 3
AND (storage_free_auctions.user_id = 167
OR admin_approve = 1
OR ISNULL(storage_free_auctions.user_id)
OR user_list_storage.user_id = 167)
GROUP BY storages.id
order by auction_date1
First row of the explain result is as follows.
id : 1
Select_type : PRIMARY
Table : user_list_storage
Type : ref possible_keys : idx_user_list_id,idx_storage_id,idx_user_id key : idx_user_id key_len : 5 ref : const
rows : 64
Extra : Using where; Using temporary; Using filesort