Hello,
I know there's a thread that just discussed something similar, i read though it and did some testing.. but can't seem to come up with a better idea for indexes or changing the query.
I did try using < and > instead of BETWEEN, but the explain plan was identical.
The db is using the MyISAM engine (because i thought it was faster for performance)
Version 5.0.67
I looked at the Index Merge. I believe it only works on InnoDB? I converted the table to InnoDB and re-ran the explain plan but the type did not read index_merge.
Any ideas?
Here's the table:
CREATE TABLE IF NOT EXISTS `city` (
`city_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`city_name` VARCHAR(70) NOT NULL ,
`city_name_lower` VARCHAR(70) NOT NULL ,
`latitude` FLOAT(12,7) NOT NULL ,
`longitude` FLOAT(12,7) NOT NULL ,
`region_id` INT UNSIGNED NOT NULL ,
PRIMARY KEY (`city_id`) ,
INDEX fk_city_region (`region_id` ASC) ,
INDEX idx_lat (`latitude` ASC) ,
INDEX idx_lng (`longitude` ASC) ,
INDEX idx_region_city (`region_id` ASC, `city_name_lower` ASC));
Here's the query:
# Query_time: 26 Lock_time: 0 Rows_sent: 40 Rows_examined: 23697
SELECT
city_id,
city_name,
ROUND( 3959 *
acos(
cos( radians( 48.983334 ) ) *
cos( radians( latitude ) ) *
cos( radians( longitude ) - radians( 6.0833335 ) ) +
sin( radians( 48.983334 ) ) *
sin( radians( latitude ) )
),
1
) AS distance
FROM
city
WHERE
latitude BETWEEN 48.283333 AND 49.683334
AND
longitude BETWEEN 5.3833337 AND 6.7833333
ORDER BY
distance
LIMIT 40;
Here's the explain plan:
id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, 'SIMPLE', 'city', 'range', 'idx_lat,idx_lng,idx_lat_lng', 'idx_lng', '4', '', 20705, 'Using where; Using filesort'
Edited 2 time(s). Last edit at 04/10/2009 08:34AM by Randy Abson.