Menu
Table a_table has index on string_column.
I have a query:
SELECT * FROM a_table WHERE string_column = 10;
I used EXPLAIN to find that no indexes are used.
Why? Could you help me with MySQL documentation link?
Updated: Sandbox (SQL Fiddle)
352
In MySQL, you can use the >= operator to test for an expression greater than or equal to. SELECT * FROM contacts WHERE contact_id >= 50; In this example, the SELECT statement would return all rows from the contacts table where the contact_id is greater than or equal to 50.
If there is a choice between multiple indexes, MySQL normally uses the index that finds the smallest number of rows (the most selective index). If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to look up rows.
MySQL also uses indexes for LIKE comparisons if the argument to LIKE is a constant string that doesn't start with a wildcard character.
The essential point is that the index cannot be used if the database has to do a conversion on the table-side of the comparison.
Besides that, the DB always coverts Strings -> Numbers because this is the deterministic way (otherwise 1 could be converted to '01', '001' as mentioned in the comments).
So, if we compare the two cases that seem to confuse you:
-- index is used
EXPLAIN SELECT * FROM a_table WHERE int_column = '1';
The DB converts the string '1' to the number 1 and then executes the query. It finally has int on both sides so it can use the index.
-- index is NOT used. WTF?
EXPLAIN SELECT * FROM a_table WHERE str_column = 1;
Again, it converts the string to numbers. However, this time it has to convert the data stored in the table. In fact, you are performing a search like cast(str_column as int) = 1. That means, you are not searching on the indexed data anymore, the DB cannot use the index.
Please have a look at this for further details:
158
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
