UK Sales: 0800 035 6364 | | We'll Call You

Speed up MySQL


MySQL databases run pretty well out of the box and you may not need to do any tuning at all. We don't recommend tweaking for tweaking sake. MySQL distribution contains bunch of sample my.cnf files which may be great templates to use. Typically they would already be much better than defaults if you chose correct one. Described below are some techniques you can use to improve your MySQL database performance. Generally speaking these are written from the point of few of a hosting company - MySQL servers are generally either standalone of share the server with a web server. Typically a MySQL will be hosting multiple database with multiple exclusive users.


Analyse Performance


Download and run MySQLTuner, and excellent free Perl script. You'll need root access to the server to run this script, and your MySQL servers administrative (root) login. The results of the script provide a variety of test results, and some recommendations. See screenshot below.
It's advisable to make a backup of your MySQL CNF file before making changes. Open your my.cnf file in a text editor, make any of the changes you decide to try, then restart MySQL. As some of the tests work best when MySQL has been running for a while, it's best to retest after a period of time has passed - how much will depend on how busy your server is, but 24 hours should be enough for a reasonable active database server.

MySQL Tuner Screenshot
MySQL Tuner Screenshot


Optimise Tables


One of the recommendations frequently seen in MySQL Tuner is that you should OPTIMIZE TABLES. The goal of optimising is to defragment tables for better performance. There are number of ways to do this, and they don't all require MySQL root access.


Optimise Tables Method 1


From the command line run this command:

mysqlcheck --optimize -A -u username -ppassword

mysqlcheck is a useful utility for checking, repairing, anaylsing and optimising MySQL tables. The -A option means all databases, and the --optimize option reclaims any unused space and defragments the data file.

Optimise Tables Method 2


This is for users who don't have direct access. Use your normal MySQL administration tool, be this a web based tool such as PHPMyAdmin, or a standalone desktop program such as Navicat or SQLyog. Select a particular table, and look for the Optimise option either in the context (right click) menu, or as part of the web administration page.


Further Reading

MySQL 5.0 Reference Manual: Optimization
MySQL Performance Blog
What to tune in MySQL Server after installation

2020Media is a leading provider of small business Wi-Fi hotspots. If you want to offer free Wi-Fi to your customers or users, our service could be exactly what you are looking for. Our Hotspots are managed in the cloud, ultra-reliable and give you loads of information about behaviour patterns of your users. Read More   Read more...
2020Media introduces Moodle Hosting - UK servers and UK Moodle experts setup your site ready to use. Read more...
Special Offer for Longer Registration Periods Register or renew a .com, .net or .org domain name for 5 years we'll include 1 year free*. Read more...
2020Media is now offering zero-click installation of the popular TYPO3 content management system. Read more...

Our blog is regularly updated with news, tips and industry opinion.


  • AggieWestons
  • baker-and-mckenzie
  • arts-council-england
  • Big Finish
  • barratt-developments-logo
  • City-of-Lincoln-council-logo
  • brora-logo
  • DenisMacShane
  • Construction-Industry-Council
  • colchester