Why is my spatial index slow?

Question

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

Juan Carlos Oropeza · Accepted Answer · 2018-03-26 20:03:32Z

1

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

answered Mar 26, 2018 at 20:03

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Why is my spatial index slow?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related