MySQL Forums
Forum List  »  Performance

Re: GROUP causing Using Temporary Using Filesort
Posted by: Rick James
Date: August 01, 2014 11:45PM

> what types of queries would do better with the TestID being first in the index?
I don't know; you seem to think it is important, since you have:
> KEY `detail_testid` (`TestID`),

> this query is summarizing the overall results from the detail table, not the summary table.

Yes, and you build a Summary table from a Detail table (also called a "Fact" table in data warehousing literature).

> 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?

Yes.
And read the 5000 Detail rows each night to build the one Summary row.

> I'm not doing anything "silly" then that is causing the temporary;filesort right?
Correct.
> I cannot be avoided with better indexes then?
Correct.

You cannot build an index across two tables (as Peter mistakenly suggested). Hence, there is no way to optimize the SELECT or indexes to avoid the temp table and filesort.

The performance issue is the _real_ problem, the temp/filesort is merely a symptom. I'm suggesting a new Summary table to deal with speed.
(Many people on this forum freak out at temp/filesort; you are not alone. The 'real' solution is often in redesigning the indexes, restructuring the schema and/or modifying the SELECT. There is no "magic" to get rid of temp/filesort. Anyway, it is not the real villain.)

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: GROUP causing Using Temporary Using Filesort
3022
August 01, 2014 11:45PM


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.