Matt,
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 ( ...
For explanation & alternatives see "The unbearable slowness of IN()" at
http://localhost/artful/infotree/queries.php.
PB
-----
Matt Neimeyer 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
------------------------------------------------------------------------
No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 8.5.364 / Virus Database: 270.12.78/2185 - Release Date: 06/18/09 05:53:00
