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 ?

Hmmm... I wonder if my code will beat it after the fix.

Rick James
– Rick James

2017-04-13 23:25:47 +00:00
Commented Apr 13, 2017 at 23:25 — Rick James
– Rick James, Commented Apr 13, 2017 at 23:25

wchiquito · Accepted Answer · 2017-04-13 13:06:33Z

2

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)

answered Apr 13, 2017 at 13:06

wchiquito

16.6k2 gold badges37 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Axi Over a year ago

I drived me crazy... thanks for spotting that bug. Same bug on MyISAM

Rick James Over a year ago

@Axi -- how fast does it run now?

Axi Over a year ago

@RickJames I didn't check but it's now using indexes

Uday Hiwarale Over a year ago

ST_Buffer only work for cartesian flat co-ordinates (dev.mysql.com/doc/refman/5.7/en/…). How this is working for you?

Collectives™ on Stack Overflow

Mysql Spatial index unused

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related