≡ Menu

How to optimize MySQL for better performance?

MySQL is a great database engine for small to medium sites and has great performance of SQL queries. I also read somewhere that Google uses MySQL for some of its functionality. MySQL is very rugged but its performance can be greatly improved. MySQL is now owned by Oracle. MySQL is the world’s most popular database. Being open source, it is constantly being improved upon by developers around the world.

MySQL is part of the LAMP stack that is very widely known and used. LAMP stands for Linux, Apache, MySQL and PHP. MySQL does not ship with any graphical user interface, but phpMyAdmin can be installed which makes the database very easy to use through an HTML frontend.

The performance of MySQL as it comes out of the box is quite good. The default MySQL database engine is MyISAM which is the most popular database engine. There is another engine called InnoDB. Some users have reported that SELECT queries run a bit faster on MyISAM as compared to InnoDB. However, InnoDB has row level locking which makes it more suitable for applications that have a lot of concurrent updates and selects.

Here are a few things and best practices that I have noted and experienced which makes the performance of MySQL faster. By tuning the performance of MySQL, you can see great speed increases and a better performance for the end user of your website:

1) Always create an index on a column that is widely used in SELECT queries

Creating an index improves the performance of SELECT queries by several orders of magnitude. If there is no index, the MySQL engine will go through all of the rows to match the key in the SELECT statement and this will considerably decrease performance. An index makes lookup take logarithmic time using a B-Tree therby increasing the performance of SELECTs considerably. So if you have a million rows, an index will be able to find keys in 20 steps while it will take a million steps for lookup without an index! However, note that it is very important that the index be on a key that is as rare as possible in the table. Unique keys work the best.

Indexes will also improve the performance of joins. Without indexes, MySQL might have to create temporary tables on which to run a query which is a performance, memory and disk hog!

Note that having too many indexes on a table will reduce the performance of INSERT and UPDATE statements.

2) Always have an integer ID as the primary key

Integer keys work best as they are fixed length and are very fast to compare. Having integer keys will improve performance upto 5 times and this is something that I have experienced first hand. Also as noted before, having a unique key as an index (or a primary key) will also cause a considerable improvement in speed of lookups. If you have multiple tables with the same key, having a seperate table that converts a key to an integer ID and then using that ID to lookup the other tables is a very recommended solution.

3) If using VARCHAR as an index, use as only as many characters as required

Smaller lengths of columns in VARCHAR columns will result in faster lookups. This is because smaller VARCHAR columns are faster to compare. This performance improvement will be very visible in very large tables having more than a million rows.

4) Create multi-column indexes if you expect frequent SELECT statements using those columns

Multi-Column indexes are very userful if you frequently have a SELECT statement that uses all those columns as a key. A multi-column index is an index on the string that is a concatenated string on all those columns. So instead of a lookup that uses each individual column index and then doing an intersection, a multi-column index will allow you to do a lookup using a single index.

SELECT * FROM table WHERE col1 = “string1″ AND col2 = “string2″

If you expect frequent use of queries like the above one, always create a multi-column index on col1 and col2.

5) Don’t use ORDER BY on large result sets

ORDER BY is a major performacne and memory hog. If the result set does not fit in memory, this will degrade performance by several orders of magnitude. ORDER BY is the most frequently mis-used query argument and should be avoided at all costs. Look for alternatives like sorting the table by an index to avoid ORDER BY. ORDER BY RAND() is its cousin which is also equally bad if used on a very large result set.

6) Use EXPLAIN on your queries to see if the indexes you created are actually being used

The EXPLAIN keyword in MySQL when put before a query outputs the execution plan of the query. It tells you which indexes are being used out of a possible set of indexes which is very useful in complicated queries. EXPLAIN can also be used on complicated joins and should especially be used for joins to verify on whether the join is happening using an index or by some other method like the creation of temporary tables and a brute force lookup of the key on the temporary table.

See this article for a tutorial on how EXPLAIN works.

7) Avoid SELECT *

Instead of SELECT *, use SELECT col1, col2 etc. This will reduce the size of the result set and also improve cache performance.

8) Should you use InnoDB or MyISAM?

InnoDB uses row level locking as compared to MyISAM which uses table level locking.

Some users have experienced that by using the database engine InnoDB, they were able to improve MySQL’s performance. However, it is also noted that MyISAM has better SELECT performance. So if you have a lot of concurrent UPDATE statements and SELECT statements, it might be useful to try InnoDB. But if your site is based on data that is loaded once and then uses only SELECT statements to render the loaded data, MyISAM might be the database engine of choice.

9) If using MyISAM, try low priority updates

Low priority updates were table to improve the performance of one of our sites by several orders of magnitude. See this article on how to enable low priority updates.

10) After the data has been loaded, use ANALYZE TABLE.

Once most of your data has been loaded in your table, use ANALYZE TABLE to update the statistics of the table. When you run an ANALYZE TABLE statement,MySQL updates several statistics about a table which then determines the query execution plan. If the statistics of a table are up to date, better execution plans can be constructed, especially for complicated joins in which the right index order can improve performance considerably.

So there you go! In this tutorial I outlined the top 10 steps which can be used to optimize the performance of MySQL databases. Try them out and share your experiences using comments!

{ 0 comments… add one }

Leave a Comment