MySQL Optimization

Optimizing MySQL

  • Use EXPLAIN SELECT, SHOW VARIABLES, SHOW STATUS and SHOW PROCESSLIST.
  • Learn how the query optimizer works.
  • Optimize your table formats.
  • Maintain your tables (myisamchk, CHECK TABLE, OPTIMIZE TABLE).
  • 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 GRANT on table level or column level if you don’t really need it.

Maintenance

  • If possible, run OPTIMIZE table once 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 CHECK table.
  • 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.
  • Use mysqladmin debug to get information about locks and performance.

Optimizing SQL

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 WHERE clause.
  • JOIN tables
  • GROUP BY
  • ORDER BY
  • DISTINCT

Tips

  • 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=constant is 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 INSERT.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s