Posted almost 7 years ago. Visible to the public. Repeats. Linked content.

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:

Copy
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:

Copy
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:

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

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.

Owner of this card:

Avatar
Arne Hartherz
Last edit:
about 2 years ago
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Arne Hartherz to makandra dev
This website uses cookies to improve usability and analyze traffic.
Accept or learn more