7 Easy Ways to Speed Up MySQL

Find out how to improve the speed and performance of your mysql queries quickly and easily.

There comes a milestone in a website's life where the traffic going to the site puts a strain on MySQL. This can cause a huge problem for novice web developers who haven't dealt with any issues to do with MySQL performance. The most common problems can be avoided by practicing the following best practices.

Only SELECT the fields you require

 All to often web developers will SELECT * which selects all fields for a record. However, you'd be surprised how much of a strain SELECT * can cause when running a query, especially on larger tables. So, instead of selecting absolutely everything, just select the fields you need. it takes up less memory on your server and thus allowing your database server to use that free memory to handle other queries and processes.

Use EXPLAIN to analyze your query

 This is probably one of the first recommendations you will run into when you are trying to optimise a MySQL query. EXPLAIN is a useful command in MySQL which can provide you some great details about how a query is ran, what index is used, how many rows it needs to check through and if it needs to do file sorts, temporary tables and other nasty things you want to avoid.

To use EXPLAIN, I firstly recommend downloading MySQL Workbench which will provide you with a query browser which you can run queries and see the results of them without sticking them in PHP,etc.

If you find that your query is using a file sort or temporary tables, you may be able to optimise your query and thus speed up it's execution time.

Use COUNT(1) to count the number of records

This point slightly crosses over into PHP. In PHP there is a function called mysql_num_rows(). It's purpose is to count the number of results returned by a query. While this sounds like a really good idea, my experience shows that this takes a whole lot longer than running a second COUNT(1) query on MySQL.

Apply indexes to useful fields

Indexes are like the letter tabs in a phone book. They make it quicker for you to find the data you need that match a certain criteria. Creating good indexes is an art and you need to think about how you will be querying your data. Things to consider are what fields are you going to use in your WHERE and how you are going to ORDER BY. When you have this information you will be able to create indexes that will speed up your queries instantly.

Use multiple inserts if possible

Do you have a lot of INSERT queries to run at once? Why not send them all to MySQL together. It will be executed a lot quicker. That can be especially useful when doing imports of data.

Use correct field types for the data

There are a lot of different field types available through MySQL, but I suggest getting familiar with the string and numeric types to get a better idea of which are best of your data. For instance, if you are only going to ever store the numbers 1, 2 and 3 in a field, use TINYINT rather than INTERGER. It takes up less space on your server.

Use the correct table storage engine type

 So many people opt-in for the MyISAM storage engine. Don't get me wrong, its a fantastic engine and can be really fast. But once you get to the point that your database is really large and you are running 100s maybe 1000s of queries per sec you will start to run into locked tables. This is where MyISAM fails. A table lock happens when MyISAM needs to run a query. The longer the query takes to run, the longer the lock. Because of this, other queries will have to wait for that query to finish and thus causing a slow down on your site. if you don't need the FULLTEXT index type, I suggest using InnoDB. It may be initially slower than MyISAM, but its much more reliable and alot safer in the case of a server crash.

Share your Hints/Tips

So, I have only touched on a few of the different ways to make MySQL perform better. I hope I have helped some web developers who currently have a slow website and need it sorting quickly. If you know of other ways of making MySQL perform better, add a comment below to share it with the rest of us.


Comments

Eddy @ 14th Jun 2010

Hi David, nice to meet you.
it's nice tutorial, but if you give some sample each point it will make reader understand about it. i'm sorry my english is bad :)

thanks...

Greg @ 24th Jan 2011

Useful tutorial

iphoner @ 19th Sep 2011

great post.
Very useful for me.
Thanks

venu @ 12th Jun 2012

Good and useful tutorial. Appriciated!

Aman @ 2nd Jul 2012

Amazing tips to Speed Up Mysql. In my next application I will keep in mind these tips. Thanks for sharing.

mediafire movies @ 6th Sep 2012

thanks for this information...
i am using InnoDB engine in mysql but i try found your first tip is wrong ..

i tried this

SELET * FROM ABC

this query run faster than this one

SELET xyz FROM ABC

Veli Atci @ 15th Nov 2013

i found setting "innodb_flush_log_at_trx_commit" parameter value to 0 (default value=1) is vrey helpful to reduce processing time in my program.

SHOW VARIABLE LIKE '%innodb_flush_log_at_trx_commit%';
SET GLOBAL innodb_flush_log_at_trx_commit=0;

Dean @ 20th Nov 2013

Hi Nice Tutorial,
The basic precaution is to limit the MySQL records in a query. It can speed up the query in a great way..
http://www.techrecite.com

Shyam @ 10th Dec 2013

Using exact datatype required will solve this.More over using RDBMS and exact select

K. Subash Chandra @ 21st Apr 2014

Thanks for the tutorial. It helps me a lot to get much knowledge about MySQL for improving performance. Thank you a lot.

Add a Comment

required
required, but not shared

© 2014 Supadupa Web Design