How to optimize MySQL and better performance

As per past experience working with MySQL and PHP, I feel that after completing initial phase of any Product / Project, the main task is to tune performance. And when it comes to performance , the first thing comes to any developer is to see h

ow MySQL works and how he/she can tune it up to get desired outcome.

Based on past experience, I have gathered some basic tip on how we can tune up MySQL and What precaution are required to avoid any fall on performance when database grows exponantialy.

1. Don’t query columns you don’t need, avoid using SELECT * FROM
2. Use numeric values (rather than alphabetical values) when performing a join
3. Use caching to reduce database load
4. Normalize tables to ensure data consistency
5. Don’t use HAVING when you can use WHERE
6. Use persistent connections
7. Better to have 10 quick queries than 1 slow one
8. Proper use of indexes improve performance
9. MySQL can search on prefix of indexes (ie: If you have index INDEX (a,b), you don’t need an index on (a))
10. Do not perform calculations on an index (eg: if you have an index for a column called salary, do not perform calculation such as amount * 2 > 10000)
11. Use INSERT LOW PRIORITY or INSERT DELAYED if you want to delay inserts from happening until the table is free
12. Use TRUNCATE TABLE rather than DELETE FROM if you are deleting an entire table (DELETE FROM delete row by row, whereas TRUNCATE TABLE deletes all at once)
13. Always use EXPLAIN to examine if your select query is inefficient
14. Use OPTIMIZE TABLE to reclaim unused space (Note: Table will be locked during optimisation, so only do it during low traffic time)
15. “LOAD DATA INFILE” is the fastest way to insert data into MySQL database (20 times faster than normal inserts)

Feel free to add more in comments if missed or any suggestion.

原文地址:  http://www.bytestechnolab.com/blog/2011/12/02/how-to-optimize-mysql-and-better-performance/