MySQL - Not using Index

Question

I'm trying to figure out what I might be doing wrong. This query doesn't seem to be using the index, as its taking way to long.

When executing:

Explain SELECT a, b, c, d  FROM `table` WHERE d = 4013456

id       select_type    table    type    possible_keys    key    key_len    ref    rows    Extra`
1      SIMPLE         table     ALL     d                   NULL    NULL  NULL  79787  Using where`
index:
d    INDEX    79787

Is there something I'm doing wrong? the query inside the explain is taking 10 seconds.. seems as if it should take less than a second.

Thank you!

Answer 1

The reason your index is not being used is because d is a varchar (you mentioned this in a comment) and you have an integer in your where clause. If you change your query to:

SELECT a, b, c, d  FROM `table` WHERE d = '4013456';

it will be more likely to use an index. But a better solution might be to change the column to an integer and leave the query alone (if that's possible given your other circumstances).

Answer 2

If d is a varchar, you need to put it in quotes. Leaving the quotes out will not use the index properly.