How could this query be optimized in terms of execution speed

Question

This query should give me the closest element to a 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 0.002s on average which is okay, but I have the feeling this can be better, especially because I currently fire it very, very often and frequent, so that the whole execution of the script piles of to several minutes.

Can (and if, how) this be optimized through any means available on a standard MySQL installation (procedures, functions, indexes, configuration, ...)

you might consider to add a column with the result of the formula : `SQRT(POW((:x - x), 2) + POW((:y - y), 2))` and add a index on it — Stephan, Aug 08 '13 at 14:12
But `:x`, `:y` change with every execution? How can I create a column with that formula if the values are not known before? — F.P, Aug 08 '13 at 14:19
Do you really need to use the `SQRT` since it's only used for sorting? Isn't it that when (x'-x)^2 + (y'-y)^2 gets higher its square root gets higher too? — iCantSeeSharp, Aug 08 '13 at 14:24

score 3 · Answer 1 · answered Aug 08 '13 at 14:26

3

1.You may use MySQL spatial extension.

2.Strip the SQRT function, because it is unnecessary when ordering.

answered Aug 08 '13 at 14:26

adamsmith

5,759
4
27
39

Instead of 1. why not calculate the sum each time X or Y are updated? – iCantSeeSharp Aug 08 '13 at 14:35
@Souvlaki `x` and `y` must be two column names, thus we cannot pre-calculate. – adamsmith Aug 08 '13 at 14:46

score 2 · Answer 2 · edited May 23 '17 at 12:05

2

Since you're calculating a distance between two points, I think that you can use MySQL spatial data type In SO there is a question that could help you.

Alternatively, as they said in comments above, you can build an index by pre-calculated value of your distance.

edited May 23 '17 at 12:05

Community

1
1

answered Aug 08 '13 at 14:17

Alma Do

37,009
9
76
105

score 1 · Accepted Answer · answered Aug 08 '13 at 15:02

Apart from removing that square root, I don't think this can be done better. What you should check is that the execution time is really O(n), which it must be since you must search through all elements once at least. This can be done by checking that execution time augments linearly with table size in your database. So if on a table of 100000 rows it takes 10 milliseconds, it should take only 100 milliseconds on a table of 1000000 rows...

How could this query be optimized in terms of execution speed

3 Answers3