Each MySQL connection, or thread, has a state that shows what it is doing at any
given time. There are several ways to view these states, but the easiest is to use the
SHOW FULL PROCESSLIST command (the states appear in the Command column). As a
query progresses through its lifecycle, its state changes many times, and there are
dozens of states. The MySQL manual is the authoritative source of information for
all the states, but we list a few here and explain what they mean:
Sleep
The thread is waiting for a new query from the client.
Query
The thread is either executing the query or sending the result back to the client.
Locked
The thread is waiting for a table lock to be granted at the server level. Locks that
are implemented by the storage engine, such as InnoDB’s row locks, do not
cause the thread to enter the Locked state.
Analyzing and statistics
The thread is checking storage engine statistics and optimizing the query.
Copying to tmp table [on disk]
The thread is processing the query and copying results to a temporary table,
probably for a GROUP BY, for a filesort, or to satisfy a UNION. If the state ends with
“on disk,” MySQL is converting an in-memory table to an on-disk table.
Sorting result
The thread is sorting a result set.
Sending data
This can mean several things: the thread might be sending data between stages
of the query, generating the result set, or returning the result set to the client.
It’s helpful to at least know the basic states, so you can get a sense of “who has the
ball” for the query.

Comments are closed.