Why Your MySQL Order‑By Is Slowing Down Queries and How Indexes Can Fix It

The article begins with a detailed analysis of an atypical slow SQL on the tcorder table, which stores tens of millions of rows for an e‑commerce platform. It shows the query’s execution statistics, highlighting high logical reads and a costly Using filesort operation caused by sorting on create_time DESC, order_id ASC without an appropriate index.

It then explains MySQL’s index structures, comparing B‑Tree and B+Tree, describing how B+Tree pages store records, the role of leaf and non‑leaf nodes, and how index height is calculated based on page size and record size. The article demonstrates how index length and row size affect B+Tree height and thus read efficiency.

Using the example query, the author shows how the optimizer chooses the idx_***_gmtcreate index, performs index condition push‑down (ICP), but still requires a filesort because the sort order does not match the index. By removing order_id from the ORDER BY clause, the query can use the ind_***_buyerid index for both filtering and sorting, eliminating the filesort and reducing logical reads.

The piece then covers MySQL profiling tools (SHOW PROFILE, INFORMATION_SCHEMA.PROFILING, EXPLAIN FORMAT=JSON) and how to interpret profiling stages such as starting, checking permissions, optimizing, executing, and Sending data. It lists common slow‑SQL causes—missing selective indexes, improper index column order, functions on indexed columns, optimizer mis‑selection, and filesort—and provides concrete mitigation steps.

Finally, it outlines a comprehensive SOP for index changes: analyzing slow‑SQL patterns, estimating column selectivity, designing optimal index column order, submitting DMS design tickets, performing phased gray‑release, silencing old indexes, and validating performance through online monitoring and load testing. The article concludes with a checklist of typical slow‑SQL issues and references for further reading.