Re: GROUP causing Using Temporary Using Filesort

[Rick James]

Yes, GROUP BY costs something; it cannot be avoided with the Schema and SELECT that you have. But there are solutions.

But first...

It is usually wasteful (and costly for INSERTs) to have one index be a leading subset of another. You have two such examples:
KEY `acct-lastupdate` (`AccountID`,`LastUpdate`),
KEY `acct-lastupdate-counts` (`AccountID`,`LastUpdate`,`XCount`,`YCount`)

KEY `detail_runid` (`RunID`),
KEY `detail_runid_testid_result` (`RunID`,`TestID`,`ResultCode`)

However, in the second example, since TestID is longer than RunID, these might be better (depending on the queries you have):
KEY `detail_runid` (`RunID`),
KEY `detail_runid_testid_result` (`TestID`,`RunID`,`ResultCode`)

Back to GROUP BY...

Your SELECT might benefit by adding a `resultcode` column to `summary`. However, it would make `summary` bigger, which may slow down many queries. But... The ratio of 150K : 1M rows between Summary and Detail is about where things should be.

Or...
You could build (and maintain) another Summary table, one aimed at the SELECT in question. It would have TestID, AccountID, LastUpdate, and ResultCode, plus COUNT(*). This new Summary table might be considerably smaller than 150K rows, hence very fast to query.

I don't understand "and s.runid <= 9999999999". Are there rows that are junk, and you are trying to avoid them? You may need to add a flag to each Summary table, saying "Runid is valid or not".

As for the HAVING -- filtering in the WHERE clause is almost always faster than waiting until the HAVING clause.

I have dealt with Summary tables in several projects. I consider 6 seconds for a mere 1M rows to be unacceptable. I would be building a new summary table.

How fast are `detail` rows being added? How big will that table become? Past, say, 10M rows, you will encounter more problems. And at 1000 rows/second you would have to seriously rethink your indexes.