Identify Slow Queries using MySQL�s Analysis Tools
Jan 28, 2011, 22:34 (0 Talkback[s])
(Other stories by Rob Gravelle)
Re-Imagining Linux Platforms to Meet the Needs of Cloud Service Providers
"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."