List:General Discussion« Previous MessageNext Message »
From:Johnny Withers Date:June 19 2009 2:28am
Subject:Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?
View as plain text  
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 <matt@stripped> 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=1
>
>


-- 
-----------------------------
Johnny Withers
601.209.4985
johnny@stripped

Thread
Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?Matt Neimeyer19 Jun
  • Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?Johnny Withers19 Jun
    • How to Optimize distinct with index周彦伟19 Jun
  • Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?Dan Nelson19 Jun
    • Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?Matt Neimeyer19 Jun
  • Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?Brent Baisley19 Jun
    • Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?Matt Neimeyer22 Jun
  • Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?Peter Brawley20 Jun
    • Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?Walter Heck - OlinData.com20 Jun
Re: How to Optimize distinct with indexDan Nelson19 Jun
Re: How to Optimize distinct with indexDarryle Steplight19 Jun
  • Re: How to Optimize distinct with indexMoon's Father26 Jun