[MDEV-10127] Optimizer does not identify index as covering on myisam table Created: 2016-05-26  Updated: 2016-05-26  Resolved: 2016-05-26

Status: Closed
Project: MariaDB Server
Component/s: Optimizer, Storage Engine - MyISAM
Affects Version/s: 10.0.24
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Jiri Kavalik Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: upstream

Attachments: File myisam_covering_test.sql    

 Description   

In some cases optimizer fetches full rows even when the index used covers the entire query. The index-only plan is used when the index is forced by USE/FORCE INDEX() hint. If the order of columns in the index is changed, the query is executed index-only without the hint (but in the test case that leads to filesort).

Affects MySQL too (tested 5.6, probably others), reported as http://bugs.mysql.com/bug.php?id=81601

 Comments   
Comment by Sergei Golubchik [ 2016-05-26 ]

My guess would be that the optimizer first looks for a best index to use for the WHERE clause, picks the first matching one, type_status_date. Then it looks further and finds that this index will return rows in the wrong order. At this point in time it is too late to go back and try another index.

If you remove your type_status_date index, then the optimizer will choose type_modified_status, and will use it as a covering index without any hints.

But the correct solution for you is to run ANALYZE TABLE, then the optimizer will see different statistics for different indexes and will not blindly choose the first matching index.

Comment by Jiri Kavalik [ 2016-05-26 ]

serg: In my tests the right index is actually being chosen, the covering index is not ignored, just the last column in it is - the "covering"-property of the index is ignored. ANALYZE did not help.

+------+-------------+------------+--------+---------------------------------------+----------------------+---------+-------+------+----------------+
 
| id   | select_type | table      | type   | possible_keys                         | key                  | key_len | ref   | rows | Extra          |
 
+------+-------------+------------+--------+---------------------------------------+----------------------+---------+-------+------+----------------+
 
|    1 | PRIMARY     | <derived2> | ALL    | NULL                                  | NULL                 | NULL    | NULL  |   10 | Using where    |
 
|    2 | DERIVED     | <derived3> | system | NULL                                  | NULL                 | NULL    | NULL  |    1 |                |
 
|    2 | DERIVED     | wp_posts   | ref    | type_status_date,type_modified_status | type_modified_status | 82      | const |   10 | Using where    |
 
|    3 | DERIVED     | NULL       | NULL   | NULL                                  | NULL                 | NULL    | NULL  | NULL | No tables used |
 
+------+-------------+------------+--------+---------------------------------------+----------------------+---------+-------+------+----------------+

Comment by Sergei Golubchik [ 2016-05-26 ]

I wrote "the correct solution is..." because it worked for me in sqlfiddle. Using your link http://sqlfiddle.com/#!9/1cc46/5 and adding analyze table wp_posts; (right after set names) I see that the last explain changes to

| 2 | DERIVED | wp_posts | index | type_status_date,type_modified_status | type_modified_status | 169 | (null) | 92 | Using where; Using index |

Comment by Jiri Kavalik [ 2016-05-26 ]

Hm, seems like a second ANALYZE did it on my local instance too.. Thank you serg!

Generated at Thu Feb 08 07:39:53 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.