Hi. I am a really newbie in MySQL. I have a database with 8.500.000 (aprox.) rows and
I want to select a row like this:


SELECT *,((DEGREES(ACOS((SIN(RADIANS(36.517)) * SIN(RADIANS(f4)) + COS(RADIANS(36.517)) * COS(RADIANS(f4)) * COS(RADIANS((-4.883)) - RADIANS(f5)))))*69.09)*1.6) as km FROM locdb order by km limit 0,1


(I've divided the lines for better reading). The syntax selects the nearest city acording to the latitude (36.517) and longitude (-4.883). The latiude and longitude colums on my table are f4 and f5, respectively.


The problem is that (obviously) it takes about a minute to do it. Has anyone an idea how to improve the table or the syntax to get a better results?

Thanks.

this may be a case for ignoring one of the fundamental tents of normlisation - perhaps it would nake sense to store your intermediate values int he table. after all its unlikely they are going to change from read to read (unless your cities are on mud slide or earthquake zones then its unlilkely tyhey are going to move).

index these intermediate columns.

not knowing what your application is you could also reduce the number of rows handled by the calculation by say imposing a crude filter
ie if you are looking for
latitude (36.517)
longitude (-4.883).
then filter out the dreck by say returning rows with a latitude of 36.517 +/- n
and a longitude of -4.883 +/- n
-this wouldonly work if you are looking for a limited subset of data, obviuosly if you need all 8.5million rows to be examined then thats not going to do much good.

anything you can do to reduce the amount of work the trig fucntions the server has to do has got to be a bonus

Not knowing how the MySQL parser handles trig it may be better to pass what you can to the parser as numbers rather than function calls (for example it may by calulating the trig function each time - doubt it but y'never know [might be worth a try first off an see what difference if any it makes)

HTH