percona-server icon indicating copy to clipboard operation
percona-server copied to clipboard

Fix candidate for bug 1682306: MbrCovers() function not utilizing spatial indexes.

Open isegal opened this issue 8 years ago • 0 comments

This is a fix candidate for https://bugs.launchpad.net/percona-server/+bug/1682306

In 5.7 The behavior of MbrContains() was changed and a new function MbrCovers() was implemented to allow the old behavior of MbrContains() that was in 5.6.

However, when using queries with MbrCovers() spatial indexes are never used.

Test Case:

SET @poly1 = ST_GeomFromText('POLYGON((25 25, 25 35, 35 35, 35 25, 25 25))');

CREATE TABLE t1 (
                   a INT NOT NULL,
                   p POINT NOT NULL,
                   l LINESTRING NOT NULL,
                   g GEOMETRY NOT NULL,
                   PRIMARY KEY(p),
                   SPATIAL KEY idx2 (p),
                   SPATIAL KEY idx3 (l),
                   SPATIAL KEY idx4 (g)
                ) ENGINE=InnoDB;

INSERT INTO t1 VALUES(
                      1, ST_GeomFromText('POINT(10 10)'),
                      ST_GeomFromText('LINESTRING(1 1, 5 5, 10 10)'),
                      ST_GeomFromText('POLYGON((30 30, 40 40, 50 50, 30 50, 30 40, 30 30))'));

INSERT INTO t1 VALUES(
                      2, ST_GeomFromText('POINT(30 30)'),
                      ST_GeomFromText('LINESTRING(2 3, 7 8, 9 10, 15 16)'),
                      ST_GeomFromText('POLYGON((10 30, 30 40, 40 50, 40 30, 30 20, 10 30))'));

EXPLAIN SELECT ST_AsText(p) FROM t1 WHERE MbrCovers(p, @poly1);

--------------------------------
Result:
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows filtered	Extra
1	SIMPLE	t1	NULL	ALL	PRIMARY,idx2	NULL	NULL	NULL	2	100.00	Using where

--------------------------------
Expected Result:
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows filtered	Extra
1	SIMPLE	t1	NULL	range	PRIMARY,idx2	idx2	34	NULL	1	100.00	Using where

isegal avatar Apr 13 '17 00:04 isegal