MySQL will not use indexes if you query the wrong data type

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

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