Mysql optimisation tool

Question

hi all,

Can anyone suggest a good MYSQL optimization tool which helps in finding the bottlenecks in a long query and hence help in optimization?? I am looking for a query profiler.

thanks...

Answer 1

Well, You mean Query Optimization? I guess EXPLAIN <query> is excellent in giving hits as to where the bottlenecks are. After which redefine you indexes & ...

UPDATE1: You could check out - MySQL optimization tools

UPDATE2: After digging up in my code, I see that I used to do 2 things for query optimization.

1. Turn on Slow Query Log - MySQL can record expensive SQL queries in the slow query log. You can define your expectations in seconds using parameter long_query_time.
2. mysqldumpslow command - After logging is turned on you can analyze the log contents using mysqldumpslow command. mysqldumpslow /path/to/your/mysql-slow-queries.log -t 10. This will show 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.