Re: Speed up slow select - was gone blind

Hi folks.

I did send an explain analyze last week but for some reason it didn't 
appear on the list.

However, I've looked into the delay and it doesn't seem to be the SQL.  
I'm now looking into why my PHP seems to sit there for 20+ seconds 
doing nowt.

Thanks to everyone for the help anyway.

Gary
On Friday 01 Apr 2005 1:46 pm, you wrote:
> Can you send the EXPLAIN ANALYZE of each?  We can't really tell where
> the slowdown is without that.
>
> On Apr 1, 2005 12:32 PM, Gary Stainburn 
<gary.stainburn@ringways.co.uk> wrote:
> > Hi folks.
> >
> > I've got my select working now, but I haven't received the speed
> > increase I'd expected.  It replaced an earlier select which
> > combined a single explicit join with multiple froms.
> >
> > The first select is the old one, the second  one is the new one
> > (with a new join).  The new one takes 24 seconds to run while the
> > old one took 29.
> >
> > How can I redo the select to improve the speed, or what else can I
> > do to optimaise the database?
> >
> > original (ugly)
> > ~~~~~
> >
> > SELECT r.r_id, r.r_registration, r.r_chassis, r.r_vehicle,
> > r.r_fuel, r.r_pack_mats, r.r_delivery,
> >         (date(r.r_delivery) - date(now())) AS r_remaining,
> >         r.r_created, r.r_completed, r.r_salesman, r.salesman_name,
> >         d.d_des, de.de_des,
> >         u.u_id, u.u_userid, u.u_username, u.u_salesman, u.u_target,
> >         t.t_id, t.t_des,
> >         s.s_id, s.s_des,
> >         c.c_id, c.c_des,
> >         co.com_count, co.com_unseen
> > FROM (SELECT r.r_id, r.r_t_id, r.r_d_id, r.r_de_id, r.r_s_id,
> >         r.r_registration, r.r_chassis, r.r_c_id, r.r_vehicle,
> >         r.r_fuel, r.r_pack_mats, r.r_delivery, r.r_salesman,
> >         r.r_created, r.r_completed, r.r_u_id,
> >         u.u_username AS salesman_name
> >         FROM (requests r LEFT JOIN users u ON
> >                 ((r.r_salesman = u.u_id)))) r,
> >         users u,
> >         request_types t,
> >         request_states s,
> >         dealerships d,
> >         departments de,
> >         customers c,
> >         comment_tallies co
> > WHERE   (r.r_d_id = d.d_id) AND
> >         (r.r_s_id = s.s_id) AND
> >         (r.r_c_id = c.c_id) AND
> >         (r.r_t_id = t.t_id) AND
> >         (r.r_d_id = d.d_id) AND
> >         (r.r_de_id = de.de_id) AND
> >         (r.r_u_id = u.u_id) AND
> >         (r.r_id = co.r_id))
> > ORDER BY r.r_id;
> >
> > new
> > ~~~
> > SELECT r.r_id, r.r_registration, r.r_chassis, r.r_vehicle,
> > r.r_fuel, r.r_pack_mats, r.r_delivery,
> >         (date(r.r_delivery) - date(now())) AS r_remaining,
> > r.r_created, r.r_completed, r.r_salesman,
> >         sm.u_username as salesman_name,
> >         d.d_des, de.de_des,
> >         u.u_id, u.u_userid, u.u_username, u.u_salesman, u.u_target,
> >         t.t_id, t.t_des,
> >         s.s_id, s.s_des,
> >         c.c_id, c.c_des,
> >         co.com_count, co.com_unseen,
> >         pl.pl_id, pl.pl_desc as plates
> > FROM requests r
> > left outer join users sm on sm.u_id = r.r_salesman
> > left outer join users u on r.r_u_id = u.u_id
> > left outer join request_types t on r.r_t_id = t.t_id
> > left outer join request_states s on r.r_s_id = s.s_id
> > left outer join dealerships d on r.r_d_id = d.d_id
> > left outer join departments de on r.r_de_id = de.de_id
> > left outer join customers c on r.r_c_id = c.c_id
> > left outer join comment_tallies co on r.r_id = co.r_id
> > left outer join plates pl on r.r_plates = pl.pl_id
> > ORDER BY r.r_id;
> >
> > --
> > Gary Stainburn
> >
> > This email does not contain private or confidential material as it
> > may be snooped on by interested government parties for unknown
> > and undisclosed purposes - Regulation of Investigatory Powers Act,
> > 2000
> >
> > ---------------------------(end of
> > broadcast)--------------------------- TIP 9: the planner will
> > ignore your desire to choose an index scan if your joining column's
> > datatypes do not match

-- 
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000