MySQL Forums
Forum List  »  Performance

select query in from clause creates problem
Posted by: sunil divyeshvar
Date: April 05, 2010 01:42AM

Hi all,

I am facing problem with following query ,
i want to know is that efficient to create index on varchar column ? not in creates problem here and the select query in from clause takes time as i think so.



please check my following query and try to help to tune it takes nearly 400 seconds,Index is created on TERABITZ_ID where ever it uses.




select p.ZIP AS NHOOD,
p.CITY AS CITY,
p.STATE AS STATE,
psub.LATITUDE AS LATITUDE,
psub.LONGITUDE AS LONGITUDE,
COUNT(p.TERABITZ_ID) AS TOTALPROP,
AVG(p.CURR_LISTPRICE) AS AVGPRICE,
psub.NHOODPROPCNT as NHOODPROPCNT

from SCHUBB.property_ptnf p,
SCHUBB.features_ptnf f,

(select distinct ptbl.ZIP,ptbl.CITY,ptbl.STATE,AVG(ptbl.LATITUDE) as LATITUDE,
AVG(ptbl.LONGITUDE) as LONGITUDE,
COUNT(ptbl.TERABITZ_ID) as NHOODPROPCNT
from property_ptnf ptbl FORCE INDEX (idx_lat,idx_long),
features_ptnf ftbl
where ptbl.TERABITZ_ID=ftbl.TERABITZ_ID AND
(ptbl.LATITUDE between 37.913867495923746 and 37.98222771312011) AND
(ptbl.LONGITUDE between -122.11870193481445 and -122.03681945800781) AND
ptbl.LATITUDE<>0 AND ptbl.LONGITUDE<>0 AND
ptbl.BEDS >= 2 AND ptbl.BEDS <= 4 AND
ptbl.BATHS >= 2 AND ptbl.BATHS <= 3 AND
ptbl.STATUS in ('Active','Contingent - Show','Leased/Option','Contingent - Release','Pending','Active Short Sale',
'Pending with Backup','Active Short Cont.','Active Court Cont.','Active Court Appr.','Active Rel. Clause',
'Price Change','Contingent','Back on Market','New','Contingent - Probate') AND
ptbl.DATASOURCE not in ('REINFOLINK','SFAR','ABRMLS','SOCAL','TSMLS','METROLIST','INCLINEVILLAGE')
GROUP BY ptbl.ZIP,ptbl.CITY) as psub

where p.TERABITZ_ID=f.TERABITZ_ID AND
p.ZIP<>'' AND
p.ZIP=psub.ZIP AND
p.CITY=psub.CITY AND
p.STATE=psub.STATE AND
p.BEDS >= 2 AND p.BEDS <= 4 AND
p.BATHS >= 2 AND p.BATHS <= 3 AND
p.STATUS in ('Active','Contingent - Show','Leased/Option','Contingent - Release','Pending','Active Short Sale',
'Pending with Backup','Active Short Cont.','Active Court Cont.','Active Court Appr.','Active Rel. Clause',
'Price Change','Contingent','Back on Market','New','Contingent - Probate') AND
p.DATASOURCE not in ('REINFOLINK','SFAR','ABRMLS','SOCAL','TSMLS','METROLIST','INCLINEVILLAGE')

GROUP BY p.ZIP,p.CITY

HAVING NHOODPROPCNT > 2

ORDER BY TOTALPROP DESC;

Options: ReplyQuote


Subject
Views
Written By
Posted
select query in from clause creates problem
3778
April 05, 2010 01:42AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.