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:
Copymysql> 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:
Copymysql> 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:
Copymysql> 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 | | +----+-------------+-------+-------+-------------------------------------+---------+---------+-------+------+-------+
Copymysql> 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 | | +----+-------------+-------+-------+-------------------------------------+---------+---------+-------+------+-------+
makandra has been working exclusively with Ruby on Rails since 2007. Our laser focus on a single technology has made us a leader in this space.