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
Wednesday, May 7, 2008
Subscribe to:
Posts (Atom)