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)
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:
Comparison of dissimilar columns may prevent use of indexes if values cannot be compared directly without conversion. Suppose that a numeric column is compared to a string column. For a given value such as 1 in the numeric column, it might compare equal to any number of values in the string column such as '1', ' 1', '00001', or '01.e1'. This rules out use of any indexes for the string column.force index..