MySQL Forums :: Performance :: Slow select on table with 1.8 million rows (filtering on lng/lat)


Advanced Search

Slow select on table with 1.8 million rows (filtering on lng/lat)
Posted by: Randy Abson ()
Date: April 10, 2009 08:17AM

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.

Options: ReplyQuote


Subject Views Written By Posted
Slow select on table with 1.8 million rows (filtering on lng/lat) 3529 Randy Abson 04/10/2009 08:17AM
Re: Slow select on table with 1.8 million rows (filtering on lng/lat) 2023 József Rekedt-Nagy 04/10/2009 01:58PM
Re: Slow select on table with 1.8 million rows (filtering on lng/lat) 2131 Rick James 04/10/2009 06:45PM
Re: Slow select on table with 1.8 million rows (filtering on lng/lat) 2031 Randy Abson 04/11/2009 08:36AM
Re: Slow select on table with 1.8 million rows (filtering on lng/lat) 1905 Rick James 04/11/2009 01:42PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.