Bug #67885 | Two column index isn't used, when adding a char column to table | ||
---|---|---|---|
Submitted: | 12 Dec 2012 17:43 | Modified: | 9 Jan 2013 17:40 |
Reporter: | Olaf Dietsche | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 5.5.24, 5.5.30, 5.7.1, 5.1.68, 5.0.97 | OS: | Linux (Ubuntu 12.04) |
Assigned to: | CPU Architecture: | Any |
[12 Dec 2012 17:43]
Olaf Dietsche
[12 Dec 2012 19:25]
Sveta Smirnova
Thank you for the report. Verified as described.
[12 Dec 2012 19:27]
Sveta Smirnova
test case for MTR
Attachment: bug67885.test (application/octet-stream, text), 895 bytes.
[8 Jan 2013 16:22]
Jørgen Løland
Not a bug. In the former case, the 'from_to_index' index is covering so an index scan can be used to read countries.* (you can see that Extra has "Using index" which means covering index). In the latter case, the index is not covering and can therefore not be used to read countries.*. For this reason, table scan is used instead of index scan.
[8 Jan 2013 23:52]
Olaf Dietsche
When I add country to from_to_index, the index is used in the second example as well. But ... - I don't understand this restriction. - Where is it documented, that an index is used only when it is covering?
[9 Jan 2013 9:17]
Jørgen Løland
Hi Olaf, It's not a question of whether or not the index can be used by the query, but whether or not it is *efficient* to use that index. Looking at the EXPLAIN in the original post you see that the index is listed in the possible_keys column. But using an index that does not filter rows (in the first EXPLAIN it does not filter rows, it is just used to retrieve the necessary column data) and then look up the values in the table afterwards is just more work than scanning the table in the first place. For you query, all rows in country have to be read. Now consider these ways of accessing the rows: Covering index: Read all index rows in sequence and return each row as they are read. The number of index rows read is the same as the number of rows in the table. Non-covering index: Read all index rows in sequence, and for each of these rows do a lookup in the table. The number of rows read = the number of index rows (=rows in the table) + a lookup in the table for each of these = 2x rows in the table. Even worse, half of these reads require random read from the disk. Table scan: Read all rows from the table in sequence and return each row as they are read. The number of rows read = the number of rows in the table. So, MySQL considers the options and decides to do index scan if a covering index is available. If not an option, table scan is much cheaper than index scan of a non-covering index. If you add predicates that can be used to perform range access or direct lookup access ('ref') in the index, or if you want an ordered result that the index can provide, MySQL may of course choose to use a non-clustering index.
[9 Jan 2013 17:40]
Olaf Dietsche
Thank you for this detailed explanation.