optimizing a query

"Randy Burke" <[email protected]>
Newsgroups gmane.comp.db.mysql.perl
Message-ID <[email protected]> 
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.
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.