Boost MySQL Sorting Speed: Optimize max_length_for_sort_data & sort_buffer_size

1. Increase max_length_for_sort_data Parameter

MySQL has two sorting algorithms: one that loads only the sort column(s) into memory and fetches other columns after sorting, and another that loads all required columns into memory, reducing I/O and being faster. The algorithm choice is controlled by the max_length_for_sort_data parameter. When the total length of all selected columns is less than this value, MySQL uses the second, in‑memory algorithm; otherwise it falls back to the first.

If sufficient memory is available to hold the non‑sorting columns, raising max_length_for_sort_data allows MySQL to select the faster algorithm.

2. Remove Unnecessary Return Columns

When memory is limited, simply increasing max_length_for_sort_data may force MySQL to split data into many segments for sorting, which can be counter‑productive. In such cases, eliminate columns that are not needed in the result set so that the total row length fits within the max_length_for_sort_data limit.

3. Increase sort_buffer_size

Raising sort_buffer_size does not directly switch MySQL to the second algorithm; instead, it reduces the need for MySQL to divide the data into multiple chunks during sorting. Fewer chunks mean fewer temporary tables and less overhead, improving overall sorting performance.