Identify Slow Queries using MySQL�s Analysis Tools

"Using MySQL analysis tools, you can identify and optimize slow queries in order to eliminate the bottlenecks that they cause. Rob Gravelle examines the Slow Query Log and Explain command, which can both help identify slow queries.

"When you consider the mind-numbing amount of data that is being accessed every day by people around the world you realize that the work that relational databases do is truly miraculous! However, unlike real miracles, those provided by databases like MySQL don't just happen. They need a little help from their designers, developers, and administrators. That's where the MySQL analysis tools come in. Using these tools, you can identify and optimize slow queries in order to eliminate the bottlenecks that they cause. Today we will be examining the Slow Query Log and Explain command, which can both help identify slow queries.

"Enabling the Slow Query Log

"The slow query log contains all SQL statements that took more than a certain number of seconds to execute.

"Some things to keep in mind: First, MySQL writes a statement to the slow query log after it has been executed and all locks have been released, so the log order might be different from execution order. Second, note that the time to acquire the initial table locks is not counted as execution time. Third, queries handled by the query cache are not added to the slow query log, nor are queries that would not benefit from the presence of an index because the table has zero rows or one row."

Complete Story

Related Stories:

• Top 10 MySQL Best Practices(Jan 12, 2011)
• Limiting the Number of Rows in MySQL Result Set(Dec 23, 2010)
• Optimizing MySQL Query Retrieval Speed Through Table Joins(Dec 10, 2010)
• Eliminating Duplicate Rows from MySQL Result Sets(Nov 12, 2010)
• How Operator Precedence Affects MySQL SELECT Queries(Sep 22, 2010)
• MySQL SELECT Statement 101(Aug 27, 2010)