Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 900
Paul Wieland Avatar asked Mar 31 '11 17:03

Paul Wieland


People also ask

How do I select a specific row in a table in MySQL?

MySQL SELECT statement is used to retrieve rows from one or more tables. The statement can also include UNION statements and subqueries. SELECT statement is used to fetch rows or records from one or more tables.

What does the Possible_keys column in this output denote?

The possible_keys column indicates the indexes from which MySQL can choose to find the rows in this table. Note that this column is totally independent of the order of the tables as displayed in the output from EXPLAIN .

What is Key_len in MySQL explain?

The key_len column indicates the length of the key that MySQL decided to use. The value of key_len enables you to determine how many parts of a multiple-part key MySQL actually uses. If the key column says NULL , the key_len column also says NULL .

What is possible keys in explain plan?

possible_keys : shows the keys that can be used by MySQL to find rows from the table. These keys may or may not be used in practice. keys : indicates the actual index used by MySQL. MySQL always looks for an optimal key that can be used for the query.


1 Answers

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.

like image 96
RolandoMySQLDBA Avatar answered Sep 18 '22 13:09

RolandoMySQLDBA