Re: select query in from clause creates problem
-- Sorry but still i am confuse, without spaces means should i write query in sequential line , i am sending like this so it is easy to read.
-- yes these two are big tables.
-- TERABITZ_ID is the Primary key of PROPERTY_PTNF table so i cant make it as a primaary key of features_ptnf table.
-- And what about this ?
Change
COUNT(ptbl.TERABITZ_ID) as NHOODPROPCNT
to
COUNT(*) as NHOODPROPCNT
-- i think that count(*) will check all the columns so i use only count(ptbl.TERABITZ_ID) , please tell me is it not correct ?
-- Please check following query and its explain plan after removing subquery.
select p.ZIP AS NHOOD,
p.CITY AS CITY,
p.STATE AS STATE,
COUNT(p.TERABITZ_ID) AS TOTALPROP,
AVG(p.CURR_LISTPRICE) AS AVGPRICE
from SCHUBB.property_ptnf p,
SCHUBB.features_ptnf f
where p.TERABITZ_ID=f.TERABITZ_ID AND
p.ZIP<>'' 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
ORDER BY TOTALPROP DESC;
-- EXPLAIN PLAN
1 SIMPLE p ALL PRIMARY,idx_zip \N \N \N 407846 Using where; Using temporary; Using filesort
1 SIMPLE f ref idx_teraid idx_teraid 8 SCHUBB.p.TERABITZ_ID 1 Using where; Using index
-- And all following queries take so much time to execute , i try to remove between and try to use <= , >= bt still its taking time.
SELECT COUNT(*)
from property_ptnf ptbl ,features_ptnf ftbl
where ptbl.TERABITZ_ID=ftbl.TERABITZ_ID
AND (ptbl.LONGITUDE between -122.118 and -122.036);
SELECT COUNT(*)
from property_ptnf ptbl ,features_ptnf ftbl
where ptbl.TERABITZ_ID=ftbl.TERABITZ_ID
AND (ptbl.LATITUDE between 37.91386 and 37.9822);
SELECT COUNT(*)
from property_ptnf ptbl ,features_ptnf ftbl
where ptbl.TERABITZ_ID=ftbl.TERABITZ_ID
AND (ptbl.LATITUDE between 37.91386 and 37.9822)
AND (ptbl.LONGITUDE between -122.118 and -122.036);
-- And about RAM size i dont know much so i am sending you output of free -m
# free -m
total used free shared buffers cached
Mem: 4051 3903 148 0 19 3121
-/+ buffers/cache: 763 3288
Swap: 1027 0 1027
Thanks,
And i hope you will reply fast asusuall.