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

Opscomplete powered by

Save money by migrating from AWS to our fully managed hosting in Germany.

• Trusted by over 100 customers
• Ready to use with Ruby, Node.js, PHP
• Proactive management by operations experts

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