When MySQL refuses to use your index, you can do a number of things wrong. One of them might be conditions with improper data types.

An example

UI/UX Design by

We make sure that your target audience has the best possible experience with your digital product. You get:

• Design tailored to your audience
• Proven processes customized to your needs
• An expert team of experienced designers

Read more Show archive.org snapshot

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 | |
+----+-------------+-------+-------+-------------------------------------+---------+---------+-------+------+-------+