MySQL Forums
Forum List  »  Performance

Re: select query in from clause creates problem
Posted by: sunil divyeshvar
Date: April 08, 2010 01:04AM

-- 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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: select query in from clause creates problem
1556
April 08, 2010 01:04AM


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.