| Bug #61983 | strange explain plan results | ||
|---|---|---|---|
| Submitted: | 26 Jul 2011 12:13 | Modified: | 27 Sep 2011 23:15 |
| Reporter: | luo longjiu | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.1.48 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[27 Jul 2011 13:21]
Valeriy Kravchuk
Please, check if the same problem happens with a newer version, 5.1.58. If it does, please, provide complete repeatable test case, with CREATE TABLE and INSERTs to get minimal set of data to demonstrate the problem.
[27 Aug 2011 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[28 Sep 2011 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: in the explain plan output,i discover the type column value is 'ALL', but the key column is not 'NULL': sql: select t1.* from test_bbs t1, (select id from test_bbs where g_id = 1 order by replay_time) t2 where t2.id = t1.id; How to repeat: mysql> explain select t1.* -> from test_bbs t1, -> (select id -> from test_bbs -> where g_id = 1 -> order by replay_time) t2 -> where t2.id = t1.id; +----+-------------+------------+--------+----------------+----------------+---------+-------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+----------------+----------------+---------+-------+------+----------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 3 | | | 1 | PRIMARY | t1 | eq_ref | PRIMARY | PRIMARY | 8 | t2.id | 1 | | | 2 | DERIVED | test_bbs | ALL | ind_test_bbs_g | ind_test_bbs_g | 5 | | 14 | Using filesort | +----+-------------+------------+--------+----------------+----------------+---------+-------+------+----------------+ 3 rows in set (0.01 sec) id=2: | 2 | DERIVED | test_bbs | ALL | ind_test_bbs_g | ind_test_bbs_g | 5 | | 14 | Using filesort| From the explain plan output,the type column is ALL,but the key column is not NULL, makes me feel confused!