[MDEV-28348] InnoDB spatial index is not optimal with ST_Intersects Created: 2022-04-19  Updated: 2023-04-14

Status: Open
Project: MariaDB Server
Component/s: GIS, Storage Engine - InnoDB
Affects Version/s: 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8
Fix Version/s: 10.4, 10.5, 10.6, 10.11

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Marko Mäkelä
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-28347 MyISAM spatial index is not optimal w... Open

 Description    

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (
 
`point` point NOT NULL,
 
  SPATIAL KEY point (point)
 
) ENGINE=InnoDB;
 
 
 
BEGIN;
 
DELIMITER $$
 
SET @i=1000;
 
WHILE @i<=5000
 
DO
 
  INSERT INTO t1 VALUES (POINT(900, 200));
 
  SET @i=@i+1;
 
END WHILE;
 
$$
 
DELIMITER ;
 
COMMIT;
 
 
 
explain SELECT * FROM t1 FORCE INDEX (point) WHERE ST_Intersects(point, GeomFromText('POLYGON((3 0, 3 3, 8 3, 8 0, 3 0))'));


+------+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
 
| id   | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | Extra       |
 
+------+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
 
|    1 | SIMPLE      | t1    | range | point         | point | 34      | NULL | 4001 | Using where |
 
+------+-------------+-------+-------+---------------+-------+---------+------+------+-------------+

The estimation of 4001 rows does not look good. In fact, no rows match the condition.

 Comments   
Comment by Alexander Barkov [ 2022-04-19 ]

Note, in version 10.2 to 10.5, the following trick with a table rebuild helps:

ALTER TABLE t1 ENGINE=MyISAM;
 
ALTER TABLE t1 ENGINE=InnoDB;
 
explain SELECT * FROM t1 FORCE INDEX (point) WHERE ST_Intersects(point, GeomFromText('POLYGON((3 0, 3 3, 8 3, 8 0, 3 0))'));


+------+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
 
| id   | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | Extra       |
 
+------+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
 
|    1 | SIMPLE      | t1    | range | point         | point | 34      | NULL | 4    | Using where |
 
+------+-------------+-------+-------+---------------+-------+---------+------+------+-------------+

Now the output is much better.

Starting from 10.6 the rebuild does not help.

Generated at Thu Feb 08 10:00:01 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.