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.
The thread is calculating a MyISAM table key distributions (for example, for ANALYZE TABLE).
The thread is checking whether the server has the required privileges to execute the statement.
The thread is performing a table check operation
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.
The server is copying to a temporary table in memory.
The thread is processing ALTER TABLE ... ENABLE KEYS for a MyISAM table.
The thread is creating a table. This includes creation of temporary tables.
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.
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.
The server is executing the second part of a multiple-table delete and deleting the matched rows from the other tables.
The thread has begun executing a statement.
The thread is executing FLUSH TABLES and is waiting for all threads to close their tables.
The server is preparing to perform a natural-language full-text search.
The server is performing initial optimizations for a query.
This state occurs during query optimization.
This state occurs after processing a query but before the freeing items state.
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.
The thread is removing an internal temporary table after processing a SELECT statement. This state is not used if no temporary table was created.
The thread is renaming a table.
The thread is rolling back a transaction.
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.
The thread is searching for rows to update and is updating them.
Refer this for full list of MySQL Query State.
Avinash Zala is leading various projects which deals with the various technology involved with the web. A combination of perfect technical and management skills. Avinash would like to chat with you and convert your imagination into the working system. You can get in touch with him on Facebook and Twitter.
View all posts by: Avinash