List:General Discussion« Previous MessageNext Message »
From:Matt Neimeyer Date:June 19 2009 2:01pm
Subject:Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?
View as plain text  
>> 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
>
> Ouch.  You might want to calculate the rectange enclosing your target
> distance, add an index on lat (and/or long), and add the rectangle check to
> your where clause: WHERE latitude BETWEEN lt1 AND lt2 AND longitude BETWEEN
> ln2 AND ln2.  That way mysql can use the index to pare down most of the rows
> without having to call all those trig functions for every zipcode.

I like this idea the best (it always bothered me running a query that
involved multiple mathmatical functions).

So... Here's the "scratch" php code I ended up with... Anyone see any
problems with it? The only problem I see is that I think the old code
was more "circular" this will be a square (within the limits of a
square on a non-spherical earth... etc.. etc..) ... so there will be
more zip codes included in the corners. If there are too many
complaints about that I might look at some sort of overlapping
rectangle scheme instead of a square.

function ChangeInLatitude($Miles) { return rad2deg($Miles/3960); }
function ChangeInLongitude($Lat, $Miles) { return
rad2deg($Miles/3960*cos(deg2rad($Lat))); }

$Miles = 5;

$OriginalLat = 39.0788994;
$OriginalLon = -77.1227036;

$ChangeInLat = ChangeInLatitude($Miles);
$ChangeInLon = ChangeInLongitude($OriginalLat, $Miles);

$MinLat = $OriginalLat-$ChangeInLat;
$MaxLat = $OriginalLat+$ChangeInLat;

$MinLon = $OriginalLon-$ChangeInLon;
$MaxLon = $OriginalLon+$ChangeInLon;

My only other question is... when I explained the new query... On the
dependent subquery it says possible keys are zip, longitude and
latitude but it used zip. It seems like a better index would be
longitude or latitude? On the primary query, even though there is an
index on custzip it doesn't say it's using ANY indexes. I should
probably leave well enough alone... but I'm curious.

Thanks again!

Matt
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