Re: select query in from clause creates problem
-- 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;