When you don't have subselects, you have two options: temporary tables or
JOINed queries.In your case, I think the temporary table is the better way
to go.
I would also eliminate the ABS() check so that I can compare values
directly against the index. I know the math is correct your way but this
way you are comparing values directly against the column which means that
indexes can come into play.
SET @targetLat = 44.6, @targetLon = -123.8, @Delta = 3
CREATE TEMPORARY TABLE tmpDeltaData
SELECT city, state, country, latitude, longitude
FROM Londata
WHERE latitude BETWEEN (@[EMAIL PROTECTED]) AND (@targetLat + @Delta)
AND longitude BETWEEN (@targetLon - @Delta) AND (@targetLon +
@Delta)
SELECT city, state, country, latitude, longitude,
IF(latitude REGEXP '[0-9\\.]+$' AND longitude REGEXP'[0-9\\.]+$',
ROUND(DEGREES(ACOS((SIN(RADIANS(@targetLat))) *
(SIN(RADIANS(latitude))) +
(COS(RADIANS(@targetLat))) *
(COS(RADIANS(latitude))) *
(COS(RADIANS(@targetLon -longitude)))))
* 111),99999999) as distance
FROM tmpDeltaData
ORDER BY distance
DROP TEMPORARY TABLE tmpDeltaData
I would also test the WHERE clause from tmpDeltaData with your original
query to compare speeds of the two methods.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Gerald Taylor <[EMAIL PROTECTED]> wrote on 10/04/2004 10:33:22 AM:
> Query optimization question
>
> I am selecting from a single table but it has a lot of rows and it has
> a very involved calculation. What I really want to do is
> is FIRST restrict the number of rows so that the big calculation is only
> performed on the ones that are within 3 degrees.
>
> Using 4.0.20
>
> A sample query is given here:
> The application interpolates variable values such as 44.6 into
> the query string, so from mysql's
> point of view they are constants, right? And the explain doc
> says it optimizes constants, but it is looking at all the rows
> and I see why.
>
> SELECT city, state, country, latitude, longitude,
> IF(latitude REGEXP '[0-9]+$' AND longitude REGEXP'[0-9]+$',
> ROUND(DEGREES(ACOS((SIN(RADIANS(44.6))) *
> (SIN(RADIANS(latitude))) +
> (COS(RADIANS(44.6))) *
> (COS(RADIANS(latitude))) *
> (COS(RADIANS(-123.28 -longitude)))))
> * 111),99999999) as distance FROM londata
> WHERE ABS(44.6-latitude) <= 3.0 AND ABS(-123.28-longitude) <= 3.0 ORDER
> BY distance;
>
>
> I guess I can't do a subselect with my version...
> If I could what would it look like?
> Something like below? (I might be able to talk
> the powers that be into an upgrade.) And if I can't
> is it more horrible to manually create a temporary table
> and perform the calculations on it than it is to
> just do what I am doing?
>
> SELECT city, state, country, latitude, longitude,
> IF(latitude REGEXP '[0-9]+$' AND longitude REGEXP'[0-9]+$',
> ROUND(DEGREES(ACOS((SIN(RADIANS(44.6))) *
> (SIN(RADIANS(latitude))) +
> (COS(RADIANS(44.6))) *
> (COS(RADIANS(latitude))) *
> (COS(RADIANS(-123.28 -longitude)))))
> * 111),99999999) as distance FROM (SELECT * FROM londata
> WHERE ABS(44.6-latitude) <= 3.0 AND ABS(-123.28-longitude) <= 3.0)
> as
> sublon ORDER BY distance;
>
> Thanks.
>
> GT
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
