MySQL Forums :: Performance :: select query in from clause creates problem


Advanced Search

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 3564 sunil divyeshvar 04/05/2010 01:42AM
Re: select query in from clause creates problem 1063 Rick James 04/06/2010 09:47PM
Re: select query in from clause creates problem 1105 sunil divyeshvar 04/06/2010 11:51PM
Re: select query in from clause creates problem 1205 Rick James 04/07/2010 08:00AM
Re: select query in from clause creates problem 1261 sunil divyeshvar 04/08/2010 01:04AM
Re: select query in from clause creates problem 1125 Rick James 04/08/2010 07:53AM
Re: select query in from clause creates problem 1093 sunil divyeshvar 04/09/2010 12:46AM
Re: select query in from clause creates problem 1119 Rick James 04/09/2010 09:55AM
Re: select query in from clause creates problem 932 sunil divyeshvar 04/09/2010 11:29PM
Re: select query in from clause creates problem 993 Rick James 04/10/2010 12:00AM


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.