It sounds like you want to use spatial indexes, but they only became available in v4.1 http://dev.mysql.com/doc/refman/5.0/en/create-index.html http://dev.mysql.com/doc/refman/5.0/en/using-a-spatial-index.html
You would need to switch your table type from InnoDB to MyISAM, which is fairly easy with ALTER TABLE. But that should allow you to drop all your calculations in the query. You don't have to do any re-architecture to change you subquery to a join: SELECT custzip FROM customers JOIN (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) AS zips ON custzip=zip Often times that simple change speeds things up considerably in MySQL. An explain should show it has a DERIVED TABLE if I recall correctly. Brent Baisley On Thu, Jun 18, 2009 at 9: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=brentt...@gmail.com > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
