List:General Discussion« Previous MessageNext Message »
From:Richard Reina Date:February 1 2001 2:19am
Subject:NEVERMIND:Re: SLOW QUERY
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;192.168.0.1","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.name, p.street, p.city, p.state, p.zip, p.appt_must,
>         p.shp_hours, p.comm_shiped, p.phone, p.contact,
>         d.name, d.street, d.city, d.state,
>         d.zip, d.appt_must, d.rcv_hours, d.phone, d.contact
>         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;
Thread
NEVERMIND:Re: SLOW QUERYRichard Reina2 Feb