I have n tables that are joined in a select query, all the where conditions are on n-1 tables, and all the columns that can be sorted are on the remaining 1 table.
When doing a join mysql always selects one of the tables with where conditions as the "first" table meaning no index is used for sorting and I always get a tmp table/filesort which, depending on the num of records, can get slow.
From MySQL manual: "You are joining many tables, and the columns in the ORDER BY are not all from the first non-constant table that is used to retrieve rows. (This is the first table in the EXPLAIN output that does not have a const join type.)".
This makes sense to me because by selecting the table with the least amount of rows that match the where criteria as first drasticly reduces the resultset to work with....so forcing the join in reverse order isn't good as an index is used for sorting but the time required to execute is a lot longer because the resultset isn't filtered by the joins first and all the rows are scaned.
My question ...Is there any way to optimize this better... and how?