Re: Slooooow query in MySQL.

[Aleksandar Vojnovic <muadib@xxxxxxxxxxxxxx>]

In addition to Chris's suggestions you should also alter the homeid 
column (set default to NULL and update the whole database which 
shouldn't be a problem) so you don't have to do a double check on the 
same column. I would also suggest that the TSCN_MEDIAid column should be 
an int not a varchar.

Aleksander

Chris wrote:
> Rob Adams wrote:
> > select h.addr, h.city, h.county, h.state, h.zip, 'yes' as show_prop,
> >    h.askingprice, '' as year_built, h.rooms, h.baths,
> >    '' as apt, '' as lot, h.sqft, h.listdate, '' as date_sold, 
> > h.comments, h.mlsnum,
> >    r.agency, concat(r.fname, ' ', r.lname) as rname,
> >    r.phone as rphone, '' as remail, '' as status, '' as prop_type,
> >    ts.TSCNfile as picture,
> >    h.homeid as homeid, 'yes' as has_virt
> >    from ProductStatus ps, home h, realtor r, ProductBin pb
> >    left join TourScene ts on ts.TSCNtourId = pb.PBINid and 
> > ts.TSCN_MEDIAid = '3'
> >    where ps.PSTSstatus = 'posted' and pb.PBINid = PSTS_POid and h.id 
> > = pb.PBINid
> >    and h.listdate > DATE_SUB(NOW(), INTERVAL 2 YEAR)
> >    and (h.homeid is not null and h.homeid <> '')
> >    and r.realtorid = pb.PBIN_HALOid limit {l1}, {l2}
> >
> > Here is the query.  I didn't know that it needed to have an ORDER 
> > clause in it for the limit to work properly.  I'll probably order by 
> > h.listdate
>
> If you don't have an ORDER BY clause then you're going to get 
> inconsistent results. The database will never guarantee returning 
> results in a set order unless you tell it to by specifying an order by 
> clause.
>
>
> To speed up your query, make sure you have indexes on:
>
> TourScene(TSCNtourId, TSCN_MEDIAid)
> ProductBin(PBINid, PBIN_HALOid)
> home(id, listdate)
> realtor(realtorid)
>
> If you can't get it fast, then post the EXPLAIN output.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php