Wednesday, May 7, 2008

MySQL not using index on simple condition

This was killing me, see if you can find why this query is slow.

The table has about 500,000 records.

SELECT * FROM standards WHERE (`standards`.`RefIdOld` = 1381320) LIMIT 1

describe of RefIdOld
+----------------+-------------+------
| Field | Type | Null | Key | Default
+----------------+-------------+------
| RefIdOld | varchar(32) | YES | MUL | NULL
+----------------+-------------+------

Index on RefIdOld:
Table: standards
Non_unique: 1
Key_name: RefIdsOld
Seq_in_index: 1
Column_name: RefIdOld
Collation: A
Cardinality: 22970
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE

Hint? Run an explain and you'll see it's not using the index, but it is listed as a possible index, just to mess with you.
Possible Keys: RefIdsOld
Key: null

Turn's our MySQL will ignore an index when the value you are trying to match on is of a different type then the column.

Our RefIdsOld is a VARCHAR, and we are using a integer in our query.

Solution:
Throw single quotes on it and you are gtg.
SELECT * FROM standards WHERE (`standards`.`RefIdOld` = '1381320') LIMIT 1