Optimizing SELECT Statements Take into account that the size of column values stored in the sort buffer is affected by the max_sort_length  system variable value. For example, if tuples store values of long string columns and you increase the value of  max_sort_length , the size of sort buffer tuples increases as well and may require you to increase  sort_buffer_size . To monitor the number of merge passes (to merge temporary files), check the  Sort_merge_passes status variable. •Increase the  read_rnd_buffer_size  variable value so that more rows are read at a time. •Change the  tmpdir  system variable to point to a dedicated file system with large amounts of free space. The variable value can list several paths that are used in round-robin fashion; you can use this feature to spread the load across several directories. Separate the paths by colon characters ( : ) on Unix and semicolon characters ( ; ) on Windows. The paths should name directories in file systems located on different  physical  disks, not different partitions on the same disk. ORDER BY Execution Plan Information Available With  EXPLAIN  (see Section 8.8.1, “Optimizing Queries with EXPLAIN” ), you can check whether MySQL can use indexes to resolve an  ORDER BY  clause: In addition, if a  filesort  is performed, optimizer trace output includes a  filesort_summary  block. For example: •If the  Extra  column of  EXPLAIN  output does not contain  Using filesort , the index is used and a filesort  is not performed. •If the  Extra  column of  EXPLAIN  output contains  Using filesort , the index is not used and a filesort  is performed. "filesort_summary": { "rows": 100, "examined_rows": 100, "number_of_tmp_files": 0, "peak_memory_used": 25192, "sort_mode": "<sort_key, packed_additional_fields>" } peak_memory_used  indicates the maximum memory used at any one time during the sort. This is a value up to but not necessarily as large as the value of the  sort_buffer_size  system variable. Prior to MySQL 8.0.12, the output shows  sort_buffer_size  instead, indicating the value of sort_buffer_size . (Prior to MySQL 8.0.12, the optimizer always allocates  sort_buffer_size  bytes for the sort buffer. As of 8.0.12, the optimizer allocates sort-buffer memory incrementally, beginning with a small amount and adding more as necessary, up to  sort_buffer_size  bytes.) The  sort_mode  value provides information about the contents of tuples in the sort buffer: • <sort_key, rowid> : This indicates that sort buffer tuples are pairs that contain the sort key value and row ID of the original table row. Tuples are sorted by sort key value and the row ID is used to read the row from the table. • <sort_key, additional_fields> : This indicates that sort buffer tuples contain the sort key value and columns referenced by the query. Tuples are sorted by sort key value and column values are read directly from the tuple. • <sort_key, packed_additional_fields> : Like the previous variant, but the additional columns are packed tightly together instead of using a fixed-length encoding. 1683