Description:
Doing a MBRcontains() query on a indexed GEOMETRY column uses the SPATIAL index just fine, two MBRcontains() conditions combined with OR lead to a full table scan though.
On a test table with a copy of the GEOMETRY data in a 2nd column with an index of its own a query which does a MBRcontains(col1, ...) OR MBRcontains(col2, ...) index_merge kicks in just fine, but this only works when merging from *different* indexes.
An OR condition on a single SPATIAL index needs to be rewritten as a UNION DISTINCT to perform well instead of doing a full table scan.
How to repeat:
On a large test table
CREATE TABLE `t2` (
`id` int(11) NOT NULL auto_increment,
`geo` geometry NOT NULL,
`geo2` geometry NOT NULL,
PRIMARY KEY (`id`),
SPATIAL KEY `geo` (`geo`),
SPATIAL KEY `geo2` (`geo2`)
) ENGINE=MyISAM;
with almost 5 million random rectangle POLYGONs in `geo`
and `geo2` being a copy of `geo` queries using a single
MBRcontains() expression use a SPATIAL index just fine:
mysql> explain select count(*) from t2
where mbrcontains(geo, geomfromtext('point(1000 1000)'))\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: range
possible_keys: geo
key: geo
key_len: 34
ref: NULL
rows: 1
Extra: Using where
1 row in set (0.00 sec)
mysql> explain select count(*) from t2
where mbrcontains(geo, geomfromtext('point(2000 2000)'))\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: range
possible_keys: geo
key: geo
key_len: 34
ref: NULL
rows: 1
Extra: Using where
1 row in set (0.00 sec)
Combining both conditions in a single query with OR leads to a full table scan though:
mysql> explain select count(*) from t2
where mbrcontains(geo, geomfromtext('point(1000 1000)'))
or mbrcontains(geo, geomfromtext('point(2000 2000)'))\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: ALL
possible_keys: geo
key: NULL
key_len: NULL
ref: NULL
rows: 4753401
Extra: Using where
1 row in set (0.00 sec)
When performing the 2nd MBRcontains() on the copy column `geo2` instead
index_merge saves the day:
mysql> explain select count(*) from t2
where mbrcontains(geo, geomfromtext('point(1000 1000)'))
or mbrcontains(geo2, geomfromtext('point(2000 2000)'))\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: index_merge
possible_keys: geo,geo2
key: geo,geo2
key_len: 34,34
ref: NULL
rows: 2
Extra: Using sort_union(geo,geo2); Using where
1 row in set (0.01 sec)
Another alternative is using UNION DISTINCT:
mysql> explain select geo from t2
where mbrcontains(geo, geomfromtext('point(1000 1000)'))
union distinct
select geo from t2
where mbrcontains(geo, geomfromtext('point(2000 2000)'))\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: t2
type: range
possible_keys: geo
key: geo
key_len: 34
ref: NULL
rows: 1
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: UNION
table: t2
type: range
possible_keys: geo
key: geo
key_len: 34
ref: NULL
rows: 1
Extra: Using where
*************************** 3. row ***************************
id: NULL
select_type: UNION RESULT
table: <union1,2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra:
3 rows in set (0.03 sec)
© 2024, Oracle Corporation and/or its affiliates
Description: Doing a MBRcontains() query on a indexed GEOMETRY column uses the SPATIAL index just fine, two MBRcontains() conditions combined with OR lead to a full table scan though. On a test table with a copy of the GEOMETRY data in a 2nd column with an index of its own a query which does a MBRcontains(col1, ...) OR MBRcontains(col2, ...) index_merge kicks in just fine, but this only works when merging from *different* indexes. An OR condition on a single SPATIAL index needs to be rewritten as a UNION DISTINCT to perform well instead of doing a full table scan. How to repeat: On a large test table CREATE TABLE `t2` ( `id` int(11) NOT NULL auto_increment, `geo` geometry NOT NULL, `geo2` geometry NOT NULL, PRIMARY KEY (`id`), SPATIAL KEY `geo` (`geo`), SPATIAL KEY `geo2` (`geo2`) ) ENGINE=MyISAM; with almost 5 million random rectangle POLYGONs in `geo` and `geo2` being a copy of `geo` queries using a single MBRcontains() expression use a SPATIAL index just fine: mysql> explain select count(*) from t2 where mbrcontains(geo, geomfromtext('point(1000 1000)'))\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t2 type: range possible_keys: geo key: geo key_len: 34 ref: NULL rows: 1 Extra: Using where 1 row in set (0.00 sec) mysql> explain select count(*) from t2 where mbrcontains(geo, geomfromtext('point(2000 2000)'))\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t2 type: range possible_keys: geo key: geo key_len: 34 ref: NULL rows: 1 Extra: Using where 1 row in set (0.00 sec) Combining both conditions in a single query with OR leads to a full table scan though: mysql> explain select count(*) from t2 where mbrcontains(geo, geomfromtext('point(1000 1000)')) or mbrcontains(geo, geomfromtext('point(2000 2000)'))\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t2 type: ALL possible_keys: geo key: NULL key_len: NULL ref: NULL rows: 4753401 Extra: Using where 1 row in set (0.00 sec) When performing the 2nd MBRcontains() on the copy column `geo2` instead index_merge saves the day: mysql> explain select count(*) from t2 where mbrcontains(geo, geomfromtext('point(1000 1000)')) or mbrcontains(geo2, geomfromtext('point(2000 2000)'))\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t2 type: index_merge possible_keys: geo,geo2 key: geo,geo2 key_len: 34,34 ref: NULL rows: 2 Extra: Using sort_union(geo,geo2); Using where 1 row in set (0.01 sec) Another alternative is using UNION DISTINCT: mysql> explain select geo from t2 where mbrcontains(geo, geomfromtext('point(1000 1000)')) union distinct select geo from t2 where mbrcontains(geo, geomfromtext('point(2000 2000)'))\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: t2 type: range possible_keys: geo key: geo key_len: 34 ref: NULL rows: 1 Extra: Using where *************************** 2. row *************************** id: 2 select_type: UNION table: t2 type: range possible_keys: geo key: geo key_len: 34 ref: NULL rows: 1 Extra: Using where *************************** 3. row *************************** id: NULL select_type: UNION RESULT table: <union1,2> type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: 3 rows in set (0.03 sec)