Using filesort indicates that an external sort action is required in addition to an index. The cause of this problem is usually directly related to the order by, and can be reduced or avoided by using appropriate indexes.

Order by using filesort

1. Create table and index first (SQL version 5.5.54)

/* category_id */ category_id (' category_id ', 'category_id', 'category_id', 'category_id', 'category_id', 'category_id', 'category_id' School_id int not null,/* school_id int not null,/* school_id int not null,/* buy_times int not null,/* buy times */ browse_times int not null/* */); Insert into course(title,category_id,school_id,buy_times,browse_times) values(' c2000 ', 2000, 001); Insert into 'course' (title,category_id,school_id,buy_times,browse_times) values('android ',2, 2000, 001); Insert into course(title,category_id,school_id,buy_times,browse_times) values('mysql ', 2000, 001, 2000); Insert into course(title,category_id,school_id,buy_times,browse_times) values(' Oracle ',2, 2000, 001); Insert into course(title,category_id,school_id,buy_times,browse_times) values('C# course ',1, 2000, 001); Insert into course(title,category_id,school_id,buy_times,browse_times) values('PS 标 ', 2000, 001); Insert into course(title,category_id,school_id,buy_times,browse_times) values(' C2000 ', 2000, 001); /* category_id (' category_id ', 'category_id', 'category_id') */ category_id (' category_id ', 'buy_times');Copy the code

Order by and group by generate a using filesort.

(1) Explain select ID from course WHERE category_id>1 order by category_id;

(category_id = ‘c002’ and ‘category_id’ = ‘C002’

(2) explain select ID from course WHERE category_id>1 ORDER BY category_id,buy_times;

(category_id = ‘c002’ and ‘category_id’ = ‘c002’ and ‘c002’ = ‘c002’ and ‘c002’ = ‘c002’ and ‘c002’ = ‘c002’ and ‘c002’ = ‘c002’ and ‘c002’

(3) Explain select ID from course WHERE category_id>1 order by buy_times;

From the left-most prefix principle, the ‘order BY’ field lacks the left-most category_id and hence generates a using filesort

(4) Explain select ID from course WHERE category_id>1 ORDER BY buy_times,category_id;

Using filesort is generated when the order of columns behind order BY does not match the order in the combined index

(5) Explain select ID from course order by category_id;

According to the left-most prefix rule, order by is followed by the left-most column in the index, so the index is used

(6) explain select id from course order by buy_times;

A using filesort is generated because the order by column does not contain the leftmost column in the index

(7) explain select id from course where buy_times > 1 order by buy_times;

Using fillesort is generated when a column after order by does not have a field in the leftmost column of the index

(8) Explain select id from course WHERE buy_times > 1 order BY category_id;

According to the leftmost prefix principle, the column after order by exists in the leftmost column of the index, so it will walk the index

(9) Explain select ID from course ORDER by buy_times DESC,category_id ASC;

A using filesort is generated when the order by column does not match the index

(10) Explain select ID from course ORDER BY category_id DESC,buy_times ASC;

Using filesort is not generated when the order by column is in descending order and the order by column is in ascending order

Summary: In the end, (3) (4) (6) (7) (9) (10) all produce using filesort.