Users report that your application is too slow. After determining there is no physical system resource bottleneck, you turn your attention to the MySQL database.
Finding a Slow SQL Statement
Looking at the currently running MySQL connections with the SHOW FULL PROCESSLIST command, you find the following details:
mysql> SHOW FULL PROCESSLIST\G ... *************************** 6. row *************************** Id: 42 User: appl Host: localhost db: NULL Command: Query Time: 3 State: Query Info: SELECT * FROM inventory WHERE item_id = 16102176
This information shows the SELECT statement in the Info column has been running for 3 seconds via the value in the Time column.
What do you do now?
Confirming the Slow Query
Your first step when identifying a potential slow query is to confirm that it is slow when repeated. Verify that it was not a unique instance that might have occurred because of other factors such as locking or a system bottleneck.
Run and Time Your SQL Statement
Re-running the SQL statement using the MySQL command-line client or other client tool is an easy approach for verification:
mysql> SELECT * FROM inventory WHERE item_id = 16102176; Empty set (3.19 sec)
This confirms the query took more than 3 seconds to execute. When the query takes more than 10 milliseconds, the output from the MySQL command-line client is sufficient.
You should rerun only SELECT statements, because these do not modify any existing data. If your slow running query is an UPDATE or DELETE statement, you can simply rewrite this query as a SELECT statement for verification purposes. For example, if the SQL query was DELETE FROM inventory WHERE item_id = 16102176, you would have rewritten this query as the SELECT statement shown in this example to simulate the performance but not modify any information.
Generate a Query Execution Plan (QEP)
When MySQL executes an SQL query, it first parses the SQL query for valid syntax, and then it constructs a QEP that determines how MySQL will retrieve information from its underlying storage engines. To show the QEP the MySQL query optimizer is expected to construct for an SQL statement, simply prefix the SELECT statement with the EXPLAIN keyword like so:
mysql> EXPLAIN SELECT * FROM inventory WHERE item_id = 16102176\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: inventory type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 787338 Extra: Using where
This vertical output is obtained using the \G statement terminator with the MySQL command line client. This is helpful for parsing output via automated operations and also for any printed form such as in this book. Using the semicolon (;) terminator provides a column orientated approach that is generally easier to read with multiple rows of output.
In most cases, an EXPLAIN does not run the actual SQL statement; however, there are some exceptions when part of a SELECT statement might be executed for the optimizer to determine how to construct the QEP. An example is the use of a derived table in the FROM clause, which you would identify with the word DERIVED in the select_type column. You can find more information about these limitations in the MySQL Reference Manual at http://dev.mysql.com/doc/refman/5.5/en/from-clause-subqueries.html.
If you knew nothing about how to read a QEP, the first two columns you should scan are the indexes used and the number of rows affected. Any query that does not use an index signified by the key column in the preceding output can be considered a poorly tuned SQL query. The number of rows affected in evaluating this SQL statement, as signified by the rows column, contributes to an estimation of how much data is read and can directly correlate to the amount of time required to execute the query. The type column with a value of ALL is also an indicator of a potential problem.
Depending on the underlying storage engine, the number of affected rows will be either an estimate or an exact number of rows to be examined. Even when the number of affected rows is an estimate (such as when the InnoDB storage engine manages the table storage), the estimate is typically adequate for the optimizer to make an informed decision.
In this EXPLAIN example no index value was found in the key column. Because this is a single table SELECT statement, this can be considered a full table scan to search for any rows that match the WHERE clause predicate. The rows value can then be considered an approximate value for the number of rows read in order to find the occurrences matching item_id=16102176.
Leave a Reply