How to optimize this query in terms of execution speed

Question

This query should give me the closest element to the given :x :y in Cartesian coordinates.

 SELECT `type` FROM `mapgen_centers` ORDER BY SQRT(POW((:x - `x`), 2) + POW((:y - `y`), 2)) LIMIT 1 

It currently takes an average of 0.002 seconds, but that’s fine, but I feel it can be better, especially because I am currently running it very, very often and often, so that the entire script runs for several minutes.

It is possible (and if, how) to optimize this using any means available on a standard MySQL installation (procedures, functions, indexes, configuration, ...)

Answer 1

Besides removing this square root, I don't think it can be done better. What you should check is that the runtime is really O(n) , which should be, since you should look for at least all the elements. This can be done by checking that the runtime increases linearly with the size of the table in your database. So if it takes 10 milliseconds on a table of 100,000 rows, it takes only 100 milliseconds for a table of 1,000,000 rows ...

Answer 2

1. You can use the spatial extension of MySQL .

2. Build the SQRT function, because it is not needed when ordering.

Answer 3

Since you are calculating the distance between two points, I think you can use the MySQL spatial data type. There is a question in SO that can help you.

Alternatively, as stated in the comments above, you can build an index based on a pre-calculated value of your distance.