Analysing slow queries is mandatory in the process of optimization of all project.
A raw analysis of the result file can be quickly difficult. A lot of data, multiple line for each query detected, etc. can forbidden a global vision of the situation, in consequence, the correction become long and hard.
Some tools are available to help to do a rational optimization.
The own tool of MySql ! It can aggregate queries, sort by multiple criteria (query time, lock time, etc.). You can found all documentation and description on MySql Website or typing the following command:
$ mysqldumpslow --help Usage: mysqldumpslow [ OPTS... ] [ LOGS... ] Parse and summarize the MySQL slow query log. Options are --verbose verbose --debug debug --help write this text to standard output -v verbose -d debug -s ORDER what to sort by (al, at, ar, ae, c, l, r, e, t), 'at' is default al: average lock time ar: average rows sent at: average query time c: count l: lock time r: rows sent t: query time -r reverse the sort order (largest last instead of first) -t NUM just show the top n queries -a don't abstract all numbers to N and strings to 'S' -n NUM abstract numbers with at least n digits within names -g PATTERN grep: only consider stmts that include this string -h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard), default is '*', i.e. match all -i NAME name of server instance (if using mysql.server startup script) -l don't subtract lock time from total time
Percona’s MySQL Slow Query Log Analyser
Since 2006, an alternative to analyse log is available : Percona Toolkit. The results are similar with as main difference with MySql's tool, it also prints a real example of the query allowing a copy and paste to MySQL for execution with EXPLAIN.