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.

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?

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

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.