MySQL Forums
Forum List  »  Performance

Re: GROUP causing Using Temporary Using Filesort
Posted by: RT Lee
Date: August 01, 2014 08:26PM

Thank you Rick!

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

Can you please elaborate on that? For example, what types of queries would do better with the TestID being first in the index?

Yes I appreciate the point about the indexes being a leading subset of another. This is just the result of me recently adding new indexes as I experimented to see how I could improve the query.

I can't remember why the 999999 part is there. Will have to dig that out. And thanks for the note about the HAVING clause.

What is interesting is that with the 99999 part, the HAVING clause, and the ORDER BY clause, the execution time is still right around 6 seconds. So those don't seem to affect the query speed at all.

> Your SELECT might benefit by adding a `resultcode` column to `summary`

I don't think that would work (unless I'm misunderstanding your point) because the resultcodes are associated with each of the individual test items (detail table). So the summary is like "here's a group of tests that were run" and the detail table is the "here is the result of each test in the group" - and the resultcode is specific to each of those test items. Make sense?

Rows are not being added at an alarming rate yet, but as the system scales out it is possible it can approach 1,000 per second or more. That is still quite a ways out tho. Good to have that on my radar as a future issue to be concerned with so thanks for pointing that out. The retention time for the data is relatively small so that should help keep the overall sizes from growing out of control.

> 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'm having a bit of a hard time grasping that idea. Mainly because this query is summarizing the overall results from the detail table, not the summary table. One thing I suppose we can do is have a detail summary table. So instead of having say 5,000 rows each day, and it having to scan through 5,000 rows times 90 days, it would have one row per testid per account per day. So the same query would involve summing over 90 rows instead of 450,000 rows (5,000 x 90). Is that what you are referring to by a summary table?

Just to double check - I'm not doing anything "silly" then that is causing the temporary;filesort right? I cannot be avoided with better indexes then?

Thank you!

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: GROUP causing Using Temporary Using Filesort
3434
August 01, 2014 08:26PM


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.