If your MySQL queries are taking a while to run, or you generally have a very heavy database driven site, the first thing that you want to do is take a look at your slow MySQL queries. This will log all MySQL queries that take a long time to process such as any that are selecting a large amount of data. Optimizing your MySQL queries will not only speed up your site, but reduce the load on the server leaving you room for other things that may require those freed up resources.

Shared hosting accounts (Launch, Power, Pro)

Unfortunately, this would not be possible to do on your own as it will require root access to the server to enable slow query logging, but you can still view more information via PHPMyAdmin. To do so, you will follow these steps:

  1. Log into PHPMyAdmin.
  2. Click on the Status tab at the top

Within here, you will see plenty of information on the particular queries that are being executed

If you would like more information on your MySQL queries, our support team will be happy to assist you via a support ticket.

VPS and Dedicated hosting

If you are on a VPS or Dedicated hosting account and you want to monitor your MySQL queries, you can fully monitor them on your own, although you will need root access to enable slow query logging which will provide you with much more information on the queries being run.

Without enabling slow query logging by our support team, you may still review running MySQL queries the same as a user on a shared server would as stated above.

If you DON'T have root access

If you do not have root access, support will need to enable this for you. After slow query logging is enabled, you will see a file named "slowqueries" within your home directory. From there, you can view it using the file manager in cPanel or via SSH access.

If you have root access

If you have root access, you can enable MySQL slow query logging quite easily via SSH. We have written a simple script that will enable it for you:

echo;echo -n "username: ";read cpuser; \
echo "log-slow-queries = /var/log/slowqueries" >> /etc/my.cnf; \
echo "long_query_time = 3" >> /etc/my.cnf;touch /var/log/slowqueries; \
chown mysql:wheel /var/log/slowqueries;chmod 664 /var/log/slowqueries; \
usermod -a -G wheel $cpuser; \
ln -s /var/log/slowqueries /home/$cpuser/slowqueries && service mysql restart

Simply copy and paste this full block of code into your SSH window as root. When it prompts for a username, add the cPanel user that you want slow query logging to be enabled for. It will allow you to create the same symlink to the slow query logs within your home directory as our support team would. From here, you can then view the file within your home directory via SSH or the cPanel file manager.

What am I looking for?

When looking through this file, you are looking for any queries that are taking an especially log time. MySQL queries such as ones that select a very large amount of data or search through a very large database can take some time as all of the information that it is pulling from will need to be processes. The best guideline to abide to when running your MySQL queries is to only select the data you need.

Did you find this article helpful?

We value your feedback!

Why was this article not helpful? (Check all that apply)
The article is too difficult or too technical to follow.
There is a step or detail missing from the instructions.
The information is incorrect or out-of-date.
It does not resolve the question/problem I have.
How did you find this article?
Please tell us how we can improve our Support Center:
Email Address
Optional, but our team may contact you for more information.
Like this Article?

Post a Comment

Name:
Email Address:
Comment:
Are you a bot?
Submit

Please note: Your name and comment will be displayed, but we will not show your email address.

Write New!
Do you want to publish a tutorial to our support center?

News / Announcements

SSL Certficate Warnings
Updated 2014-04-14 11:34 am EST
Hits: 1956
Heartbleed 0-day OpenSSL security bug
Updated 2014-04-14 04:43 pm EST
Hits: 5183

Related Questions

Here are a few questions related to this article that our customers have asked:
Server resource review
Would you like to ask a question about this page? If so, click the button below!
Ask a Question

Need more Help?

Search

Ask the Community!

Get help with your questions from our community of like-minded hosting users and InMotion Hosting Staff.

Current Customers

Chat: Click to Chat Now E-mail: support@InMotionHosting.com
Call: 888-321-HOST (4678) Ticket: Submit a Support Ticket

Not a Customer?

Get web hosting from a company that is here to help. Sign up today!