Re: Query optimization question

[gerald_clark]

Gerald Taylor wrote:
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

A Subselect won't help. You still have to scan the whole table to the 
calculation for the where clause.
Pre calculate your min and max lat. and lon.
WHERE  latitude <= @maxlat and latitude >= @minlat and longitude <= 
@maxlon and longitude >= @minlon

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]