September 16, 2011

Locating Slow MySQL Queries: A Quick and Easy Way

Share on Tumblr

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
This will then log longer queries of over 1 second in the file /var/lib/mysql/mysqld-slow.log.
Simply review the longer queries found in this file and then test the query individually to see why it is having such a long load time.  In many cases, you should be able to enhance a complex mysql lookup to make it more efficient in the way it is grabbing the data.
Jeremy Oms

Jeremy Oms

Jeremy is an entrepreneur and leading manager of New Blood, Inc. Excited about all things Internet related, Jeremy spends much of his time project managing custom web applications and directing advanced search engine optimization efforts.

More Posts - Website

Follow Me:
TwitterFacebookGoogle Plus

Share on Tumblr

Related Posts

No Comments »

No comments yet.

RSS feed for comments on this post. TrackBack URL

Leave a comment