| Bug #37053 | Optimizer unnecessarily uses temporary and filesort with ORDER BY | ||
|---|---|---|---|
| Submitted: | 29 May 2008 4:11 | Modified: | 16 Sep 2008 14:33 |
| Reporter: | Allan Packer | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 4.1, 5.0, 5.1,6.0.5 | OS: | Any (Linux, Solaris) |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
| Tags: | optimizer filesort temporary order by | ||
[31 May 2008 18:37]
Sveta Smirnova
Thank you for the report. Please provide access to test environment or dump of the affected tables.
[11 Jun 2008 18:35]
Sveta Smirnova
Thank you for the provided data. Verified as described.
[8 Jul 2008 17:54]
Sveta Smirnova
Platform-specific: Linux version uses filesort with both queries.
[9 Jul 2008 20:58]
Sveta Smirnova
Output with version 5.0.66: $mysql50 bug37053_orig Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 38 Server version: 5.0.66-debug Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> explain extended SELECT t_id, t_dts, st_name, tt_name, t_s_symb, t_qty, t_exec_name, t_chrg, s_name, ex_name FROM trade straight_join status_type straight_join trade_type straight_join security straight_join exchange force index (PRIMARY) WHERE t_ca_id = 43000031857 AND st_id = t_st_id AND tt_id = t_tt_id AND s_symb = t_s_symb AND ex_id = s_ex_id ORDER BY t_dts DESC LIMIT 50; +----+-------------+-------------+--------+-----------------------------------------------------------------------+-----------------+---------+--------------------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+--------+-----------------------------------------------------------------------+-----------------+---------+--------------------------------+------+----------------------------------------------+ | 1 | SIMPLE | trade | range | idx_t_ca_id_dts,idx_t_s_symb_dts,t_st_id,t_tt_id,idx_t_ca_id_dts_desc | idx_t_ca_id_dts | 5 | NULL | 2182 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | status_type | index | PRIMARY,idx_st_id_name | idx_st_name_id | 16 | NULL | 4 | Using where; Using index | | 1 | SIMPLE | trade_type | eq_ref | PRIMARY,idx_tt_id_name | PRIMARY | 3 | bug37053_orig.trade.t_tt_id | 1 | | | 1 | SIMPLE | security | eq_ref | PRIMARY,s_ex_id | PRIMARY | 17 | bug37053_orig.trade.t_s_symb | 1 | | | 1 | SIMPLE | exchange | eq_ref | PRIMARY | PRIMARY | 6 | bug37053_orig.security.s_ex_id | 1 | | +----+-------------+-------------+--------+-----------------------------------------------------------------------+-----------------+---------+--------------------------------+------+----------------------------------------------+ 5 rows in set, 1 warning (0.01 sec) mysql> explain extended SELECT t_id, t_dts, st_name, tt_name, t_s_symb, -> t_qty, t_exec_name, t_chrg, s_name, ex_name -> FROM trade, status_type, trade_type, security, exchange -> WHERE t_ca_id = 43000031857 -> AND st_id = t_st_id -> AND tt_id = t_tt_id -> AND s_symb = t_s_symb -> AND ex_id = s_ex_id -> ORDER BY t_dts DESC -> LIMIT 50; +----+-------------+-------------+--------+-----------------------------------------------------------------------+----------------+---------+--------------------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+--------+-----------------------------------------------------------------------+----------------+---------+--------------------------------+------+----------------------------------------------+ | 1 | SIMPLE | trade_type | index | PRIMARY,idx_tt_id_name | idx_tt_name_id | 17 | NULL | 5 | Using index; Using temporary; Using filesort | | 1 | SIMPLE | trade | ref | idx_t_ca_id_dts,idx_t_s_symb_dts,t_st_id,t_tt_id,idx_t_ca_id_dts_desc | t_tt_id | 3 | bug37053_orig.trade_type.tt_id | 437 | Using where | | 1 | SIMPLE | status_type | eq_ref | PRIMARY,idx_st_id_name | PRIMARY | 4 | bug37053_orig.trade.t_st_id | 1 | | | 1 | SIMPLE | security | eq_ref | PRIMARY,s_ex_id | PRIMARY | 17 | bug37053_orig.trade.t_s_symb | 1 | | | 1 | SIMPLE | exchange | eq_ref | PRIMARY | PRIMARY | 6 | bug37053_orig.security.s_ex_id | 1 | | +----+-------------+-------------+--------+-----------------------------------------------------------------------+----------------+---------+--------------------------------+------+----------------------------------------------+ 5 rows in set, 1 warning (0.00 sec) mysql> explain extended SELECT t_id, t_dts, st_name, tt_name, t_s_symb, -> t_qty, t_exec_name, t_chrg, s_name, ex_name -> FROM trade straight_join status_type straight_join trade_type straight_join security -> straight_join exchange WHERE t_ca_id = 43000031857 -> AND st_id = t_st_id -> AND tt_id = t_tt_id -> AND s_symb = t_s_symb -> AND ex_id = s_ex_id -> ORDER BY t_dts DESC -> LIMIT 50; +----+-------------+-------------+--------+-----------------------------------------------------------------------+-----------------+---------+--------------------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+--------+-----------------------------------------------------------------------+-----------------+---------+--------------------------------+------+----------------------------------------------+ | 1 | SIMPLE | trade | range | idx_t_ca_id_dts,idx_t_s_symb_dts,t_st_id,t_tt_id,idx_t_ca_id_dts_desc | idx_t_ca_id_dts | 5 | NULL | 2182 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | status_type | index | PRIMARY,idx_st_id_name | idx_st_name_id | 16 | NULL | 4 | Using where; Using index | | 1 | SIMPLE | trade_type | eq_ref | PRIMARY,idx_tt_id_name | PRIMARY | 3 | bug37053_orig.trade.t_tt_id | 1 | | | 1 | SIMPLE | security | eq_ref | PRIMARY,s_ex_id | PRIMARY | 17 | bug37053_orig.trade.t_s_symb | 1 | | | 1 | SIMPLE | exchange | eq_ref | PRIMARY | PRIMARY | 6 | bug37053_orig.security.s_ex_id | 1 | | +----+-------------+-------------+--------+-----------------------------------------------------------------------+-----------------+---------+--------------------------------+------+----------------------------------------------+ 5 rows in set, 1 warning (0.00 sec) With versiion 5.0.56 STRAIGHT JOIN works fine.
[9 Jul 2008 21:00]
Sveta Smirnova
Explain output with version 5.1.28: $mysql51 bug37053_orig Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 146 Server version: 5.1.28-debug-log Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> explain extended SELECT t_id, t_dts, st_name, tt_name, t_s_symb, t_qty, t_exec_name, t_chrg, s_name, ex_name FROM trade straight_join status_type straight_join trade_type straight_join security straight_join exchange WHERE t_ca_id = 43000031857 AND st_id = t_st_id AND tt_id = t_tt_id AND s_symb = t_s_symb AND ex_id = s_ex_id ORDER BY t_dts DESC LIMIT 50; +----+-------------+-------------+--------+-----------------------------------------------------------------------+-----------------+---------+------------------------------+------+----------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+--------+-----------------------------------------------------------------------+-----------------+---------+------------------------------+------+----------+---------------------------------+ | 1 | SIMPLE | trade | ref | idx_t_ca_id_dts,idx_t_s_symb_dts,t_st_id,t_tt_id,idx_t_ca_id_dts_desc | idx_t_ca_id_dts | 5 | const | 1130 | 100.00 | Using temporary; Using filesort | | 1 | SIMPLE | status_type | eq_ref | PRIMARY,idx_st_id_name | PRIMARY | 4 | bug37053_orig.trade.t_st_id | 1 | 100.00 | | | 1 | SIMPLE | trade_type | eq_ref | PRIMARY,idx_tt_id_name | PRIMARY | 3 | bug37053_orig.trade.t_tt_id | 1 | 100.00 | | | 1 | SIMPLE | security | eq_ref | PRIMARY,s_ex_id | PRIMARY | 17 | bug37053_orig.trade.t_s_symb | 1 | 100.00 | | | 1 | SIMPLE | exchange | ALL | PRIMARY | NULL | NULL | NULL | 4 | 75.00 | Using where; Using join buffer | +----+-------------+-------------+--------+-----------------------------------------------------------------------+-----------------+---------+------------------------------+------+----------+---------------------------------+ 5 rows in set, 1 warning (0.02 sec) mysql> explain extended SELECT t_id, t_dts, st_name, tt_name, t_s_symb, t_qty, t_exec_name, t_chrg, s_name, ex_name FROM trade straight_join status_type straight_join trade_type straight_join security straight_join exchange force index (PRIMARY) WHERE t_ca_id = 43000031857 AND st_id = t_st_id AND tt_id = t_tt_id AND s_symb = t_s_symb AND ex_id = s_ex_id ORDER BY t_dts DESC LIMIT 50; +----+-------------+-------------+--------+-----------------------------------------------------------------------+-----------------+---------+--------------------------------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+--------+-----------------------------------------------------------------------+-----------------+---------+--------------------------------+------+----------+-------------+ | 1 | SIMPLE | trade | ref | idx_t_ca_id_dts,idx_t_s_symb_dts,t_st_id,t_tt_id,idx_t_ca_id_dts_desc | idx_t_ca_id_dts | 5 | const | 1130 | 100.00 | Using where | | 1 | SIMPLE | status_type | eq_ref | PRIMARY,idx_st_id_name | PRIMARY | 4 | bug37053_orig.trade.t_st_id | 1 | 100.00 | | | 1 | SIMPLE | trade_type | eq_ref | PRIMARY,idx_tt_id_name | PRIMARY | 3 | bug37053_orig.trade.t_tt_id | 1 | 100.00 | | | 1 | SIMPLE | security | eq_ref | PRIMARY,s_ex_id | PRIMARY | 17 | bug37053_orig.trade.t_s_symb | 1 | 100.00 | | | 1 | SIMPLE | exchange | eq_ref | PRIMARY | PRIMARY | 6 | bug37053_orig.security.s_ex_id | 1 | 100.00 | | +----+-------------+-------------+--------+-----------------------------------------------------------------------+-----------------+---------+--------------------------------+------+----------+-------------+ 5 rows in set, 1 warning (0.00 sec)
[9 Jul 2008 21:04]
Sveta Smirnova
Original select with version 5.1.28:
mysql> explain extended SELECT t_id, t_dts, st_name, tt_name, t_s_symb, t_qty, t_exec_name, t_chrg, s_name, ex_name FROM trade, status_type, trade_type, security, exchange WHERE t_ca_id = 43000031857 AND st_id = t_st_id AND tt_id = t_tt_id AND s_symb = t_s_symb AND ex_id = s_ex_id ORDER BY t_dts DESC LIMIT 50\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: trade
type: ref
possible_keys: idx_t_ca_id_dts,idx_t_s_symb_dts,t_st_id,t_tt_id,idx_t_ca_id_dts_desc
key: idx_t_ca_id_dts
key_len: 5
ref: const
rows: 1130
filtered: 100.00
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: status_type
type: eq_ref
possible_keys: PRIMARY,idx_st_id_name
key: PRIMARY
key_len: 4
ref: bug37053_orig.trade.t_st_id
rows: 1
filtered: 100.00
Extra:
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: trade_type
type: eq_ref
possible_keys: PRIMARY,idx_tt_id_name
key: PRIMARY
key_len: 3
ref: bug37053_orig.trade.t_tt_id
rows: 1
filtered: 100.00
Extra:
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: security
type: eq_ref
possible_keys: PRIMARY,s_ex_id
key: PRIMARY
key_len: 17
ref: bug37053_orig.trade.t_s_symb
rows: 1
filtered: 100.00
Extra:
*************************** 5. row ***************************
id: 1
select_type: SIMPLE
table: exchange
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 4
filtered: 75.00
Extra: Using where; Using join buffer
5 rows in set, 1 warning (0.00 sec)
[9 Jul 2008 21:08]
Sveta Smirnova
EXPLAIN with version 6.0.6: $./bin/mysql -uroot --socket=/tmp/mysql_ssmirnova.sock bug37053_orig Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 6.0.6-alpha-debug Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> explain extended SELECT t_id, t_dts, st_name, tt_name, t_s_symb, t_qty, t_exec_name, t_chrg, s_name, ex_name FROM trade, status_type, trade_type, security, exchange WHERE t_ca_id = 43000031857 AND st_id = t_st_id AND tt_id = t_tt_id AND s_symb = t_s_symb AND ex_id = s_ex_id ORDER BY t_dts DESC LIMIT 50\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: trade type: ref possible_keys: idx_t_ca_id_dts,idx_t_s_symb_dts,t_st_id,t_tt_id,idx_t_ca_id_dts_desc key: idx_t_ca_id_dts key_len: 5 ref: const rows: 1130 filtered: 100.00 Extra: Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: status_type type: eq_ref possible_keys: PRIMARY,idx_st_id_name key: PRIMARY key_len: 4 ref: bug37053_orig.trade.t_st_id rows: 1 filtered: 100.00 Extra: *************************** 3. row *************************** id: 1 select_type: SIMPLE table: trade_type type: eq_ref possible_keys: PRIMARY,idx_tt_id_name key: PRIMARY key_len: 3 ref: bug37053_orig.trade.t_tt_id rows: 1 filtered: 100.00 Extra: *************************** 4. row *************************** id: 1 select_type: SIMPLE table: security type: eq_ref possible_keys: PRIMARY,s_ex_id key: PRIMARY key_len: 17 ref: bug37053_orig.trade.t_s_symb rows: 1 filtered: 100.00 Extra: *************************** 5. row *************************** id: 1 select_type: SIMPLE table: exchange type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 4 filtered: 75.00 Extra: Using where; Using join buffer 5 rows in set, 1 warning (0.02 sec) mysql> explain extended SELECT t_id, t_dts, st_name, tt_name, t_s_symb, t_qty, -> t_exec_name, t_chrg, s_name, ex_name FROM trade straight_join status_type straight_join -> trade_type straight_join security straight_join exchange WHERE t_ca_id = 43000031857 -> AND st_id = t_st_id AND tt_id = t_tt_id AND s_symb = t_s_symb AND ex_id = s_ex_id -> ORDER BY t_dts DESC LIMIT 50\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: trade type: ref possible_keys: idx_t_ca_id_dts,idx_t_s_symb_dts,t_st_id,t_tt_id,idx_t_ca_id_dts_desc key: idx_t_ca_id_dts key_len: 5 ref: const rows: 1130 filtered: 100.00 Extra: Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: status_type type: eq_ref possible_keys: PRIMARY,idx_st_id_name key: PRIMARY key_len: 4 ref: bug37053_orig.trade.t_st_id rows: 1 filtered: 100.00 Extra: *************************** 3. row *************************** id: 1 select_type: SIMPLE table: trade_type type: eq_ref possible_keys: PRIMARY,idx_tt_id_name key: PRIMARY key_len: 3 ref: bug37053_orig.trade.t_tt_id rows: 1 filtered: 100.00 Extra: *************************** 4. row *************************** id: 1 select_type: SIMPLE table: security type: eq_ref possible_keys: PRIMARY,s_ex_id key: PRIMARY key_len: 17 ref: bug37053_orig.trade.t_s_symb rows: 1 filtered: 100.00 Extra: *************************** 5. row *************************** id: 1 select_type: SIMPLE table: exchange type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 4 filtered: 75.00 Extra: Using where; Using join buffer 5 rows in set, 1 warning (0.00 sec) mysql> explain extended SELECT t_id, t_dts, st_name, tt_name, t_s_symb, t_qty, -> t_exec_name, t_chrg, s_name, ex_name FROM trade straight_join status_type straight_join -> trade_type straight_join security straight_join exchange force index (PRIMARY) WHERE -> t_ca_id = 43000031857 AND st_id = t_st_id AND tt_id = t_tt_id AND s_symb = -> t_s_symb AND ex_id = s_ex_id ORDER BY t_dts DESC LIMIT 50\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: trade type: ref possible_keys: idx_t_ca_id_dts,idx_t_s_symb_dts,t_st_id,t_tt_id,idx_t_ca_id_dts_desc key: idx_t_ca_id_dts key_len: 5 ref: const rows: 1130 filtered: 100.00 Extra: Using where *************************** 2. row *************************** id: 1 select_type: SIMPLE table: status_type type: eq_ref possible_keys: PRIMARY,idx_st_id_name key: PRIMARY key_len: 4 ref: bug37053_orig.trade.t_st_id rows: 1 filtered: 100.00 Extra: *************************** 3. row *************************** id: 1 select_type: SIMPLE table: trade_type type: eq_ref possible_keys: PRIMARY,idx_tt_id_name key: PRIMARY key_len: 3 ref: bug37053_orig.trade.t_tt_id rows: 1 filtered: 100.00 Extra: *************************** 4. row *************************** id: 1 select_type: SIMPLE table: security type: eq_ref possible_keys: PRIMARY,s_ex_id key: PRIMARY key_len: 17 ref: bug37053_orig.trade.t_s_symb rows: 1 filtered: 100.00 Extra: *************************** 5. row *************************** id: 1 select_type: SIMPLE table: exchange type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 6 ref: bug37053_orig.security.s_ex_id rows: 1 filtered: 100.00 Extra: 5 rows in set, 1 warning (0.00 sec)
Description: To execute the following query the optimizer unnecessarily chooses a temporary table and filesort. However, by forcing the join order and forcing the use of the PRIMARY index on the exchange table, the filesort is not used, and the query response time improves by more than 100x. Here is the default query and explain plan: -------------- explain extended SELECT t_id, t_dts, st_name, tt_name, t_s_symb, t_qty, t_exec_name, t_chrg, s_name, ex_name FROM trade, status_type, trade_type, security, exchange WHERE t_ca_id = 43000031857 AND st_id = t_st_id AND tt_id = t_tt_id AND s_symb = t_s_symb AND ex_id = s_ex_id ORDER BY t_dts DESC LIMIT 50 -------------- +----+-------------+-------------+--------+-----------------------------------------------------------------------+-----------------+---------+---------------------+------+----------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+--------+-----------------------------------------------------------------------+-----------------+---------+---------------------+------+----------+---------------------------------+ | 1 | SIMPLE | trade | ref | idx_t_ca_id_dts,idx_t_s_symb_dts,t_st_id,t_tt_id,idx_t_ca_id_dts_desc | idx_t_ca_id_dts | 5 | const | 5384 | 100.00 | Using temporary; Using filesort | | 1 | SIMPLE | status_type | eq_ref | PRIMARY,idx_st_id_name | PRIMARY | 4 | tpce.trade.t_st_id | 1 | 100.00 | | | 1 | SIMPLE | trade_type | eq_ref | PRIMARY,idx_tt_id_name | PRIMARY | 3 | tpce.trade.t_tt_id | 1 | 100.00 | | | 1 | SIMPLE | security | eq_ref | PRIMARY,s_ex_id | PRIMARY | 17 | tpce.trade.t_s_symb | 1 | 100.00 | | | 1 | SIMPLE | exchange | ALL | PRIMARY | NULL | NULL | NULL | 4 | 75.00 | Using where; Using join buffer | +----+-------------+-------------+--------+-----------------------------------------------------------------------+-----------------+---------+---------------------+------+----------+---------------------------------+ Here is the result when the join order is forced and the use of the PRIMARY index on exchange is forced: explain extended SELECT t_id, t_dts, st_name, tt_name, t_s_symb, t_qty, t_exec_name, t_chrg, s_name, ex_name FROM trade straight_join status_type straight_join trade_type straight_join security straight_join exchange force index (PRIMARY) WHERE t_ca_id = 43000031857 AND st_id = t_st_id AND tt_id = t_tt_id AND s_symb = t_s_symb AND ex_id = s_ex_id ORDER BY t_dts DESC LIMIT 50 -------------- +----+-------------+-------------+--------+-----------------------------------------------------------------------+-----------------+---------+-----------------------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+--------+-----------------------------------------------------------------------+-----------------+---------+-----------------------+------+----------+-------------+ | 1 | SIMPLE | trade | ref | idx_t_ca_id_dts,idx_t_s_symb_dts,t_st_id,t_tt_id,idx_t_ca_id_dts_desc | idx_t_ca_id_dts | 5 | const | 5376 | 100.00 | Using where | | 1 | SIMPLE | status_type | eq_ref | PRIMARY,idx_st_id_name | PRIMARY | 4 | tpce.trade.t_st_id | 1 | 100.00 | | | 1 | SIMPLE | trade_type | eq_ref | PRIMARY,idx_tt_id_name | PRIMARY | 3 | tpce.trade.t_tt_id | 1 | 100.00 | | | 1 | SIMPLE | security | eq_ref | PRIMARY,s_ex_id | PRIMARY | 17 | tpce.trade.t_s_symb | 1 | 100.00 | | | 1 | SIMPLE | exchange | eq_ref | PRIMARY | PRIMARY | 6 | tpce.security.s_ex_id | 1 | 100.00 | | +----+-------------+-------------+--------+-----------------------------------------------------------------------+-----------------+---------+-----------------------+------+----------+-------------+ How to repeat: Unfortunately this isn't easy to repeat. I setup a series of simple tables with similar indexes, and couldn't reproduce the problem (i.e. the optimizer chose the correct plan), so it seems to be dependent on the data. I can provide access to the test environment, though.