Some of MySQL Performance Optimition Tips

1. Turn on trace on slow query

http://dev.mysql.com/doc/refman/4.1/en/slow-query-log.html

E.g. in my.ini
#minimum 1, default 10 seconds
long_query_time=3
#deprecated after 5.1.12
log-slow-queries=file.log
#as of 5.1.12, use this
#slow_query_log_file=file.log
#log the queries retrieving all the rows, not necessarily meaning no index existing on the table
log-queries-not-using-indexes
log=allquery.log
#binary log for replication setup
#log-bin=basename.ext
#max_binlog_size=1000000000
2. OPTIMIZE TABLE tblName
use it if large part of table have been deleted from the table. it reclaims the unused space and defragment the data file.

3. ANALYZE TABLE tbl1,tbl2...
It analyzes and stores the key distribution for a table.

4. SHOW INDEX FROM tbl;[\G]
list the indexes defined on the table. it also reports the cardinality(an estimate of the number of unique values in the index) of the index. The higher cardinality it is, the more likely the mysql will use the index.

5. high cardinality column should be indexed as left most index key
MySQL uses multiple-column indexes in such a way that queries are fast when you specify a known quantity for the first column of the index in a WHERE clause, even if you do not specify values for the other columns.