Rsilitech

Ways to Speed Up MySQL

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.