Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1
-
None
-
10.1.6-1
Description
This script
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a GEOMETRY NOT NULL, SPATIAL KEY(a)) ENGINE=MyISAM;
|
INSERT INTO t1 VALUES (Point(1,2)),(Point(1,3));
|
EXPLAIN SELECT * FROM t1 WHERE MBRINTERSECTS(a,Point(1,2));
|
returns
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
| 1 | SIMPLE | t1 | range | a | a | 34 | NULL | 1 | Using where |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
which means range optimizer is used. So far so good.
Now if I change the order of the arguments:
EXPLAIN SELECT * FROM t1 WHERE MBRINTERSECTS(Point(1,2),a);
|
it does not use the index any more:
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
| 1 | SIMPLE | t1 | ALL | a | NULL | NULL | NULL | 2 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
The same problem is repeatable with the precise function INTERSECTS().
Note, MBRINTERSECTS() and INTERSECTS() are symmetric operation, so the order of the arguments should not matter.