MySQL Forums
Forum List  »  Performance

Re: select query in from clause creates problem
Posted by: sunil divyeshvar
Date: April 09, 2010 12:46AM

-- Thanks for reply,

-- I think there is nothing more we can do with that query , bt i got to know abt varchar index , count(*) , distinct - group by etc.

-- You can check output as u say.
mysql> SELECT COUNT(*)
-> from property_ptnf ptbl
-> where (ptbl.LONGITUDE between -122.118 and -122.036);
+----------+
| COUNT(*) |
+----------+
| 31494 |
+----------+
1 row in set (0.03 sec)

mysql> SELECT COUNT(*)
-> from property_ptnf ptbl
-> where (ptbl.LATITUDE between 37.91386 and 37.9822);
+----------+
| COUNT(*) |
+----------+
| 45960 |
+----------+
1 row in set (0.03 sec)

mysql> SELECT COUNT(*)
-> from property_ptnf ptbl
-> where (ptbl.LATITUDE between 37.91386 and 37.9822)
-> AND (ptbl.LONGITUDE between -122.118 and -122.036);
+----------+
| COUNT(*) |
+----------+
| 6175 |
+----------+
1 row in set (1 min 15.83 sec)



-- I have one other query which really became headache because it executes 50 times a day and take long time i think the UNION clause creates problem.


-- Please try to help me in my other query too.

(SELECT p.TERABITZ_ID,p.ID,'' AS MLS_TERABITZID,STATUS,DATEDIFF('2010-04-09', LISTDATE) as diffrence,DATASOURCE,ADDRESS,CITY,STATE,ZIP,CURR_LISTPRICE,SALEPRICE,BEDS,BATHS,LATITUDE,LONGITUDE,MLS_NUMBER,CAST(AREASQFT AS SIGNED) as AREASQFT,UNIT_NUMBER,p.TYPE,'' as PKTLIST,VTOUR_URL,b.NAME,b.CODE,p.SALEDATE,(SELECT group_concat(URL order by CAPTION DESC,URL SEPARATOR ',') FROM SOTHEBYMA.links_ptnf l WHERE l.TYPE='photo' and l.TERABITZ_ID=p.TERABITZ_ID) as PHOTOS,(SELECT concat(op.OPEN_DATE,',',op.OPEN_ST_TIME,',',op.OPEN_END_TIME) FROM SOTHEBYMA.openhouse_ptnf op WHERE op.TERABITZ_ID=p.TERABITZ_ID AND op.OPEN_DATE >= '2010-04-09' ORDER BY op.OPEN_DATE ASC limit 1) as MLSOPENHOMES,(SELECT concat(op.OPEN_DATE,',',op.OPEN_ST_TIME,',',op.OPEN_END_TIME) FROM POCKET_LISTING_QA.openhouse_ptnf op WHERE op.TERABITZ_ID=p.TERABITZ_ID AND op.OPEN_DATE >= '2010-04-09' AND op.SOURCE='COMMONWEALTH' ORDER BY op.OPEN_DATE ASC limit 1) as PKTOPENHOMES,(SELECT openhouse_directions.ID FROM POCKET_LISTING_QA.openhouse_directions WHERE openhouse_directions.TERABITZ_ID=p.TERABITZ_ID AND openhouse_directions.SOURCE in('COMMONWEALTH') limit 1) as OPENDIR FROM SOTHEBYMA.property_ptnf p, SOTHEBYMA.broker_ptnf b, SOTHEBYMA.features_ptnf f WHERE (p.TERABITZ_ID=b.TERABITZ_ID AND b.TYPE='Listing Broker') AND p.TERABITZ_ID=f.TERABITZ_ID AND (p.TERABITZ_ID,p.DATASOURCE) not in (SELECT enhp.MLS_TERABITZID,enhp.DATASOURCE FROM ENHANCED_LISTING_QA.property_ptnf enhp WHERE enhp.SOURCE='COMMONWEALTH') AND STATUS in ('Active','Extended','Price Change','Reactivated','Back on Market','New')) UNION (SELECT p.TERABITZ_ID,p.ID,p.MLS_TERABITZID,STATUS,DATEDIFF('2010-04-09', LISTDATE) as diffrence,DATASOURCE,ADDRESS,CITY,STATE,ZIP,CURR_LISTPRICE,SALEPRICE,BEDS,BATHS,LATITUDE,LONGITUDE,MLS_NUMBER,CAST(AREASQFT AS SIGNED) as AREASQFT,UNIT_NUMBER,p.TYPE,'ENH' as PKTLIST,VTOUR_URL,b.NAME,b.CODE,p.SALEDATE,(SELECT group_concat(URL order by CAPTION DESC,URL SEPARATOR ',') FROM ENHANCED_LISTING_QA.links_ptnf l WHERE l.TYPE='photo' and l.TERABITZ_ID=p.TERABITZ_ID) as PHOTOS,(SELECT concat(op.OPEN_DATE,',',op.OPEN_ST_TIME,',',op.OPEN_END_TIME) FROM SOTHEBYMA.openhouse_ptnf op WHERE op.TERABITZ_ID=p.MLS_TERABITZID AND op.OPEN_DATE >= '2010-04-09' ORDER BY op.OPEN_DATE ASC limit 1) as MLSOPENHOMES,(SELECT concat(op.OPEN_DATE,',',op.OPEN_ST_TIME,',',op.OPEN_END_TIME) FROM POCKET_LISTING_QA.openhouse_ptnf op WHERE op.TERABITZ_ID=p.MLS_TERABITZID AND op.OPEN_DATE >= '2010-04-09' AND op.SOURCE=p.SOURCE ORDER BY op.OPEN_DATE ASC limit 1) as PKTOPENHOMES,(SELECT openhouse_directions.ID FROM POCKET_LISTING_QA.openhouse_directions WHERE openhouse_directions.TERABITZ_ID=p.MLS_TERABITZID AND openhouse_directions.SOURCE in('POCKETLISTING','COMMONWEALTH') limit 1) as OPENDIR FROM ENHANCED_LISTING_QA.property_ptnf p, ENHANCED_LISTING_QA.broker_ptnf b, ENHANCED_LISTING_QA.features_ptnf f WHERE (p.TERABITZ_ID=b.TERABITZ_ID AND b.TYPE='Listing Broker') AND p.TERABITZ_ID=f.TERABITZ_ID AND p.SOURCE in ('COMMONWEALTH') AND STATUS in ('Active','Extended','Price Change','Reactivated','Back on Market','New')) order by CURR_LISTPRICE DESC;

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: select query in from clause creates problem
1373
April 09, 2010 12:46AM


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.