Mysql query is not using any key

Question

CREATE TABLE `TEST` (
`ID1` mediumint(8) NOT NULL default '0',
`ID2` mediumint(8) NOT NULL default '0',
`DATE` datetime NOT NULL default '0000-00-00 00:00:00',
 UNIQUE KEY `COMBO_INDEX` (`ID1`,`ID2`),
 KEY `ID2` (`ID2`)
) ENGINE=InnoDB`

This table has approx 16196496 records

EXPLAIN SELECT * FROM TEST WHERE ID1 IN ('8518582',  '5398912',  '6120243',  '6841316',  '7580078',  '7671953',  '7775737',  '7792470',  '7887985',  '7888375',  '7946516',  '8008760',  '8111722',  '8211235',  '8262746',  '8365675',  '8396853', '8399818',  '8410062',  '8459079',  '8490683')

I am getting output as

+----+-------------+------------------------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | table                  | type | possible_keys | key  | key_len | ref  | rows     | Extra       |
+----+-------------+------------------------+------+---------------+------+---------+------+----------+-------------+
|  1 | SIMPLE      | TEST                   | ALL  | ID1           | NULL | NULL    | NULL | 16196496 | Using where |
+----+-------------+------------------------+------+---------------+------+---------+------+----------+-------------+

I don't understand why the query is not using any key.

Also, when I run this query in this innodb table, it's taking huge amount of time 329 second (MySQL version 5.0.45-log).

While if I run same query on myisam table, it's taking just 2 seconds (though on explain its showing the same result). I am using MySQL version 5.5.

Why is the query not taking any key?

Answer 1

i am not sure but another reason which i though is "it might be case when there is insufficient memory available for indexes to load and hence full scan"

Answer 2

innodb needs a primary key to fast seek to the row found in index. As long as you don't have any - mysql cannot do that so it prefers fullscan.

http://dev.mysql.com/doc/refman/5.6/en/innodb-table-and-index.html

Accessing a row through the clustered index is fast because the index search leads directly to the page with all the row data. If a table is large, the clustered index architecture often saves a disk I/O operation when compared to storage organizations that store row data using a different page from the index record. (For example, MyISAM uses one file for data rows and another for index records.)

So the obvious solution - is to replace the unique key with a primary key (though personally I don't like natural primary keys, composite natural primary keys especially).

PS: seems like my guess in the comments about using numbers instead of strings helped. Though the advice about adding primary keys still in action - do that to get even better performance.