Every once in a while during php and MySQL development, you may come across a page of your website that for some reason is loading very slowly. And you can’t seem to figure out what is causing it. Maybe there are a number of MySQL lookups occurring, but you cannot seem to figure out which one is having the slow return in speed.
It is easy to notice web pages that are slow on any website. Even a 1 or 2 second delay and many users will find that they are not even going to wait to load the entire page. So in terms of speed, a 1 or 2 second mysql database return is going to be very noticeable on a web page.
As discussed in this Non-technical Guide to Isolating Slow MySQL Queries, there is a simply way to have your server log any query that takes over a certain amount of seconds. It is by adding a directive in your my.cnf configuration file.
By default MySQL does not log slow queries and the slow query time is 10 seconds. This needs to be changed by adding these lines under the [msyqld] section in /etc/my.cnf:log-slow-queries
long_query_time = 1