Why does enclosing a field in quotes take longer than without it?

Question

For example, I have this query:

SELECT * FROM a WHERE field IN (-1, "1")

The above query takes 20 times longer than this one to execute. Why?

SELECT * FROM a WHERE field IN (-1, 1)

Also, in the first case the index is not used, even with USE INDEX.

Have you tried running `EXPLAIN` to see if the execution is different? — AdamMc331, May 20 '15 at 17:36
Also, see the link on Mike's answer, as it explains both how the conversion can take up CPU time, as well as how the quotes may lead to scenarios where an index is not being used as a result. — AdamMc331, May 20 '15 at 17:39

score 2 · Accepted Answer · answered May 20 '15 at 17:34

2

Most likely because MySQL is converting field to CHAR, and not "1" to INT.

See how long this runs:

SELECT * FROM a WHERE field IN (-1, CAST("1" AS INT));

answered May 20 '15 at 17:34

mike.k

1

See [this](http://stackoverflow.com/a/6782019/3131147) answer for more info. It sounds like MySQL is actually converting "1" (a char) to an int(because that matches the type of field), but still, this conversion process takes time, and that's why it runs longer. – AdamMc331 May 20 '15 at 17:38

1 Answers1