Re: Another Using temporary; Using filesort performance problem

[Drew Tanner]

It went from ~15 seconds to ~10 seconds. Still not great, but an improvement.

One of the differences was an index I had on the table.

I had a compound index (sch_production_did, sch_year). In this case the JOIN caused the production_dim table to get joined first (with the temp table and filesort). Changing to a straight join, flipped it around so the schedule_facts table was first.

When I flipped the index to (sch_year, sch_production_did), the optimizer chose the schedule_facts table first then the production_dim table - for both a JOIN and a STRAIGHT_JOIN.