MySQL Performance Tips

• Tweet

• Tweet

I’m not an expert but I’ve had my own errors and trials along the way so here I am. These are all the tips I believe you should use when you are optimizing for MySQL database performance. Some stuff may be very handy, but they may not be good for performance.

Design

1. Normalize first, and de-normalize where appropriate. Table joins are never lags in performance. Use them!
2. Choose the appropriate collation / charset. UTF16 is omnipotent but it requires 2 times the storage. UTF8 delivers more characters but it’s slower than latin1. Unless it’s necessary, use latin1.
3. utf8_general_ci is slightly faster than utf8_unicode_ci, thus better in performance.
4. Use NOT NULL wherever possible.
5. Use indexes. Create indexes by analyzing the SELECTs you are doing on the table.
6. Indexing increases performance in selecting, yet decreases performance in inserting. So don’t index everything but only those necessary. And don’t make duplicate indexes.

Querying

1. Use smallest data types possible.
2. IN (…) is a total fail. Avoid it wherever possible.
3. ORDER BY RAND() is a fail too.
4. When you need to SELECT … to make sure there’s no duplicate unique index values before inserting something, use INSERT … ON DUPLICATE KEY UPDATE … instead.
5. If your app writes a lot, use InnoDB; if it reads a lot, use MyISAM. Here’s a comparison.
6. Never use LIMIT xxxx1, xxxx2 with large tables especially when offset xxxx1 is a pretty large number. Instead, give a more strict WHERE … clause with LIMIT xxxx2.
7. If a feature is deprecated, stop using it.
8. Avoid wildcards % or _ at the beginning of LIKE queries, e.g. LIKE ‘%bloom’ — this should be avoided. For instance, you should store email addresses reversed (REVERSED()) so it’s faster to search by domain.
9. If you have a large set of data rows to be inserted at once, create a combined INSERT query string in PHP first — that is, to bulk insert them in batches rather than one by one. LOAD DATA is also a better option than individual INSERT.
10. Prefer GROUP BY over DISTINCT.
11. Frequently visit Slow Query Log.
12. Avoid calculated values in WHERE … , LIMIT … , ORDER BY … , etc.
13. Use EXPLAIN … to get an idea of how MySQL is doing with your queries thus better optimize it. For example, it would suggest the indexes you should have created.
14. Don’t SELECT * … on everything, SELECT only what you need.
15. Be wary of lots of small queries that may be triggered in loops. Instead, combine them and use one large query wherever possible.

Storage Engines

1. Know the pros and cons of different storage engines and use them accordingly. But try them out in the test environment before making the decision.
2. Archive old data such as logs in ARCHIVE tables or MERGE tables.
3. BLACKHOLE engine is very fast for busy things like logs.

my.ini

1. Increase the default key buffer size: key_buffer = 128M
2. Increase the default number of tables that can be kept open: table_cache = 128
3. Increase the default sort buffer size:
   sort_buffer_size = 32M
   myisam_sort_buffer_size = 32M
4. Disable binary logging that is for data replication by commenting out the line like this:  # log-bin=mysql-bin

What to read from here:

1. Indexing: http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html
2. “LIMIT” Optimization: http://dev.mysql.com/doc/refman/5.0/en/limit-optimization.html
3. “ORDER BY” Optimizationhttp://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html
4. Efficient Paging: http://www.slideshare.net/suratbhati/efficient-pagination-using-mysql-6187107
5. Configuration Tweaks: http://docs.cellblue.nl/2007/03/17/easy-mysql-performance-tweaks/