MYSQL order by sorting leads to inefficient and small optimization

There is a master table, left join, and a small table paging statements twice, because order by causes sorting at execution time, as can be seen from the Using filesort in the execution plan and the time consuming creating sort index in profile.

From the trace file, you can see the calculation of filesort:

"join_execution": {"select#": 1, "steps": [{"filesort_information": [{"direction": "desc", "table": "`topxxx` `t`" "field": "create_date"}] / * filesort_information * /, "filesort_priority_queue_optimization": {"limit": 20, "rows_estimate": 2302749, "row_size": 264, "memory_available": 4194304 "chosen": true} / * filesort_priority_queue_optimization * /, "filesort_execution": [] / * filesort_execution * /, "filesort_summary": {"rows": 21, "examined_rows": 216594, "number_of_tmp_files": 0 "sort_buffer_size": 5712, "sort_mode": "} / * filesort_summary * /}] / * steps * /} / * join_execution * /}

After the sort field is added to the index, the sort operation is subtracted, and the sort field is placed at the front of the index.

Create index idx_topxxx1 on topic (create_date desc,is_del,is_en)

You can see in trace that indexes are used for sorting.

"reconsidering_access_paths_for_index_ordering": {"clause": "ORDER BY", "index_order_summary": {"table": "`topic``t`", "index_provides_order": true, "order_direction": "desc", "index": "idx_topxxx1" "plan_changed": true, "access_type": "index"} / * index_order_summary * /} / * reconsidering_access_paths_for_index_ordering * /}, {

For the record.