Description:
When using a partial index, the optimizer will do a more expensive table lookup instead of using the index, even when this partial index covers the whole set of needed data. If we add a full index on the row, an index scan is correctly done instead of a table read.
I've tested this across a number of GA versions from 5.0 -> 5.6 and found the EXPLAIN plan to be the same across all versions: with a partial index, EXPLAIN will not have "Using index".
See below for how to reproduce.
How to repeat:
1) I'm using the world InnoDB sample database that you can get from here:
http://dev.mysql.com/doc/index-other.html
http://dev.mysql.com/doc/world-setup/en/index.html
2) Now create a partial index on 'Name' so that table looks like:
mysql [localhost] {msandbox} (test) > ALTER TABLE City ADD INDEX `short10` (`Name`(10));
Query OK, 0 rows affected (0.56 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql [localhost] {msandbox} (test) > SHOW CREATE TABLE City\G
*************************** 1. row ***************************
Table: City
Create Table: CREATE TABLE `City` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`),
KEY `short10` (`Name`(10))
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
3) Now execute a query that does not need the whole of 'Name' column and check EXPLAIN:
mysql [localhost] {msandbox} (test) > SELECT COUNT(*) FROM City WHERE Name LIKE 'Br%'; +----------+
| COUNT(*) |
+----------+
| 33 |
+----------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (test) > EXPLAIN SELECT COUNT(*) FROM City WHERE Name LIKE 'Br%';
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | City | range | short10 | short10 | 10 | NULL | 33 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
4) Notice how in 3), the query is not "Using index". If we use a full index, EXPLAIN will show a faster index scan is used:
mysql [localhost] {msandbox} (test) > ALTER TABLE City ADD INDEX `full35` (`Name`);
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql [localhost] {msandbox} (test) > SELECT COUNT(*) FROM City WHERE Name LIKE 'Br%';
+----------+
| COUNT(*) |
+----------+
| 33 |
+----------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (test) > EXPLAIN SELECT COUNT(*) FROM City WHERE Name LIKE 'Br%';
+----+-------------+-------+-------+----------------+--------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+----------------+--------+---------+------+------+--------------------------+
| 1 | SIMPLE | City | range | short10,full35 | full35 | 35 | NULL | 33 | Using where; Using index |
+----+-------------+-------+-------+----------------+--------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
5) The optimizer doesn't seem to know that COUNT(*) doesn't need the whole 'Name' column. This is true for other functions that only need a prefix of 'Name' or that don't even need to lookup that row:
mysql [localhost] {msandbox} (test) > EXPLAIN SELECT COUNT(Name) FROM City WHERE Name LIKE 'Br%';
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | City | range | short10 | short10 | 10 | NULL | 33 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (test) > EXPLAIN SELECT LEFT(Name, 1) FROM City WHERE Name LIKE 'Br%';
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | City | range | short10 | short10 | 10 | NULL | 33 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.49 sec)
mysql [localhost] {msandbox} (test) > EXPLAIN SELECT 1 FROM City WHERE Name LIKE 'Br%';
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | City | range | short10 | short10 | 10 | NULL | 33 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
Suggested fix:
The optimizer should be more intelligent in knowing that a partial index does not always need to fetch the full row information from the table. It should be able to show "Using index" in the EXPLAIN when the partial index is sufficient (eg: counting rows).
NOTE: using a "full" index is usually not a problem. However, due to such limitations on the size of indexes:
===
"By default, an index key for a single-column index can be up to 767 bytes. The same length limit applies to any index key prefix. See Section 13.1.13, “CREATE INDEX Syntax”. "
http://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html
===
Only partial indexes can be created for columns that are larger (eg: VARCHAR(4000)), meaning the user will always have to use slow table scan instead of faster index scan with "Using index".
