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

bpgergo · Accepted Answer · 2012-06-13 10:19:01Z

4

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)

answered Jun 13, 2012 at 10:19

bpgergo

16.1k5 gold badges48 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related