As you can see, I have several more joins after this one. The number of self joins for match_scores depend on how many scoring_sections the user has configured.

Do you think it is likely I'll be able to find a combination of query structure and indices that will allow me to avoid temp/filesort?

If not, could I have some advice on the best my.cnf settings to make that temp/filesort as painless as possible? As it is, I save the results of this query into a "review" table that is used by the application for dynamic display. This way, resorting, filtering, and pagination don't impact this big nasty query.
If I could get this query down to just a few seconds, I'd be okay with it, but right now, it is taking minutes.

Comment

I didn't post so, but I actually did try ORDER BY NULL just to make sure.

As near as I can tell after reading the MySQL docs on how GROUP BY impacts query optimization, it doesn't look like it would be possible for my query to avoid at least the filesort and in many cases, the temporary table. This seems to be because I am grouping by columns in multiple tables.

The good news is that by adjusting the temp table size variables in my config, I was able to keep these queries solely in memory which provides enough of a performance increase to satisfy my needs.

Comment

No I don't think that you will be able to get rid of the filesort with a GROUP BY like that.

GROUP BY means that MySQL will need that the rows are in order according to the columns involved in the GROUP BY.
And since your group by columns come from different tables you can't have an index that solves this since indexes can't span over several tables.

If the GROUP BY columns are all part of one table and you have an combined index for them then MySQL can choose to use that index as you have seen.

Add this to my.cnf to raise the limit for in memory temp tables to 10MB:

tmp_table_size=10M

But does the query actually take minutes?
Because it looks like you don't have that many rows.

How much memory are you allowing for index cache?
The key_buffer_size variable in my.cnf.
If the problem is that this cache is to small and mysql has to read the indexes from disk which can slow down things a lot.