MySQL: not quoting WHERE conditions slows query execution

Question

I just ran into a curious issue with MySQL. I had a very slow query, even if properly indexed, which was like this:

SELECT * FROM `tbl1` WHERE `parm1` = 'taleggio' AND `parm2` = 10

As said both parm1 and parm2 had indexes. The query was taking from 0.2s up to 120s, depending on the searched values.

Then I quoted the search terms like this:

SELECT * FROM `tbl1` WHERE `parm1` = 'taleggio' AND `parm2` = '10'

and now the execution takes 0.02s! Why that?

Answer 1

First query should give you an error as you're filtering items with string, the datatype would be CHAR or VARCHAR.

If you you integer, in same columns, the query will rub without quotes but it will be slow because of string datatype.

Second Query is proper written,

However for more performence, you can also create combine index on (parm1, parm2) with

CREATE INDEX tbl11 ON tbl1 (parm1, parm2))