RE: optimizing a query

Marginally offtopic, but there is an upcoming free "webinar" being offered by MySQL on query execution:

http://www.mysql.com/news-and-events/web-seminars/query-execution.php 

Might be worthwhile attending.

-----Original Message-----
From: Randy Burke [mailto:[email protected]] 
Sent: Thursday, January 18, 2007 2:43 PM
To: [email protected]
Subject: optimizing a query

I know this is not really a Perl question, Except that I use perl yo generate the page.

I have a report that is displayed on a webpage in an 10 x 9 table

Originally we did it as one query per cell, then we combined it into one monolithic query that returns one row with 72 columns

I am sure that there is some way to optimize this, maybe using sub-query, which I have not been able to grasp yet.

so here is the beast:

    my ($newdata, $noanswerdata, $timeddata, $misseddata, $highdata, $normaldata, $lowdata, $monitordata);

    # Open DB
    my $dbh = DBI->connect( "dbi:$datasrc", "$sqluser", "$sqlpassword") || error("Unable to open user database: $DBI::errstr");

    # Setup the Query
    my $query = qq{SELECT
            # New
            sum(TO_DAYS(creationdate)<TO_DAYS(now() - INTERVAL 6 DAY) AND status='O' AND date_format(lastcontact, "%Y")='0000' AND noanswer='0'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 6 DAY) AND status='O' AND date_format(lastcontact, "%Y")='0000' AND noanswer='0'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 5 DAY) AND status='O' AND date_format(lastcontact, "%Y")='0000' AND noanswer='0'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 4 DAY) AND status='O' AND date_format(lastcontact, "%Y")='0000' AND noanswer='0'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 3 DAY) AND status='O' AND date_format(lastcontact, "%Y")='0000' AND noanswer='0'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 2 DAY) AND status='O' AND date_format(lastcontact, "%Y")='0000' AND noanswer='0'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 1 DAY) AND status='O' AND date_format(lastcontact, "%Y")='0000' AND noanswer='0'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() ) AND status='O' AND date_format(lastcontact, "%Y")='0000' AND noanswer='0'),
            # No answer
            sum(TO_DAYS(creationdate)<TO_DAYS(now() - INTERVAL 6 DAY) AND status='O' AND noanswer='1'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 6 DAY) AND status='O' AND noanswer='1'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 5 DAY) AND status='O' AND noanswer='1'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 4 DAY) AND status='O' AND noanswer='1'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 3 DAY) AND status='O' AND noanswer='1'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 2 DAY) AND status='O' AND noanswer='1'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 1 DAY) AND status='O' AND noanswer='1'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() ) AND status='O' AND noanswer='1'),
            # Timed Callback
            sum(TO_DAYS(creationdate)<TO_DAYS(now() - INTERVAL 6 DAY) AND status='O' AND NOT date_format(timedcallback, "%Y")='0000'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 6 DAY) AND status='O' AND NOT date_format(timedcallback, "%Y")='0000'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 5 DAY) AND status='O' AND NOT date_format(timedcallback, "%Y")='0000'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 4 DAY) AND status='O' AND NOT date_format(timedcallback, "%Y")='0000'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 3 DAY) AND status='O' AND NOT date_format(timedcallback, "%Y")='0000'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 2 DAY) AND status='O' AND NOT date_format(timedcallback, "%Y")='0000'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 1 DAY) AND status='O' AND NOT date_format(timedcallback, "%Y")='0000'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() ) AND status='O' AND NOT date_format(timedcallback, "%Y")='0000'),
            # Missed Timed Callbacks
            sum(TO_DAYS(creationdate)<TO_DAYS(now() - INTERVAL 6 DAY) AND status='O' AND timedcallback<date_format(now(), '%Y-%m-%d') AND NOT date_format(timedcallback, "%Y")='0000'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 6 DAY) AND status='O' AND timedcallback<date_format(now(), '%Y-%m-%d') AND NOT date_format(timedcallback, "%Y")='0000'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 5 DAY) AND status='O' AND timedcallback<date_format(now(), '%Y-%m-%d') AND NOT date_format(timedcallback, "%Y")='0000'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 4 DAY) AND status='O' AND timedcallback<date_format(now(), '%Y-%m-%d') AND NOT date_format(timedcallback, "%Y")='0000'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 3 DAY) AND status='O' AND timedcallback<date_format(now(), '%Y-%m-%d') AND NOT date_format(timedcallback, "%Y")='0000'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 2 DAY) AND status='O' AND timedcallback<date_format(now(), '%Y-%m-%d') AND NOT date_format(timedcallback, "%Y")='0000'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 1 DAY) AND status='O' AND timedcallback<date_format(now(), '%Y-%m-%d') AND NOT date_format(timedcallback, "%Y")='0000'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() ) AND status='O' AND timedcallback<date_format(now(), '%Y-%m-%d') AND NOT date_format(timedcallback, "%Y")='0000'),
            # High Priority
            sum(TO_DAYS(creationdate)<TO_DAYS(now() - INTERVAL 6 DAY) AND status='O' AND priority='1'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 6 DAY) AND status='O' AND priority='1'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 5 DAY) AND status='O' AND priority='1'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 4 DAY) AND status='O' AND priority='1'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 3 DAY) AND status='O' AND priority='1'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 2 DAY) AND status='O' AND priority='1'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 1 DAY) AND status='O' AND priority='1'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() ) AND status='O' AND priority='1'),
            # Normal Priority
            sum(TO_DAYS(creationdate)<TO_DAYS(now() - INTERVAL 6 DAY) AND status='O' AND priority='2'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 6 DAY) AND status='O' AND priority='2'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 5 DAY) AND status='O' AND priority='2'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 4 DAY) AND status='O' AND priority='2'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 3 DAY) AND status='O' AND priority='2'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 2 DAY) AND status='O' AND priority='2'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 1 DAY) AND status='O' AND priority='2'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() ) AND status='O' AND priority='2'),
            # Low Priority
            sum(TO_DAYS(creationdate)<TO_DAYS(now() - INTERVAL 6 DAY) AND status='O' AND priority='3'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 6 DAY) AND status='O' AND priority='3'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 5 DAY) AND status='O' AND priority='3'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 4 DAY) AND status='O' AND priority='3'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 3 DAY) AND status='O' AND priority='3'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 2 DAY) AND status='O' AND priority='3'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 1 DAY) AND status='O' AND priority='3'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() ) AND status='O' AND priority='3'),
            # Monitor
            sum(TO_DAYS(creationdate)<TO_DAYS(now() - INTERVAL 6 DAY) AND status='M'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 6 DAY) AND status='M'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 5 DAY) AND status='M'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 4 DAY) AND status='M'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 3 DAY) AND status='M'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 2 DAY) AND status='M'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() - INTERVAL 1 DAY) AND status='M'),
            sum(TO_DAYS(creationdate)=TO_DAYS(now() ) AND status='M')
            FROM ticketsys
            WHERE project=?
              AND assignedto='POOL'
            GROUP BY project};
    my $sth = $dbh->prepare($query);
    $sth->execute($project) || error("Cannot execute: $DBI::errstr");
    my ($newolder, $newtodayminus6, $newtodayminus5, $newtodayminus4, $newtodayminus3, $newtodayminus2, $newtodayminus1, $newtoday, $naolder, $natodayminus6, $natodayminus5, $natodayminus4, $natodayminus3, $natodayminus2, $natodayminus1, $natoday, $tcolder, $tctodayminus6, $tctodayminus5, $tctodayminus4, $tctodayminus3, $tctodayminus2, $tctodayminus1, $tctoday, $mcolder, $mctodayminus6, $mctodayminus5, $mctodayminus4, $mctodayminus3, $mctodayminus2, $mctodayminus1, $mctoday, $hpolder, $hptodayminus6, $hptodayminus5, $hptodayminus4, $hptodayminus3, $hptodayminus2, $hptodayminus1, $hptoday, $npolder, $nptodayminus6, $nptodayminus5, $nptodayminus4, $nptodayminus3, $nptodayminus2, $nptodayminus1, $nptoday, $lpolder, $lptodayminus6, $lptodayminus5, $lptodayminus4, $lptodayminus3, $lptodayminus2, $lptodayminus1, $lptoday, $monolder, $montodayminus6, $montodayminus5, $montodayminus4, $montodayminus3, $montodayminus2, $montodayminus1, $montoday);
    $sth->bind_columns( undef, \$newolder, \$newtodayminus6, \$newtodayminus5, \$newtodayminus4, \$newtodayminus3, \$newtodayminus2, \$newtodayminus1, \$newtoday, \$naolder, \$natodayminus6, \$natodayminus5, \$natodayminus4, \$natodayminus3, \$natodayminus2, \$natodayminus1, \$natoday, \$tcolder, \$tctodayminus6, \$tctodayminus5, \$tctodayminus4, \$tctodayminus3, \$tctodayminus2, \$tctodayminus1, \$tctoday, \$mcolder, \$mctodayminus6, \$mctodayminus5, \$mctodayminus4, \$mctodayminus3, \$mctodayminus2, \$mctodayminus1, \$mctoday, \$hpolder, \$hptodayminus6, \$hptodayminus5, \$hptodayminus4, \$hptodayminus3, \$hptodayminus2, \$hptodayminus1, \$hptoday, \$npolder, \$nptodayminus6, \$nptodayminus5, \$nptodayminus4, \$nptodayminus3, \$nptodayminus2, \$nptodayminus1, \$nptoday, \$lpolder, \$lptodayminus6, \$lptodayminus5, \$lptodayminus4, \$lptodayminus3, \$lptodayminus2, \$lptodayminus1, \$lptoday, \$monolder, \$montodayminus6, \$montodayminus5, \$montodayminus4, \$montodayminus3, \$montodayminus2, \$montodayminus1, \$montoday);
    $sth->fetch();
    $sth->finish();

Any help in taming the monster would be nice.

Randy B.


INFORMATION IN THIS MESSAGE, INCLUDING ANY ATTACHMENTS, IS INTENDED FOR THE PERSONAL AND CONFIDENTIAL USE OF THE INTENDED RECIPIENT(S) NAMED ABOVE. If you are not an intended recipient of this message, or an agent responsible for delivering it to an intended recipient, you are hereby notified that you have received this message in error, and that any review, dissemination, distribution, or copying of this message is strictly prohibited. If you received this message in error, please notify the sender immediately, delete the message, and return any hard copy print-outs.

This message has been scanned for viruses by McAfee's Groupshield.

-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    http://lists.mysql.com/[email protected]