MySQL query using filesort and temporary

Question

I am using a simple MySQL query, but the performance is realy bad because of using ORDER BY. I can't figure out why MySQL is using filesort and temporary.

My query is:

EXPLAIN 
SELECT * FROM Events
INNER JOIN EventLogFiles ON ServerID = 42
AND Events.LogFileID = EventLogFiles.LogFileID
ORDER BY ReportID DESC , TimeWritten DESC 
LIMIT 100

This is the output of EXPLAIN:

Table Events structure

Table Events indexes

Table EventLogFiles structure

Table EventLogFiles indexes

UPDATE:

I tried to create two new indexes, but both still force MySQL to use filesort and temporary.

ALTER TABLE Events ADD INDEX  `ReportID_TimeWritten_ServerID_LogFileID` ( ReportID DESC,  TimeWritten DESC,  ServerID,  LogFileID)

ALTER TABLE Events ADD INDEX  `ServerID_LogFileID_ReportID_TimeWritten` ( ServerID,  LogFileID, ReportID DESC,  TimeWritten DESC)

Answer 1

In order to utilize the index for both selection and sorting, you need to have all of the following columns in a multi-column index on Events: (ServerID, LogFileID, ReportID, TimeWritten).

Currently, MySQL cannot utilize the existing multi-column index because it doesn't include LogFileID, which is in your ON clause.

If you ever have problems where MySQL is selecting from EventLogFiles first, you can change the INNER JOIN to a STRAIGHT JOIN to ensure that MySQL always elects from Events first, using your index.