Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL)
-
None
Description
DROP TABLE IF EXISTS t1; |
CREATE TABLE t1 ( |
point point NOT NULL, |
SPATIAL KEY `point` (`point`) |
) ENGINE=MyISAM;
|
|
|
DELIMITER $$
|
FOR i IN 1000..5000 |
DO
|
INSERT INTO t1 VALUES (POINT(900, 200)); |
END FOR; |
$$
|
DELIMITER ;
|
|
|
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. All points inserted are outside of the polygon.
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 |
|
+------+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
|
Attachments
Issue Links
- relates to
-
-
- Open
-
It's also repeatable with 10.2, but needs a WHILE loop instead of FOR:
) ENGINE=MyISAM;DELIMITER $$WHILE @i <= 5000DO$$DELIMITER ;+------+-------------+-------+-------+---------------+-------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+------+-------------+-------+-------+---------------+-------+---------+------+------+-------------+| 1 | SIMPLE | t1 | range | point | point | 34 | NULL | 4001 | Using where |+------+-------------+-------+-------+---------------+-------+---------+------+------+-------------+