I have been reading articles on how to optimise MySQL databases and queries and here are a few tips I have learnt and would like to share with you:
- Proper use of indexes improve performance
- Do not perform calculations on an index (eg: if you have an index for a column called salary, do not perform calculation such as salary * 2 > 10000)
- "LOAD DATA INFILE" is the fastest way to insert data into MySQL database (20 times faster than normal inserts)
- Use INSERT LOW PRIORITY or INSERT DELAYED if you want to delay inserts from happening until the table is free
- 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)
- Always use EXPLAIN to examine if your select query is efficient
- Use OPTIMIZE TABLE to reclaim unused space (Note: Table will be locked during optimisation, so only do it during low traffic time)
- Better to have 10 quick queries than 1 slow one
- Use caching to reduce database load
- Normalize tables to ensure data consistency
- Use persistent connections
- Don't query columns you don't need, avoid using SELECT * FROM
- MySQL can search on prefix of indexes (ie: If you have index INDEX (a,b), you don't need an index on (a))
- Don't use HAVING when you can use WHERE
- Use numeric values (rather than alphabetical values) when performing a join
Other resources:
- Optimizing MySQL: Queries and Indexes
- Optimizing and Tuning Your MySQL Database
- Tip for optimizing large MySQL database queries
- Four Ways to Optimize Your MySQL Database
- MySQL Presentations: Optimizing MySQL
[tags]optimising, fine tuning, mysql, database, query, optimisiation, sql, databases, php[/tags]
