Q

Increasing query/performance speed

Currently, we have a database with a main table containing 3 million records. We want to increase that to 10 million but that is not a possibility at the moment. Almost all 3 million records are deleted and replaced every day, throughout the day.

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

Then further,

...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
This Content Component encountered an error

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataCenter

SearchServerVirtualization

SearchCloudComputing

SearchEnterpriseDesktop

Close