MySQL Server Performance Tips:
- Do regular maintenance of MyISAM tables with analyze and optimize commands.
- Choose better backup policy for DBs. It completely depends on which storage engine we are using.
- Always try compress static data so can save the space and no need to take backup often.
- Check MySQL memory utilization before set any high value for buffer parameters.
- While using InnoDB storage engine,always use INNODB_FILE_PER_TABLE.
- Use benchmarking to identify current performance of server.
- Keep in mind that regular backup should not affect the server performance during peak time.
- Always design schema properly, so when data will grow and if we want to change the structure or something it should be easy to scale.
DB Design Tips:
- Keep in mind that 3rd normal form is not always good. Design database as per requirement and try to use less joins.
- Always try to use boolean flags rather than create any varchar field.
- Indexes are always good for performance but dont create index on every field.
- Avoid to create duplicate indexes as it will not being used and consume more space.
- For the email address column in large tables, try to create reverse column and than make index on it. It will be faster.
- Use appropriate data types always. Unnecessary, avoid to give large value to data types.
- Try to avoid NULL. Rather than that give ‘ ‘ for text field and 0 for numeric field.
MySQL Query Optimization Tips
- Use EXPLAIN to profile the query execution plan
- Use Slow Query Log to track down slow queries
- Don’t use DISTINCT when you have or could use GROUP BY
- Use LOAD DATA instead of INSERT while inserting bulk records.
- Don’t use ORDER BY RAND() if you have less records. Its for large amount of data
- Use SQL_NO_CACHE when you are SELECT frequently updated data or large sets of data
- Always avoid wildcards at the start of LIKE queries. It will not use any index.
- Always keep in mind that complex queries should be split.
- Try to use caching for similar queries.
- Don’t use COUNT (*) on any Innodb tables for search,If you want total no of rows, than use SQL_CALC_FOUND_ROWS and SELECT FOUND_ROWS() functions.
MySQL Configuration Parameters Tips
- For InnoDB try to use innodb_flush_method (O_DIRECT). It will bypass OS filecache for innodb data files.
- Before set any parameter, check that its session level or global level parameter and set value according.
- Try to use session level parameters while doing any bulk operations.
- Run MySQL with STRICT SQL_MODE so server can give proper warnings.
- Maximum time, try to use –skip-name-resolve for connect mysql server through hostname.