Optimize MySQL Database Queries

[joseletk]

This is covered in detail everywhere, so just keep in mind a few important notes: One bad query statement running often can bring your site to its knees. Two or three bad query statements don't perform much different than one. In other words, if you optimize one query you may not see any server-wide speed improvement. If you find & optimize ALL your bad queries you may suddenly see a 5x server speed improvement. The log-slow-queries feature of MySQL can be very helpful.

How to log slow queries:

Code: [Select]

# vi /etc/rc.d/init.d/mysqld
Find this line:

Code: [Select]

SAFE_MYSQLD_OPTIONS="--defaults-file=/etc/my.cnf"
change it to:

Code: [Select]

SAFE_MYSQLD_OPTIONS="--defaults-file=/etc/my.cnf --log-slow-queries=/var/log/slow-queries.log"
As you can see, we added the option of logging all slow queries to /var/log/slow-queries.log
Close and save mysqld. Shift + Z + Z

Code: [Select]

touch /var/log/slow-queries.log
chmod 644 /var/log/slow-queries.log

restart mysql
service myslqd restart
mysqld will log all slow queries to this file.==============================================================================================