creating sort index

The creating sort index thread state indicates that a thread is processing a SELECT statement that requires the use of an internal temporary table to sort the data.

Supported engine versions

This thread state information is supported for the following versions:

• Aurora MySQL version 2 up to 2.09.2

Context

The creating sort index state appears when a query with an ORDER BY or GROUP BY clause can't use an existing index to perform the operation. In this case, MySQL needs to perform a more expensive filesort operation. This operation is typically performed in memory if the result set isn't too large. Otherwise, it involves creating a file on disk.

Likely causes of increased waits

The appearance of creating sort index doesn't by itself indicate a problem. If performance is poor, and you see frequent instances of creating sort index, the most likely cause is slow queries with ORDER BY or GROUP BY operators.

Actions

The general guideline is to find queries with ORDER BY or GROUP BY clauses that are associated with the increases in the creating sort index state. Then see whether adding an index or increasing the sort buffer size solves the problem.

Topics

• Turn on the Performance Schema if it isn't turned on
• Identify the problem queries
• Examine the explain plans for filesort usage
• Increase the sort buffer size

Turn on the Performance Schema if it isn't turned on

Performance Insights reports thread states only if Performance Schema instruments aren't turned on. When Performance Schema instruments are turned on, Performance Insights reports wait events instead. Performance Schema instruments provide additional insights and better tools when you investigate potential performance problems. Therefore, we recommend that you turn on the Performance Schema. For more information, see Turning on the Performance Schema for Performance Insights on Aurora MySQL.

Identify the problem queries

To identify current queries that are causing increases in the creating sort index state, run show processlist and see if any of the queries have ORDER BY or GROUP BY. Optionally, run explain for connection N, where N is the process list ID of the query with filesort.

To identify past queries that are causing these increases, turn on the slow query log and find the queries with ORDER BY. Run EXPLAIN on the slow queries and look for "using filesort." For more information, see Examine the explain plans for filesort usage.

Examine the explain plans for filesort usage

Identify the statements with ORDER BY or GROUP BY clauses that result in the creating sort index state.

The following example shows how to run explain on a query. The Extra column shows that this query uses filesort.

mysql> explain select * from mytable order by c1 limit 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: mytable
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2064548
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.01 sec)

The following example shows the result of running EXPLAIN on the same query after an index is created on column c1.

mysql> alter table mytable add index (c1);

mysql> explain select * from mytable order by c1 limit 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: mytable
   partitions: NULL
         type: index
possible_keys: NULL
          key: c1
      key_len: 1023
          ref: NULL
         rows: 10
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.01 sec)

For information on using indexes for sort order optimization, see ORDER BY Optimization in the MySQL documentation.

Increase the sort buffer size

To see whether a specific query required a filesort process that created a file on disk, check the sort_merge_passes variable value after running the query. The following shows an example.

mysql> show session status like 'sort_merge_passes';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 0     |
+-------------------+-------+
1 row in set (0.01 sec)

--- run query
mysql> select * from mytable order by u limit 10; 
--- run status again:

mysql> show session status like 'sort_merge_passes';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 0     |
+-------------------+-------+
1 row in set (0.01 sec)

If the value of sort_merge_passes is high, consider increasing the sort buffer size. Apply the increase at the session level, because increasing it globally can significantly increase the amount of RAM MySQL uses. The following example shows how to change the sort buffer size before running a query.

mysql> set session sort_buffer_size=10*1024*1024;
Query OK, 0 rows affected (0.00 sec)
-- run query