| Bug #39786 | Explain doesn't show key with order by in derived table. | ||
|---|---|---|---|
| Submitted: | 1 Oct 2008 17:56 | Modified: | 12 Oct 2012 10:59 |
| Reporter: | Shannon Wade | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.1.26 | OS: | Any |
| Assigned to: | Evgeny Potemkin | CPU Architecture: | Any |
[1 Oct 2008 18:07]
Sergey Petrunya
This is a known property. The observed effects are manifestations of the fact that at the moment FROM subqueries are resolved using this strategy: 1. Optimize and run the FROM subquery and capture the result into temp table. The temp table has no indexes, if the records in the temptable happen to be ordered, mysql won't use that fact. 2. Optimize and run the parent select. (see also http://s.petrunia.net/blog/?p=43, slide #3).
[1 Oct 2008 18:09]
Sergey Petrunya
Evgen Potemkin is working on FROM subqueries, see WL#3485. AFAIU the problem reported in this bug should go away when WL#3485 is implemented. At the moment the target version for WL#3485 is 6.1
[2 Oct 2008 15:38]
MySQL Verification Team
Hi sergey,
Could you clarify this behavior for me, I'm not sure I understand why the key is only shown in certain circumstances along with the Handler_read_key behavior (in description) when they are not. If the temp table has no indexes why would key be shown sometimes and sometimes not?
Also below with a sum & group by it shows the key and rows=2 with an order by inside.
mysql> explain select *
-> from (
-> select c2, c3, sum(c5)
-> from t1
-> where c3 in ( 2,3 )
-> and c4=2
-> group by c2,c3
-> order by c5
-> ) x \G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
Extra:
*************************** 2. row ***************************
id: 2
select_type: DERIVED
table: t1
type: range
possible_keys: test
key: test
key_len: 8
ref: NULL
rows: 2
Extra: Using where; Using temporary; Using filesort
2 rows in set (0.00 sec)
[9 Oct 2008 16:58]
Sergey Petrunya
What was said above is all true however Shannon has drawn my attention that the posted EXPLAINs also indicate another problem. Investigating...
[12 Oct 2012 10:58]
Olav Sandstå
This bug has been fixed in MySQL 5.5.26.
Description: In the following table and queries, moving the 'order by' outside the inner query or removing the 'order by' results in the chosen Key being displayed by EXPLAIN. Also using force index does not result in key being displayed. SHOW SESSION STATUS indicates that the key is likely being chosen by the optimizer but that it is not reflected in the EXPLAIN results. This still occurs if engine=MyISAM or InnoDb. Tested with 5.1.26-rc-log and also mysql-5.1.28-rc. Table & Data: drop table if exists t1; CREATE TABLE t1 ( c1 int(10) unsigned NOT NULL AUTO_INCREMENT, c2 int(10) unsigned NOT NULL, c3 int(10) unsigned NOT NULL, c4 int(10) unsigned NOT NULL, c5 int(10) not null, PRIMARY KEY (c1), key test (c3,c4) ) ENGINE=MyISAM; INSERT INTO `t1` VALUES (1,1,1,1,1),(2,1,2,2,2),(3,2,2,5,4),(4,3,1,1,6),(5,3,3,2,7),(6,3,2,5,7),(7,1,5,1,7),(8,3,6,1,9),(9,1,8,1,3),(10,3,9,1,4); Simple query, results and explain shows using 'test' key: mysql> select c2, c3, c5 -> from t1 -> where c3 in ( 2,3 ) -> and c4=2 -> order by c5 ; +----+----+----+ | c2 | c3 | c5 | +----+----+----+ | 1 | 2 | 2 | | 3 | 3 | 7 | +----+----+----+ 2 rows in set (0.00 sec) mysql> explain select c2, c3, c5 -> from t1 -> where c3 in ( 2,3 ) -> and c4=2 -> order by c5 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: range possible_keys: test key: test key_len: 8 ref: NULL rows: 2 Extra: Using where; Using filesort 1 row in set (0.00 sec) Plan as inner, no key shown, derived2 rows=2: mysql> explain -> select * -> from ( -> select c2, c3, c5 -> from t1 -> where c3 in ( 2,3 ) -> and c4=2 -> order by c5 -> ) x \G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: <derived2> type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2 Extra: *************************** 2. row *************************** id: 2 select_type: DERIVED table: t1 type: ALL possible_keys: test key: NULL key_len: NULL ref: NULL rows: 10 Extra: Using filesort 2 rows in set (0.00 sec) Force index, no key: mysql> explain -> select * -> from ( -> select c2, c3, c5 -> from t1 force index(test) -> where c3 in ( 2,3 ) -> and c4=2 -> order by c5 -> ) x \G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: <derived2> type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2 Extra: *************************** 2. row *************************** id: 2 select_type: DERIVED table: t1 type: ALL possible_keys: test key: NULL key_len: NULL ref: NULL rows: 10 Extra: Using filesort 2 rows in set (0.00 sec) Handler status with force index: mysql> show session status like 'handler%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Handler_commit | 0 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 2 | | Handler_read_next | 2 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 34 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 31 | +----------------------------+-------+ 15 rows in set (0.01 sec) Removing the order by shows the key mysql> explain -> select * -> from ( -> select c2, c3, c5 -> from t1 force index(test) -> where c3 in ( 2,3 ) -> and c4=2 -> /* order by c5 */ -> ) x \G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: <derived2> type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2 Extra: *************************** 2. row *************************** id: 2 select_type: DERIVED table: t1 type: range possible_keys: test key: test key_len: 8 ref: NULL rows: 2 Extra: Using where 2 rows in set (0.00 sec) How to repeat: Table structure, queries, explain provided in description.