From: Peter Brawley
Date: June 19 2009 3:27am
Subject: Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?
List-Archive: http://lists.mysql.com/mysql/217915
Message-Id: <4A3B05B5.3060905@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------090007030200030806060205"

--------------090007030200030806060205
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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
>
>   

--------------090007030200030806060205--