CODEWITHSUNDEEP
mysqlspatial
dewijones92
dewijones92

Reputation: 1359

Why is my spatial index slow?

I have two tables 

CREATE TABLE `city_landmark` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `location` geometry NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  SPATIAL KEY `spatial_index1` (`location`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=latin1


CREATE TABLE `device_locations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `location` geometry NOT NULL,
  PRIMARY KEY (`id`),
  SPATIAL KEY `spatial_index_2` (`location`)
) ENGINE=InnoDB AUTO_INCREMENT=1000004 DEFAULT CHARSET=latin1

City landmark rows: 10000
Device locations rows: 1000002

I want to find out the number of rows in 'device_locations' is within a certain proximity of each city landmark. 

SELECT *, 
       ifnull( 
       ( 
              SELECT 1 
              FROM   city_landmark cl force INDEX (spatial_index1) 
              where  st_within(cl.location, st_buffer(dl.location, 1, st_buffer_strategy('point_circle', 6)) ) limit 1), 0) 'in_range'
FROM   device_locations dl 
LIMIT 200;

This is really slow for some reason. Please suggest a better method?

For some reason it makes no difference if spatial_index1 is used or not.
With index: 2.067 seconds
Without index: 2.016 seconds

Upvotes: 0

Views: 271

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

I'm not familiar with mysql spatial, I use postgresql with postgis. But I will speculate a little bit.

I guess because you have to calculate the st_buffer you aren't able to get benefit of the index. The same is true with regular index when you do some function and alter the index field.

So if your city location is a point geometry, add another field city_perimeter and fill it with the result from st_buffer Then you can create a spatial index for city_perimeter.

Your query should become:

SELECT c.id, count(*)
FROM city_landmark c
JOIN device_locations d
  ON st_within(c.city_perimeter, d.location)
GROUP BY c.id

Upvotes: 1

Related Questions