Tuesday, February 16, 2010

Optimize for high performance of mysql database

I have been using Mysql for most of my projects. While designing the database the question is to make it faster and effective so that the performance doesn't get affected in the long run.

Here are few guidelines that I follow and I am summarizing it here: 

  • Add indexes to columns which are frequently used in the where clause of your sql statement. ALTER TABLE [tablename] ADD INDEX (col);
  • You Don't have to  Index everything on the where clause.Primary Keys are already indexed.
  • If in a table lots of inserts are happening compared to select than dont index that table as that increases the overhead.
  • Avoid null values in column which is indexed.   
Optimize tables
  • This will speed up the loading time. OPTIMIZE table [tablename];

Optimize SQL queries
  • Run "explain query" statement to see which indexes are used.
  • Avoid using "select * from table" in your query. Fetch only those columns which are required.
  • Always have "Slow Query Log" ON.
  • Use stored procedures instead of inline queries when possible. As stored procedures are stored in the database server the execution time is faster with respect to inline queries.
  • Use correct datatype and set the length appropriately.
  • MYISAM is faster than INNODB; so use MYISAM when there is no transaction related function required.

No comments:

Post a Comment