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.
Related Q&A from Scott Noyes
Get suggestions for choosing MySQL character sets and field collations from expert Scott Noyes.continue reading
A MySQL expert describes two ways that the multi-master support can be used.continue reading
An expert describes where to find information on detection deadlock algorithms in MySQL.continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.