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

Question

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?

Your `EXPLAIN` returned a warning. What was it? (Run `SHOW WARNINGS` after the query to display the warning.) — , Jan 20 '16 at 03:44
`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)` fro m `map`.`geo` where (mbrcontains((@`g3`),`map`.`geo`.`geo`) > 0) 1 row in set (0.00 sec)` — user2400825, Jan 20 '16 at 07:39
Please include the warnings in your question, not in a comment. — , Jan 20 '16 at 07:41

score 3 · Accepted Answer · answered Jan 20 '16 at 03:26

3

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"

answered Jan 20 '16 at 03:26

vmachan

1,672
1
10
10

"For MyISAM and (as of MySQL 5.7.5) InnoDB tables, MySQL can create spatial indexes using syntax similar to that for creating regular indexes, but using the SPATIAL keyword."----https://dev.mysql.com/doc/refman/5.7/en/creating-spatial-indexes.html – user2400825 Jan 20 '16 at 07:30
Both me and MySQL made mistakes. My mistake is that the function "mbrcontains" conld not use with ">". MySQL's mistake is that 5.7.10 could not support the spatial index. When I change table's storage engine into MyISAM, the query used index.So it must be a MySQL's bug. – user2400825 Jan 21 '16 at 08:43

score 0 · Answer 2 · answered Jun 03 '19 at 08:59

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

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

2 Answers2