How to optimize MySQL performance?

Optimization is a difficult task because to optimize a system you must know the in and out of it. You can optimize your system easily if have knowledge or understanding of your system. In MySQL the main performance booster is query optimization. Although other factors are also important in improving performance but by optimizing query you will see drastic impact on execution time. So here I am discussing some quick tips for improving MySQL performance.

Tips: For MySQL OS & hardware

1. Avoid reading data from disk as it is very slow process.
2. Use SAS instead of SATA.
3. Avoid using lower versions of RAID.
4. Never try to kill a MysQL process forcefully otherwise you will loose your data.
5. Use smaller disks as they are much faster than larger disks.
6. Your server should be dedicated to MySQL only.
7. Make sure your OS and data partitions are physically seperated.
8. Avoid using software RAIDS.

Tips: For MySQL schema optimization

1. Use indexes to speed things up but don't overdo it.
2. Instead of adding new indexes, always try to extend the existing ones.
3. Use triggers.
4. Use storage engine according to your data requirements.
5. Optimize tables.
6. If you are storing variable length strings use VARCHAR instead of CHAR.
7. Use more linking tables.
8. Try to use the smallest data type.
9. If your database has auditing tables then use ARCHIVE storage engine.
10. Try to avoid using NULL because when it indexed it take an extra byte per entry.

Tips: For MySQL backups

1. When taking backup of database make sure MySQL is closed.
2. Optimize all the table before backup.
3. Don't use replication during backup as it can make data inconsistent.
4. If you backup data using mysqldump then use it with -opt.
5. Check the size of tables, indexes and database after backup to monitor growth.
6. Test your backups weekly to make sure they are working properly.
7. Avoid using LVM snapshot for backup as it can make your data inconsistent.

Tips: For MySQL query optimization

1. Normalize all the tables.
2. Use 'OPTIMIZE TABLE' and 'REPAIR TABLE COMMAND' to improve the loading speed of your database.
3. Find out the slow queries in your database using Slow Query Log.
4. After identify the slow queries, use commands like EXPLAIN, SHOWPROCESS LIST to check the functionality of queries and their side effects on database.
5. Avoid using HAVING clause.
6. Use TRUNCATE command instead of DELETE for removing entire table.
7. Try to use GROUP BY rather than DISTINCT when required.
8. Count (*) can lock the entire table, so avoid using it.
9. Avoid MAX command instead use ORDER BY.
10. Use UNION in sub queries.
11. To remove table use DROP TABLE command instead of DELETE FROM.

These are the basic things that you can do to optimize MySQL. I hope it will help you. If you too want to share some tips, please go ahead.