When MySQL refuses to use your index, there's a number of things that you may be doing wrong. One of them might be conditions with improper data types.
An example
For example, let's assume you have a users table with an email field (varchar) which is indexed.
MySQL will use the index when your query is well-formed:
mysql> EXPLAIN SELECT * FROM users WHERE email = 'foo@example.com';
+----+-------------+-------+-------+----------------------+----------------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+----------------------+----------------------+---------+-------+------+-------+
| 1 | SIMPLE | users | const | index_users_on_email | index_users_on_email | 768 | const | 1 | |
+----+-------------+-------+-------+----------------------+----------------------+---------+-------+------+-------+
However, indexes are not used if you are passing incorrect data types. Like integers for string fields:
mysql> EXPLAIN SELECT * FROM users WHERE email = 12345;
+----+-------------+-------+------+----------------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+----------------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | users | ALL | index_users_on_email | NULL | NULL | NULL | 586514 | Using where |
+----+-------------+-------+------+----------------------+------+---------+------+--------+-------------+
It's just like that. Deal with it.
Works for integer columns
FYI, it does work if you pass strings for integers. So at least you're only half-screwed:
mysql> EXPLAIN SELECT * FROM users WHERE id = 123;
+----+-------------+-------+-------+-------------------------------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-------------------------------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | users | const | PRIMARY,index_users_on_id_and_token | PRIMARY | 4 | const | 1 | |
+----+-------------+-------+-------+-------------------------------------+---------+---------+-------+------+-------+
mysql> EXPLAIN SELECT * FROM users WHERE id = '123';
+----+-------------+-------+-------+-------------------------------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-------------------------------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | users | const | PRIMARY,index_users_on_id_and_token | PRIMARY | 4 | const | 1 | |
+----+-------------+-------+-------+-------------------------------------+---------+---------+-------+------+-------+
Posted by Arne Hartherz to makandra dev (2013-03-12 14:57)