CODEWITHSUNDEEP
mysqlgeospatialspatial-queryspatial-index
user2400825
user2400825

Reputation: 184

Why doesn't this query use key(MySQL 5.7 spatial)?

I'm using MySQL 5.7.10, the storage engine is InnoDB. Below are the SQLs.
1. create table

CREATE TABLE `geo` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`geo` GEOMETRY NOT NULL,
PRIMARY KEY (`id`),
SPATIAL INDEX `geo` (`geo`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1

2. insert data

insert into geo(geo) values(ST_GeomFromText('POINT(108.875000 34.216020)'));
insert into geo(geo) values(ST_GeomFromText('POINT(109.569098 36.646357)'));
insert into geo(geo) values(ST_GeomFromText('POINT(109.550988 36.633384)'));
insert into geo(geo) values(ST_GeomFromText('POINT(109.472800 36.624116)'));
insert into geo(geo) values(ST_GeomFromText('POINT(109.487460 36.563614)'));
insert into geo(geo) values(ST_GeomFromText('POINT(109.532016 36.686191)'));
insert into geo(geo) values(ST_GeomFromText('POINT(109.319010 36.987505)'));

3. create a polygon

SET @g3 = ST_GeomFromText('Polygon((108 36.5,108 36.7,109.5 36.7,109.5 36.5,108 36.5))');

4. explain SQL

mysql> explain select st_x(geo),st_y(geo) from geo where mbrcontains(@g3,geo)>0\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: geo
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 8
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select st_x(`map`.`geo`.`geo`) AS `st_x(geo)`,st_y(`map`.`geo`.`geo`) AS `st_y(geo)` from `map`.`geo` where (mbrcontains((@`g3`),`map`.`geo`.`geo`) > 0)
1 row in set (0.00 sec)

Why doesn't this query use key?

Upvotes: 0

Views: 1119

Answers (2)

matt
matt

Reputation: 4734

At first, I wanted to add this as a comment to the accepted answer but then I decided to add another one as the accepted answer is wrong.

InnoDB does support spatial indexes in 5.7. The reason why your query with InnoDB skips the index is due to the optimizer, not lack of support in the storage engine. The optimizer simply decides that in InnoDB's case it's not cost effective to use the index (for whatever reasons). To confirm it add more rows (ideally with randomly spread points) to the table and repeat the query

Upvotes: 0

vmachan
vmachan

Reputation: 1682

Based on this Percona post

"Spatial indexes (RTREE) are only supported for MyISAM tables. One can use the functions for InnoDB tables, but it will not use spatial keys"

Upvotes: 3

Related Questions