Ask the Expert

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.

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: