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)
|