Re: 500 mil. rows table partitioning

[Rick James]

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.)