Re: slow queries using multi-column index

[Harrison Fisk]

Hi,

With the way that MySQL uses multi-column indexes, you should always have the = columns as the leftmost prefix.

So with your WHERE clause of:
photo > -2 and APPDOMAIN_ID = 0 and COUNTRY = 'DE' and REGISTERED > date_sub(curdate(), interval 7 day)

You should have an index as either:

(APPDOMAIN_ID, COUNTRY, photo)

or

(APPDOMAIN_ID, COUNTRY, REGISTERED)

Either of those should give a performance increase. If not please give the EXPLAINs of the query with both of those indexes and the original setup.

Harrison Fisk, Trainer and Consultant
MySQL AB, www.mysql.com