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.

On Feb 6, 8:45 am, n...@index.hr wrote:
> 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.

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

nte@index.hr wrote:
> 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?


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
be appropriate in your case.

3. You could store an additional non-temporary table for all distances
from all rows to all fixed points that you may be interested in. This
table would become very long, but just storing a few numbers, the table
rows would not be very wide. With proper indexes it could still be
searched quickly.

Regards,
Bill K.