MySQL Forums
Forum List  »  Performance

Re: Millions of rows, storing aggregate/rollup results help please
Posted by: Rick James
Date: September 01, 2016 04:35PM

Another way to tackle a problem like this is to keep a log of what queries users ask for. Then review them -- you will probably find that a couple of filters are almost always included. Build combinations that include common patterns, and let the other combinations be more costly. (Your app code would need to understand to either reach for a summary table (fast) or reach for the Fact table (slow).)

If you would like to present such a log, I will make a stab at what combos to make.

I see no provision for counting NULLs; is there such? If not, then maybe (let's say) Filter8 shows up only these three ways:

AND Filter8 = 0
AND Filter8 = 1
# Filter8 not specified, hence (0,1,NULL), which cannot be done with IN(...).

Or do you also have
AND Filter8 IN (0,1) -- same as Filter8 IS NOT NULL?

Options: ReplyQuote

Written By
Re: Millions of rows, storing aggregate/rollup results help please
September 01, 2016 04:35PM

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.