Hi.
On Wed, Nov 14, 2001 at 10:02:54AM -0500, [EMAIL PROTECTED] wrote:
> Currently, I am using the following query:
>
> SELECT DISTINCT o.szZipCode FROM tblZips z,tblZips o WHERE
> z.szZipCode=$szZip AND (3956 * (2 *
> ASIN(SQRT(POWER(SIN(((z.dblLat-o.dblLat)*0.017453293)/2),2) +
> COS(z.dblLat*0.017453293) * COS(o.dblLat*0.017453293) *
> POWER(SIN(((z.dblLon-o.dblLon)*0.017453293)/2),2))))) < $iRadius
Well, the main problem is probably not the complexity of the
expression, but rather the expression itself. MySQL cannot use
indexes, when the columns are within expressions.
> where $iRadius is the Radius of search
> and $szZip is the zip code (char)
>
> This runs rather slowly over the 76 thousand zip codes in the US.
>
> Here are my three solutions:
>
> 1) Use a subquery (not an option on 3.23)
I don't see how that could speed up the query? Could you give an
example?
> 2) Compute the max/min latitude/longitude and then query on that range
> (proximity becomes a square, but that's not a big deal)
I would take that, rewrite it to a form where MySQL can use indexes
(i.e. o.dblLat between x and y) and keep the calculation above behind
an AND.
So the query can use index to restrict the matching rows to that
square and only calculate the complicated expression for all remaining
zip codes. That should give a nice performace boost.
> 3) Since I am using only 3 radius (10,25,50), compute for those three
> ranges over the entire table and make a new table (I guess around 1.5
> million records will be created, so around 350 MB of space)
You could reduce that to the 50 miles radius and use that condition to
reduces the matching rows with an index and afterwards compute the
complicated expression again.
Bye,
Benjamin.
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
