I often find doing the IN (subquery...) is really slow versus doing a join:
SELECT cutzip FROM customers INNER JOIN zipcodes ON customers.zipcode=zipcodes.zip WHERE degrees(acos(sin(radians(39.0788994)) * sin(radians(latitude)) + cos(radians(39.0788994)) * cos(radians(latitude)) * cos(radians(-77.1227036-longitude)) ) ) *60 *1.1515 < 5 That query may have un-matched ()'s, not sure. hard to tell =) Try a join. -jw On Thu, Jun 18, 2009 at 8:06 PM, Matt Neimeyer <m...@neimeyer.org> wrote: > I'm converting a PHP app from using Visual FoxPro as the database > backend to using MySQL as the backend. I'm testing on MySQL 4.1.22 on > Mac OSX 10.4. The end application will be deployed cross platform and > to both 4.x and 5.x MySQL servers. > > This query returned 21 records in .27 seconds. > > SELECT zip FROM zipcodes WHERE > degrees(acos(sin(radians(39.0788994))*sin(radians(latitude))+ > > cos(radians(39.0788994))*cos(radians(latitude))*cos(radians(-77.1227036-longitude))))*60*1.1515 > < 5 > > This query returned 21442 records in 1.08 seconds. > > SELECT custzip FROM customers > > This query is still running half an hour later, with a Time of 2167 > and a State of "Sending Data" (according to the mysql process list) > > SELECT custzip FROM customers WHERE custzip IN (SELECT zip FROM > zipcodes WHERE > degrees(acos(sin(radians(39.0788994))*sin(radians(latitude))+ > > cos(radians(39.0788994))*cos(radians(latitude))*cos(radians(-77.1227036-longitude))))*60*1.1515 > < 5) > > When I try to EXPLAIN the query it gives me the following... > > id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra > 1,PRIMARY,customers,index,NULL,cw_custzip,30,NULL,21226,Using where; Using > index > 2,DEPENDENT SUBQUERY,zipcodes,ALL,NULL,NULL,NULL,NULL,42144,Using where > > If it matters both tables are INNODB and both customers.custzip and > zipcodes.zip are indexed. We used a program called DBConvert from > DMSoft to convert the data so it's "exactly" the same on both the VFP > side and the MySQL side. With all that in mind... VFP returns the > exact same query in 5-10 seconds and that includes render time in the > web browser. > > By comparison... the query WHERE id IN (SELECT id FROM phone WHERE > phonedate >= '2001-01-01' AND phonedate <= '2009-06-18') returns > almost instantly. > > I'm at a complete loss... The suggestions I've seen online for > optimizing Dependent Subquery's basically revolve around changing it > from a sub-query to a join but that would require more > re-architecturing than I want to do... (Unless I'm forced) Especially > since more than a few of those solutions suggested precalculating the > distance between zipcodes which only works if the distances are known > (only allow 10, 50 and 100 mile radi for example) > > Any ideas? > > Thanks in advance! > > Matt > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=joh...@pixelated.net > > -- ----------------------------- Johnny Withers 601.209.4985 joh...@pixelated.net
