michi bloggt!

I posted an analysis of the SQL Log Files of twoday.net here on this blog a while ago:
-> SQL Log File Analysis

Back then I did this analysis by playing around with RegExps in my text-editor (in order to group statements), and performed the calculation in R.

Well, now max wrote a small little perl-script, which automated the whole process. All you need to do now, in order to find out your top-queries, is to enable logSQL for a while, and then call this script on it:
-> helmasqllogfileanalyzer (pl, 1 KB)

./helmaSqlLogFileAnalyzer.pl helma.twoday_net.sql.log

ms      count   sql
32035   2156    SELECT AV_TEXT.TEXT_ID FROM AV_TEXT WHERE AV_TEXT.TEXT_F_SITE = XXX AND (TEXT_PROTOTYPE = 'XXX' AND TEXT_ISONLINE > 0) ORDER BY TEXT_CREATETIME DESC
29067   1609    SELECT AV_TEXT.TEXT_TOPIC FROM AV_TEXT WHERE AV_TEXT.TEXT_F_SITE = XXX AND (TEXT_PROTOTYPE = 'XXX' and TEXT_ISONLINE > 0) GROUP BY TEXT_TOPIC ORDER BY TEXT_TOPIC
24899   1400    SELECT AV_TEXT.TEXT_DAY FROM AV_TEXT WHERE AV_TEXT.TEXT_F_SITE = XXX AND (TEXT_PROTOTYPE = 'XXX' and TEXT_ISONLINE = XXX) GROUP BY TEXT_DAY ORDER BY TEXT_DAY asc
24442   10030   SELECT AV_SKIN.* FROM AV_SKIN  WHERE AV_SKIN.SKIN_NAME = 'XXX' AND AV_SKIN.SKIN_F_LAYOUT = XXX AND AV_SKIN.SKIN_PROTOTYPE = 'XXX'
21766   8708    SELECT AV_IMAGE.* FROM AV_IMAGE  WHERE AV_IMAGE.IMAGE_ALIAS = 'XXX' AND AV_IMAGE.IMAGE_F_SITE = XXX AND (IMAGE_PROTOTYPE = 'XXX' and IMAGE_F_IMAGE_PARENT is null)
19574   8503    SELECT AV_TEXT.TEXT_ID FROM AV_TEXT WHERE AV_TEXT.TEXT_F_TEXT_STORY = XXX AND (TEXT_PROTOTYPE='Comment' AND TEXT_ISONLINE = XXX) ORDER BY TEXT_MODIFYTIME DESC
11081   2734    SELECT AV_TEXT.TEXT_ID FROM AV_TEXT WHERE AV_TEXT.TEXT_F_SITE = XXX AND AV_TEXT.TEXT_DAY = 'XXX' AND (TEXT_PROTOTYPE = 'XXX' and TEXT_ISONLINE = XXX) ORDER BY TEXT_CREATETIME desc
11035   31      SELECT AV_SITE.SITE_ID FROM AV_SITE WHERE (SITE_ISONLINE > 0 AND SITE_ISBLOCKED = XXX AND SITE_SHOW = XXX) ORDER BY SITE_LASTUPDATE desc
10072   3905    SELECT AV_TEXT.* FROM AV_TEXT WHERE AV_TEXT.TEXT_ID = XXX
...

The first column contains the cumulated amount of time, and the second column the number of occurences for the according group of SQL-statements.

Feel free to use/abuse this script for your own needs. Thanks max!