List:General Discussion« Previous MessageNext Message »
From:Richard Reina Date:February 1 2001 2:19am
View as plain text  
I figured out the problem.  As it turns out I was including a table in
the FROM that I was not even getting data from.  After making that
correction the query now takes less than 1 second.  MySQL ROCKS!

richard@stripped wrote:
> I know its a huge query but its taking about 90 seconds.  Can anyone tell me how I
> can speed it up?
> use DBI;
> $dbh = DBI->connect("DBI:mysql:database=rushload;","richard",
> "wwgd5492");
> my $q = "EXPLAIN SELECT pu.contno, DATE_FORMAT(pu.rpu_date, 'm-d'),
>         pu.rpu_time_from,
>         pu.rpu_time_untl, MIN(pu.sequence),
>         l.commodity, l.eqpmnt, l.carrierno, l.crname, l.xtra_pu,
>         MAX(do.sequence),
>         DATE_FORMAT(do.rdl_date, 'm-d'), do.rdl_time,
>, p.street,, p.state,, p.appt_must,
>         p.shp_hours, p.comm_shiped,,,
>, d.street,, d.state,
>, d.appt_must, d.rcv_hours,,
>         FROM loads l, pickup pu, carrier c,
>         dropoff do, shpr_rcvr p, shpr_rcvr d
>         WHERE pu.pu_date IS NULL
>         AND l.inact IS NULL
>         AND l.contno=pu.contno
>         AND l.contno=do.contno
>         AND p.sr_id=pu.cnsgnr_id
>         AND d.sr_id=do.consig_id
>         AND pu.sequence=1
>         GROUP BY contno
>         ORDER BY pu.rpu_date ASC";
> my $sth = $dbh->prepare($q);
> $sth->execute;