Search code examples
mysqlselectindexingexplain

SELECT statement not using possible_keys

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?

Solution

  • 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.