11

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)

Dmitry
  • 7,457
  • 12
  • 57
  • 83
  • 2
    Refer to this page http://dev.mysql.com/doc/refman/4.1/en/mysql-indexes.html .. 3rd bullet point `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.` – Meherzad May 28 '13 at 07:28
  • Actually, 10 is not a column (I saw this text in documentation before). So it can be converted to string of necessary type. – Dmitry May 28 '13 at 07:38
  • 1
    Check this http://sqlfiddle.com/#!2/f4807/1 for string to int conversion index is used while for int to string it does not follow even though I use `force index`.. – Meherzad May 28 '13 at 07:53

1 Answers1

19

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:

Markus Winand
  • 8,371
  • 1
  • 35
  • 44
  • is there a way to let MySQL throw the error since the column type and the value type don't match? – asgs Nov 30 '20 at 11:54