MySQL Forums
Forum List  »  Partitioning

Re: 500 mil. rows table partitioning
Posted by: Rick James
Date: July 21, 2011 08:46PM

The query optimizer takes statistics about the distribution of keys to come up with 5999565 (and other numbers found in EXPLAIN). Sometimes the number is actually calculated; this one was probably not.

I've done about all I can for your performance without going back to slamming key-value schema designs.

> I set it up exactly 20% of what I have (8GB) - 1.6GB
If this is your main query, and the query is now running entirely "Using index", then increasing key_buffer_size to, say, 5G may help.

The main query... I see site_id constant throughout all the joined tables; is that normal? What about the other fields? Are any of them "constant" within a query? (repeat and offsetid) If so, then we can look into normalizing out the combination that is always constant. Or they could be moved to the front of the INDEX, along with siteid. The goal is to minimize how much of the index is looked at. Think about finding your name in an alphabetized list, using "K" and "r" versus having the first and last letters (K and i).

Can you tell if you are I/O bound for this case?
> 13 vars -> 16371 sec
If so, the more RAM would help. (But maybe not more than 10x faster.)

Options: ReplyQuote

Written By
Re: 500 mil. rows table partitioning
July 21, 2011 08:46PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.