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.
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.
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.
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.
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.
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.
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.
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.
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.
Hi, i'm David! I've been building websites since 1996. Through my experiences, I have gained a well-rounded knowledge of the design & development of websites. I founded Supadupa Web Design to help others learn from my experiences.
Follow @davidburleson
