percona-server
percona-server copied to clipboard
Fix candidate for bug 1682306: MbrCovers() function not utilizing spatial indexes.
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