Re: Slooooow query in MySQL.

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. -- Postgresql & php tutorials http://www.designmagick.com/