# Why is this that complicated?

• February 6th, 08:45 AM
nte@index.hr
Why is this that complicated?
I have a table and I have to do a certain mathematical calculation for
each row.
Than I have to select the row with the minimal result of calculation.

I don't want to do the calculation more than once and I don't want to
sort the
whole table.

I did the following:

CREATE TEMPORARY TABLE Temp
SELECT Transformers.*,
SQRT(POW(1234567 - Transformers.X, 2) + POW(7654321 - Transformers.Y,
2)) AS Distance
FROM Transformers;
SELECT * FROM Temp
WHERE Distance = (SELECT MIN(Distance) FROM Temp);
DROP TABLE Temp;

This looks nice, but it doesn't work since I can execute only one
SELECT
on temporary table. Otherwise, temporary tables would fit perfectly
since
this request is repeated frequently (with input different numbers) and
for
multiple sessions.

How to solve this problem?

Many thanks.

• February 6th, 04:57 PM
strawberry
Re: Why is this that complicated?
On Feb 6, 8:45 am, hr wrote:

Sorry if this obvious but would a JOIN solve the problem?

• February 6th, 06:40 PM
Bill
Re: Why is this that complicated?
hr wrote:

I have a couple of tips:

1. If SQRT(expr1) < SQRT(expr2), then expr1 < expr2. So computing the
SQRT() on both rows just for the purpose of finding the row with the
least value is expensive and unnecessary. Eliminating this calculation
could make the query cheap enough that you can do it on every request.

2. The problem you have shown indicates that you calculate the distance
to a fixed point for all rows. If that were the case, you could
pre-calculate the distance and store it in a new column in the
Transformers table. Put an index on it, and it would be very fast to
query it thus:

SELECT * FROM Transformers ORDER BY distance ASC LIMIT 1

Anytime you insert a new row or change the X and Y, you would
re-calculate the distance for just one row and store it. Anytime you
change the fixed point you would need to re-calculate the distance for
all rows.

But I would assume that you don't use a fixed origin point for the
distance calculation. It's more likely that you are comparing all rows
against a different point from time to time. So this solution may not