Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10251

ST_INTERSECTS(tbl1.col, tbl2.col) does not use the spatial index

    Details

      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)
      

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              GeoffMontee Geoff Montee
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated: