Details
-
Task
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
None
Description
Let's say that we have the following tables:
One table that keeps track of specific geolocation points, which is an InnoDB table:
CREATE TABLE `point_table` (
|
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
|
`Latitude` varchar(100) DEFAULT NULL,
|
`Longitude` varchar(100) DEFAULT NULL,
|
`geo_location` POINT,
|
PRIMARY KEY (`id`),
|
KEY `Latitude` (`Latitude`),
|
KEY `Longitude` (`Longitude`)
|
) ENGINE=InnoDB;
|
And one table that keeps track of predefined areas:
CREATE TABLE `geometry_table` (
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
`SHAPE` geometry NOT NULL,
|
UNIQUE KEY `id` (`id`),
|
SPATIAL KEY `SHAPE` (`SHAPE`)
|
) ENGINE=MyISAM;
|
Some users want to find out which area each point is in, so they ran queries like this:
SELECT pt.id,
|
( SELECT gt.id
|
FROM geometry_table gt
|
WHERE ST_INTERSECTS( pt.geo_location, gt.shape )
|
LIMIT 1
|
)
|
FROM point_table pt;
|
This query does not use the spatial index:
+------+--------------------+-------+------+---------------+------+---------+------+--------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+-------+------+---------------+------+---------+------+--------+-------------+
|
| 1 | PRIMARY | pt | ALL | NULL | NULL | NULL | NULL | 317301 | |
|
| 2 | DEPENDENT SUBQUERY | gt | ALL | SHAPE | NULL | NULL | NULL | 35 | Using where |
|
+------+--------------------+-------+------+---------------+------+---------+------+--------+-------------+
|
It will not use the index even if FORCE INDEX is used.
The users also tried something like this:
SELECT pt.id, gt.id
|
FROM point_table pt
|
JOIN geometry_table gt
|
ON ST_INTERSECTS( pt.geo_location, gt.shape );
|
That does not use the spatial index either:
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------------------------------------------+
|
| 1 | SIMPLE | gt | ALL | SHAPE | NULL | NULL | NULL | 35 | |
|
| 1 | SIMPLE | pt | ALL | NULL | NULL | NULL | NULL | 317301 | Using where; Using join buffer (flat, BNL join) |
|
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------------------------------------------+
|
2 rows in set (0.00 sec)
|
The spatial index is used when one uses a constant:
MariaDB [test]> explain SELECT gt.id FROM geometry_table gt WHERE ST_INTERSECTS(shape, ST_GEOMFROMTEXT('POINT(10 10)'));
+------+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
| 1 | SIMPLE | gt | range | SHAPE | SHAPE | 34 | NULL | 1 | Using where |
+------+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
As far as I understand, this is a known limitation of the GIS optimizer. R-Tree index only supports "range-like" scans, that is, it only works when a GIS predicate compares an indexed column with a constant.
It's similar with non-GIS ranges. If we consider the "<" comparison, "t.key < const" is handled efficiently, while "t1.key < t2.col" is not.
One could argue that "t1.key < t2.col" avoids running cross-joins by using "Range checked for each record" plans. We don't see "range checked for each record" for GIS queries, for some reason. I'd like to check this with holyfoot.