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?

Antony

Naming a column key doesn't make it one. Though I'm not fully understanding the question, assign the key column as the primary key if you'd like that kind of functionality. If not you're going to need to supply more information (such as current schema for instance). – Philharmonic

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.

Magnifico

Your answer is technically correct, however "the MySQL Query Optimizer decided correctly" is not. I am more or less in the same situation, and in my case using the key (forcing it) makes the query much faster. – Tori

I amgeting 1500 records out of 200k. Still no available key used. – Tailpiece

Me too… 15,080 out of 709,743 – Repp

@kellogs, @Serge, Try using force key, does it work for you? – Melitamelitopol

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.

Pyridoxine

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.

Tinker