Thursday, November 11, 2010

MySQL Quirks: make sure you use the correct type

I was comparing these two MySQL queries. They looked the same, except for some extra joins and groups, but one was lasting for 5 seconds, the other for 2 minutes. I removed all extra stuff from the long lasting query to get to an almost identical query as the first, only to see it run for two minutes again. The only difference was the type of a WHERE clause. Instead of comparing date with "20101012" (string) I would compare it with 20101012 (integer). Apparently, the type conversion alone was invalidating any use of the index on the date column and made the query last forever. Good to know.