From the first month of my employment at SAP I was really impressed how the guys in support become crazy on SQL optimization. Deeply impressed on variety of tools for doing this on Oracle and DB2 I decided to carry out the same analysis on my own projects. The first question I’ve asked myself: “Do we have any native MySQL tools for sql optimization?”.

Yes, we do.

MySQL can record expensive SQL queries in the slow query log. Turning this feature on helps you to track all SQL queries which took longer than you expected. You can define your expectations in seconds using parameter long_query_time.

After the logging is turned on you can analyze the log contents using mysqldumpslow command. E.g. following call

mysqldumpslow /path/to/your/mysql-slow-queries.log -t 10

shows you top 10 performance killers. For each statement in the output you can see the number of identical calls, execution time in seconds, rows affected and the statement itself.

I have already tested slow query logging in diorama and found several performance killers in… phpBB. But that’s  another story ;-)