Mysql Query State

By Avinash

October 02, 2011TutorialStart Discussion

Whenever you perform your query on MySQL database, MySQL Server will pass that query from the diffrent states. There are around 65 query states available. I am going show few most used states over here.

If you are using PHPMyAdmin then you will not able to see these states. You will need to use the MySQL Administrator for seeing these states.

Analyzing

The thread is calculating a MyISAM table key distributions (for example, for ANALYZE TABLE).

checking permissions

The thread is checking whether the server has the required privileges to execute the statement.

Checking table

The thread is performing a table check operation

copy to tmp table

The thread is processing an ALTER TABLE statement. This state occurs after the table with the new structure has been created but before rows are copied into it.

Copying to tmp table

The server is copying to a temporary table in memory.

Creating index

The thread is processing ALTER TABLE ... ENABLE KEYS for a MyISAM table.

creating table

The thread is creating a table. This includes creation of temporary tables.

Creating tmp table

The thread is creating a temporary table in memory or on disk. If the table is created in memory but later is converted to an on-disk table, the state during that operation will be Copying to tmp table on disk.

deleting from main table

The server is executing the first part of a multiple-table delete. It is deleting only from the first table, and saving columns and offsets to be used for deleting from the other (reference) tables.

deleting from reference tables

The server is executing the second part of a multiple-table delete and deleting the matched rows from the other tables.

executing

The thread has begun executing a statement.

Flushing tables

The thread is executing FLUSH TABLES and is waiting for all threads to close their tables.

FULLTEXT initialization

The server is preparing to perform a natural-language full-text search.

optimizing

The server is performing initial optimizations for a query.

preparing

This state occurs during query optimization.

query end

This state occurs after processing a query but before the freeing items state.

Removing duplicates

The query was using SELECT DISTINCT in such a way that MySQL could not optimize away the distinct operation at an early stage. Because of this, MySQL requires an extra stage to remove all duplicated rows before sending the result to the client.

removing tmp table

The thread is removing an internal temporary table after processing a SELECT statement. This state is not used if no temporary table was created.

rename

The thread is renaming a table.

Rolling back

The thread is rolling back a transaction.

Sending data

The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query.

Updating

The thread is searching for rows to update and is updating them.

Refer this for full list of MySQL Query State.

Share This Article