SELECT statement not using possible_keys

Question

I have a table from a legacy system which does not have a primary key. It records transactional data for issuing materials in a factory.

For simplicities sake, lets say each row contains job_number, part_number, quantity & date_issued.

I added an index to the date issued column. When I run an EXPLAIN SELECT * FROM issued_parts WHERE date_issued > '20100101', it shows this:

+----+-------------+----------------+------+-------------------+------+---------+------+---------+-------------+
| id | select_type | table          | type | possible_keys     | key  | key_len | ref  | rows    | Extra       |
+----+-------------+----------------+------+-------------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | issued_parts   | ALL  | date_issued_alloc | NULL | NULL    | NULL | 9724620 | Using where |
+----+-------------+----------------+------+-------------------+------+---------+------+---------+-------------+

So it sees the key, but it doesn't use it? Can someone explain why?

Answer 1

Something tells me the MySQL Query Optimizer decided correctly.

Here is how you can tell. Run these:

Count of Rows

SELECT COUNT(1) FROM issued_parts;

Count of Rows Matching Your Query

SELECT COUNT(1) FROM issued_parts WHERE date_issued > '20100101';

If the number of rows you are actually retrieving exceeds 5% of the table's total number, the MySQL Query Optimizer decides it would be less effort to do a full table scan.

Now, if your query was more exact, for example, with this:

SELECT * FROM issued_parts WHERE date_issued = '20100101';

then, you will get a different EXPLAIN plan altogether.

Answer 2

possible_keys names keys with the relevant columns in, but that doesn't mean that each key in it is going to be useful for the query. In this case, none are.

Answer 3

There are multiple types of indexes (indices?). A hash index is a fast way to do a lookup on an item given a specific value. If you have a bunch of discreet values that you are querying against, (for example, a list of 10 dates) then you can calculate a hash for each of those values, and look them up in the index. Since you aren't doing a lookup on a specific value, but rather doing a comparison, a hash index won't help you.

On the other hand, a B-Tree index can help you because it gives an ordering to the elements it is indexing. For instance, see here: http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html for mysql (search for B-Tree Index Characteristics) . You may want to check that your table is using a b-tree index for it's index column.