MySQL doesn't use my defined index over a simple select query

Question

I've been trying to use an index in my query but to no avail. It's defined, but not used.

Here are my indexes

Table  | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment  
--------------------------------------------------------------------------------------------------------------------------------------------------------
alerts |          1 | date_index |            1 | date        | A         |        9825 |     NULL | NULL   |      | BTREE

This my query + explain:

EXPLAIN SELECT latitude, longitude, msg_id
        FROM alerts
        FORCE INDEX(date_index)
        WHERE date>1336312075;


| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
--------------------------------------------------------------------------------------------------------
|  1 | SIMPLE      | alerts | ALL  | date_index    | NULL | NULL    | NULL | 9825 | Using where |

The table has 9825 rows, and the date I chose was among the latest 9.... so it should have scanned only 9 rows.

Any ideas? Thanks

score 4 · Accepted Answer · answered Jun 13 '12 at 10:19

Just a guess: what is the type of your date column? Is it possible, that the value 1336312075 is not of type date? In this case the db may convert all values in the date column to the type of 1336312075 instead of converting 1336312075 to the type of date column which could render the index useless.

Possible solution: convert your value to the type of date column:

where date > convert_to_the_type_of_date_column(1336312075)

MySQL doesn't use my defined index over a simple select query

1 Answers1