This perl script parses a MySQL slow_queries log file ignoring all queries less than $min_time and prints out how many times a query was greater than $min_time with the seconds it took each time to run. The queries are sorted by number of times it ran, with the most often query appearing at the bottom of the output.

Usage: mysql_slow_log_parser logfile

Also, it does to regex substitutions to normalize the queries...

$query_string =~ s/\d+/XXX/g;
query_string =~ s/([\'\"]).+?([\'\"])/$1XXX$2/g;

These replace numbers with XXX and strings found in quotes with XXX so that the same select statement with different WHERE clauses will be considered as the same query.

so these...

SELECT * FROM offices WHERE office_id = 3;
SELECT * FROM offices WHERE office_id = 19;

become...

SELECT * FROM offices WHERE office_id = XXX;

And these...

SELECT * FROM photos WHERE camera_model LIKE 'Nikon%';
SELECT * FROM photos WHERE camera_model LIKE '%Olympus';

become...

SELECT * FROM photos WHERE camera_model LIKE 'XXX';

*SO* if you use numbers in your table names, or column names, you might get some oddities, but I doubt it. I mean, how different should the following queries be considered?

SELECT car1 FROM autos_10;
SELECT car54 FROM autos_11;

I don't think it's a big deal.

Download: mysql_slow_log_parser

Turn something like this:

# User@Host: gimp[drool] @ algernon.retards.org [10.10.10.7]
# Time: 14  Lock_time: 0  Rows_sent: 127  Rows_examined: 87189

        select retard_user.id, cname.first,cname.last
        from retard_user,contact,cname,helmet
        where cname.id = contact.name_id
        and contact.id = retard_user.contact_id
        and retard_user.helmet_id = helmet.id
        and helmet.brand_id = 9
        and helmet.id = 143
        group by retard_user.id
        order by cname.last;
# User@Host: gimp[drool] @ algernon.retards.org [10.10.10.7]
# Time: 0  Lock_time: 0  Rows_sent: 2  Rows_examined: 1

        select workbook_code from workbook_defs where brand_id=9;
# Time: 010626 10:44:50
# User@Host: staff[staff] @ algernon.retards.org [10.10.10.7]
# Time: 0  Lock_time: 0  Rows_sent: 3  Rows_examined: 1
use lead_generator;

        SELECT answer_id, answer_text
        FROM pl_answers
        WHERE poll_id = 4
    ;
# Time: 010626 10:44:51
# User@Host: gimp[drool] @ algernon.retards.org [10.10.10.7]
# Time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 1
use webtie;

    select count(tm.id)
      from text_message tm, tm_status tms
      where tm.tm_status_id = tms.id and
            tm.to_id = 21259 and
            tm.to_group = 'retard_user' and
            tms.description = 'new';
# User@Host: gimp[drool] @ algernon.retards.org [10.10.10.7]
# Time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 1

    select count(tm.id)
      from text_message tm, tm_status tms
      where tm.tm_status_id = tms.id and
            tm.to_id = 10331 and
            tm.to_group = 'retard_user' and
            tms.description = 'new';

.
.
.
.

### 6 Queries 
### Taking 27, 28, 30, 35, 58 and 60 seconds to complete

        SELECT p.question_text, a.answer_text
          FROM pl_results r, pl_polls p, pl_answers a
         WHERE r.poll_id      = p.poll_id
           AND r.answer_id    = a.answer_id
           AND r.referrer_key = 'XXX';


### 7 Queries 
### Taking 58, 199, 575, 611, 660, 756 and 964 seconds to complete


        insert into retard_index_counts
        select keyword_id, count(retard_user_id) from retard_index group by keyword_id;


### 11 Queries 
### Taking 45 to 87 seconds to complete


        select retard_user_id from retard_index group by retard_user_id;