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
