- Learn how the query optimizer works.
- Optimize your table formats.
- Maintain your tables (
- Use MySQL extensions to get things done faster.
- Write a MySQL UDF function if you notice that you would need some function in many places.
- Don’t use
GRANTon table level or column level if you don’t really need it.
- If possible, run
OPTIMIZE tableonce in a while. This is especially important on variable size rows that are updated a lot.
- Update the key distribution statistics in your tables once in a while with
myisamchk -a; Remember to take down MySQL before doing this!
- If you get fragmented files, it may be worth it to copy all files to another disk, clear the old disk and copy the files back.
- If you have problems, check your tables with myisamchk or
- Monitor MySQL status with:
mysqladmin -i10 processlist extended-status
- With the MySQL GUI client you can monitor the process list and the status in different windows.
mysqladmin debugto get information about locks and performance.
Use SQL for the things it’s good at, and do other things in your application. Use the SQL server to:
- Find rows based on
- Use keys wisely.
- Keys are good for searches, but bad for inserts / updates of key columns.
- Keep by data in the 3rd normal database form, but don’t be afraid of duplicating information or creating summary tables if you need more speed.
- Instead of doing a lot of
GROUP BYs on a big table, create summary tables of the big table and query this instead.
UPDATE table set count=count+1 where key_column=constantis very fast!
- For log tables, it’s probably better to generate summary tables from them once in a while than try to keep the summary tables live.
- Take advantage of default values on