2 {{ upvoteCount | shortNum }}
2 {{ upvoteCount | shortNum }}

HELP!!! Need Database Expert, Please

by Michael D Price
2 replies
Hello All,

Wow this place has changed!

Anyway, down to business ...

I have a problem. I have developed an online script that will allow its users to perform a database search for the number of records within a radius of a given zipcode.

The results of this query must match (close enough) with the results of the same query using AccuZip software.

So far it functions exactly as it should, the way the calculations are performed, However it is too slow.

Currently taking over 9 seconds ...

Can someone take a look at the query, and make suggestions on how to improve upon it, to speed it up ...


-------------------
SELECT COUNT(*) as COUNT FROM (

SELECT unique_id, lat,lon,(((acos(sin((34.099800*pi()/180)) * sin((`lat`*pi()/180))+cos((34.099800*pi()/180)) * cos((`lat`*pi()/180)) * cos(((-118.412003- `Lon`)*pi()/180))))*180/pi())*60*1.1515) as distance FROM records)

as result WHERE distance <= 150;
-----------------------------

Any takers? the 150 is 150 mile radius, and the starting point is 34.099800,-118.412003
#database #database optimization #expert #geo location #mysql database
  • Profile picture of the author javrsmith
    You need to partition the data faster. Right now it's calculating for every record, even those located thousands of miles away.

    Are you able to partition faster? Perhaps you could add a clause to say "and substring('POSTAL',1,2) = '60'" This would let the database narrow down to the Chicago area right away. "98" for Seattle, etc.
    If you don't have postal, you might want to roughly calculate it in a batch job and store with each record.

    Alternatively, if the starting point is known when the record is created, you could store DISTANCE during the initial add. If the starting point is variable, this might not work. Maybe you have a limited number of starting points. You could store DISTANCE1, DISTANCE2, etc.

    I also see that the calculation "34.099800*pi()/180" is being performed twice for this query. You could pre-calculate this into a constant variable. I would also use "3.14159" for pi, eliminating 6 calls to the function.
    {{ DiscussionBoard.errors[10620952].message }}
  • Profile picture of the author David Beroff
    Continuing on javrsmith's idea, I'd precede the complex calculation with a simple check on the containing rectangle. i.e., If it's not in the rectangle, don't even bother doing the trig.
    Signature
    Put MY voice on YOUR video: AwesomeAmericanAudio.com
    {{ DiscussionBoard.errors[10622169].message }}

Trending Topics