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]