Re: Slow Query stuck on "Sending Data"
Posted by:
Rick James
Date: February 11, 2011 08:00PM
To build the 'perfect' index, do the following:
1. Start with all fields that are '=' some constant.
2. Add on one more thing - a range, group by, or order by.
3. Anything after that is ignored.
Therefore, (`country`,`state`,`city`,`visible`,`listing_date`,`category`,`property_type`)
stopped after listing_date, and could not take advantage of the last two fields.
This would have worked better:
(`country`,`state`,`city`,`visible`,`category`,`property_type`,`listing_date`)
assuming all 6 of the first 6 fields were in WHERE with '='. (The 6 could be in any order.)
So, why did
(`country`,`state`,`city`,`visible`,`listing_date`)
work so well? I suspect it did not. Instead, the freshly rebuild table (due to changing the indexes) was cleaner and/or better cached.