CODEWITHSUNDEEP
mysqlmysql-5.5
chicharito
chicharito

Reputation: 1087

Strange behaviour of mysql

I Have two types of queries

  1. ID IN ('123456',1234456)
  2. ID IN ('123456','1234456')

Strange issue is when i used mysql explain query 1st query is not using index on ID while second query is using index.

I am really confused why ?

ID IS unique key (int)

Upvotes: 0

Views: 68

Answers (2)

Krish R
Krish R

Reputation: 22721

You should never mix quoted and unquoted values in an IN list because the comparison rules for quoted values (such as strings) and unquoted values (such as numbers) differ. Mixing types may therefore lead to inconsistent results.

For example, do not write an IN expression like this:

SELECT val1 FROM tbl1 WHERE val1 IN (1,2,'a');

Instead, write it like this:

SELECT val1 FROM tbl1 WHERE val1 IN ('1','2','a');

Ref: http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#function_in

Upvotes: 4

Barmar
Barmar

Reputation: 781633

MySQL is apparently not able to use the index if the IN list contains a mixture of different datatypes. In your case, you mix strings and integers.

Upvotes: 3

Related Questions