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?

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). — Brad Christie, Mar 31 '11 at 17:25

RolandoMySQLDBA · Accepted Answer · 2012-05-17T14:28:42.953

11

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.

edited May 17 '12 at 14:28

answered Mar 31 '11 at 18:33

RolandoMySQLDBA

43,883
16
91
132

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. – Lorenzo Dematté Jan 14 '13 at 11:38
1

I amgeting 1500 records out of 200k. Still no available key used. – kellogs Oct 21 '13 at 11:47
1

Me too… 15,080 out of 709,743 – Serge Velikan Jul 11 '14 at 13:47
@kellogs, @Serge, Try using [`force key`](http://dev.mysql.com/doc/refman/5.1/en/index-hints.html), does it work for you? – Pacerier Feb 01 '15 at 22:41

score 0 · Answer 2 · answered Mar 31 '11 at 18:44

0

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.

answered Mar 31 '11 at 18:44

Lightness Races in Orbit

378,754
76
643
1,055

score 0 · Answer 3 · answered Mar 31 '11 at 18:56

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.

SELECT statement not using possible_keys

3 Answers3

Linked