We LOAD INDEX INTO CACHE for all the relevant tables (key_buffer is set to 512MB). After that, we'll switch a flag in an info table to tell the searches to start pulling from these updated tables. We repeat the process on the table that was previously the search table. During this time, even simple queries can end up in the slow query log and I can't figure out why.
This query benchmarks at approx 0.25s SELECT fldResort AS dest_name, fldResort as ap_destname, fldDestinationAPC, min( fldPrice ) AS price, fldCountry as country, fldBoardBasis, fldFlyTime, sum( fldOfferCount ) as offercount FROM tblSummaryFull WHERE fldStatus = 0 AND fldDepartureDate >= '2006-12-27' AND fldDepartureDate <= '2007-01-02' AND fldDuration >= 7 AND fldDuration <= 7 AND tblSummaryFull.fldSearchTypes LIKE '%all%' GROUP BY dest_name, fldBoardBasis ORDER BY price.
I'm intrigued by the query cited:
...AND fldDuration >= 7 AND fldDuration <= 7...
In other words:
fldDuration = 7
...AND tblSummaryFull.fldSearchTypes LIKE '%all%'...
Doing a LIKE match with a leading % more or less guarantees a filesort. A guess, based solely on the name of the field and this wildcard match, suggests that some normalization could help.
Without the table structure and some sample content, I cannot say for sure, but I think this user might want to check out Merge and Cluster. Both take large amounts of data and spread it across several tables, so queries can operate on a much smaller subset.
This was first published in November 2006