Mysql Spatial index unused

Question

I'm looking for being able to find rows matching approximatively (let's say within 20 meters) given from and to points. It works but it doesn't use index.

I'm trying to take advantage of Spatial index on this table but it doesn't seems to be used (Explain command give me "possible_keys" = null).

With the following:

• mysql 5.7.17

• table:

  CREATE TABLE `geoDirections` (
     `id` int(11) NOT NULL,
     `from` point NOT NULL,
     `to` point NOT NULL,
  ) ENGINE=InnoDB;
  ALTER TABLE `geoDirections`
      ADD PRIMARY KEY (`id`),
      ADD SPATIAL KEY `from` (`from`),
      ADD SPATIAL KEY `to` (`to`);
  

• arround 1000000 rows inserted

What I tried:

• using ST_Contains

  EXPLAIN SELECT 
     g.`from`
  FROM 
      geoDirections g
  WHERE
      ST_Contains(ST_Buffer(
            ST_GeomFromText('POINT(-2.00751 48.6547)', 4326), (0.00001*20)), g.`from`) = 1 
      AND
          ST_Contains(ST_Buffer(
            ST_GeomFromText('POINT(-2.05757 48.6338)', 4326), (0.00001*20)), g.`to`) = 1 
  

gives me

    | id  |  select_type  |  table  |  partitions  |  type  |  possible_keys  |  key  |  key_len  |  ref  |  rows  |  filtered  |  Extra       |
    | 1   |  SIMPLE       |  g      |     null     |  ALL   |     null        |  null |    null   |  null | 994867 |  100.00    |  Using where |

• using calculated distance

  EXPLAIN SELECT
      X(g.`from`),Y(g.`from`), g.*, (
        6373 * acos (
        cos ( radians( -2.00751 ) )
        * cos( radians( X(g.`from`) ) )
        * cos( radians( Y(g.`from`) ) - radians( 48.6547  ) )
        + sin ( radians( -2.00751 ) )
        * sin( radians( X(g.`from`) ) )
      )
  ) AS distanceFrom
  FROM geoDirections g
  HAVING distanceFrom < 0.02
  

gives me

    | id  |  select_type  |  table  |  partitions  |  type  |  possible_keys  |  key  |  key_len  |  ref  |  rows  |  filtered  |  Extra  |
    | 1   |  SIMPLE       |  g      |     null     |  ALL   |     null        |  null |    null   |  null | 994867 |  100.00    |  null   |

• even something as simple as

  EXPLAIN SELECT
      X(g.`from`),Y(g.`from`), g.*
  FROM geoDirections g
  WHERE X(g.`from`) = -2.00751
  

gives me

    | id  |  select_type  |  table  |  partitions  |  type  |  possible_keys  |  key  |  key_len  |  ref  |  rows  |  filtered  |  Extra       |
    | 1   |  SIMPLE       |  g      |     null     |  ALL   |     null        |  null |    null   |  null | 994867 |  100.00    |  Using where |

• tried converting InnoDb to MyIsam (as older InnoDb version wheren't supposed to support spatial indexes)

What am I missing ?

Answer 1

Bug #76384 Spatial index not used when checking return values explicitly.

Try:

mysql> DROP TABLE IF EXISTS `geoDirections`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `geoDirections` (
    ->    `id` INT(11) NOT NULL,
    ->    `from` POINT NOT NULL,
    ->    `to` POINT NOT NULL
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE `geoDirections`
    ->     ADD PRIMARY KEY (`id`),
    ->     ADD SPATIAL INDEX (`from`),
    ->     ADD SPATIAL INDEX (`to`);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT `g`.`from`
    -> FROM `geoDirections` `g`
    -> WHERE
    ->       ST_Contains(ST_Buffer(
    ->           ST_GeomFromText('POINT(-2.00751 48.6547)', 4326), (0.00001 * 20)), `g`.`from`) 
    ->       AND
    ->       ST_Contains(ST_Buffer(
    ->           ST_GeomFromText('POINT(-2.05757 48.6338)', 4326), (0.00001 * 20)), `g`.`to`)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: g
   partitions: NULL
         type: ALL
possible_keys: from,to
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)