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/

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

Reply via email to