MySQL query using filesort and temporary

Question

I am using a simple MySQL query, but performance is very poor due to using ORDER BY. I cannot understand why MySQL uses filesort and temporary.

My request:

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 Event Structure

Table Event Indices

Table Structure EventLogFiles

EventLogFiles Index Table

UPDATE:

I tried to create two new indexes, but both of them 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

To use the index to select and sort, you need to have all of the following columns in the index with multiple columns in the Events Read: (ServerID, LogFileID, ReportID, TimeWritten).

MySQL , LogFileID, ON.

- , MySQL EventLogFiles, INNER JOIN STRAIGHT JOIN, , MySQL , .

Answer 2

temp , (ServerID, LogFileID, ReportID) TimeWritten, , auto_increment, ReportID, ORDER BY ReportID(PK - Order Physical) ort.