Re: Slooooow query in MySQL.

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.