What is considered as slow MySQL query?
Tuesday, December 23rd, 2008It’s pretty hard question. What you will consider as slow query execution depends on many factors. Those factors may include your server’s CPU, RAM, even disk space or simply the query design. While designing and constructing MySQL queries for your application you should always keep in mind that it’s always better to split one big query, over bloated with many joins, to few simpler queries. Eat your geeky pride and write usable queries.
Back to main theme, how to detect slow queries. Well, first you have to turn on slow query tracking on your MySQL server. Do it by putting these lines in [mysqld] section inside your /etc/my.cnf file:
log-slow-queries = slow.log
long_query_time = 20
First line defines name of the log file as well as tell MySQL server to log slow queries. Second line defines time in seconds. MySQL server will consider query as slow if its execution exceeds time defined. Don’t forget to restart your server to make these changes effective.
Once you’re set up you should periodically inspect slow.log. If it’s empty you should consider decreasing long_query_time. Happy hunting!