Benjamin Pflugmann wrote: > > 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.
I've done all three options in the past. Your best option would be #3 (produce a fully cross-computed table) because you can use indexed lookups rather than computed queries... *much* faster, and worth the massive amount of disk space it consumes. -- Tom Bradford The dbXML Project Open Source Native XML Database http://www.dbxml.org --------------------------------------------------------------------- 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
