SELECT call without using possible_keys

Question

I have a table from an old system that does not have a primary key. It records transactional data for the release of materials in the factory.

For simplicity, let's say each row contains job_number, part_number, quantity and date_issued.

I added an index to the date column. When I run EXPLAIN SELECT * FROM issu_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, he sees the key, but he does not use it? Can someone explain why?

Answer 1

Something tells you that the MySQL query optimizer has decided correctly.

Here's how you can tell it. Follow these steps:

Number of lines

SELECT COUNT(1) FROM issued_parts; 

Number of rows matching your query

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

If the number of rows you actually get exceeds 5% of the total number of tables, the MySQL query optimizer decides that there would be less effort to completely scan the table.

Now, if your request was more accurate, for example with this:

 SELECT * FROM issued_parts WHERE date_issued = '20100101'; 

then you will get a completely different EXPLAIN plan.

Answer 2

possible_keys enter the keys with the corresponding columns, but this does not mean that each key in it will be useful for the request. In this case, no.

Answer 3

There are several types of indexes (indexes?). A hash index is a quick way to search for an item with a given value. If you have a bunch of confidential values ​​that you are accessing, for example, a list of 10 dates, you can calculate the hash for each of these values ​​and see them in the index. Since you are not searching for a specific value, but rather performing a comparison, a hash index will not help you.

The B-Tree index, on the other hand, can help you because it gives ordering to the elements that it indexes. For example, see here http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html for mysql (search for B-Tree Indexes). You can verify that your table uses the b-tree index for the index column.