MySQL 8.0でSPATIAL INDEXが使用されない

# はじめに

(追記)解決記事書きました。
https://qiita.com/miyauchi/items/89ae1870c5f611b2558c

MySQLのGEOMETRY型の列に対して検索をかけようとしたところSPATIAL INDEXが使われなかったので検証してみました。

※この記事を全部書き終わったところで下記の記事を見つけたので(多分)解決しました。SRIDしないとINDEX使われないし、警告も出ないそうな。まじかよ。
https://qiita.com/qyen/items/bc4a7be812253c2be9f9
※せっかく書いたから投稿しておきます。SRID指定した記事は気力があったら別記事として書く。



# 環境について

MySQLの8.0.16を使用しました。

```
mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 8.0.16    |
+-----------+
1 row in set (0.00 sec)
```

# テーブルの定義とデータの準備

まずは、GEOMETRY型の列geomを一つだけ定義したtestというテーブルを作成しました。
geom列にはSPATIAL INDEXが張られています。

このgeom列に対して検索をかけ、INDEXが使用されることを期待します。

```
mysql> SHOW CREATE TABLE test;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                           |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `geom` geometry NOT NULL,
  SPATIAL KEY `sp_index` (`geom`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
```

testテーブルに対し、１レコードだけデータをINSERTしました。

```
mysql> SELECT ST_AsText(geom) FROM test;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ST_AsText(geom)                                                                                                                                                                                    |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| POLYGON((43.08256990265745 141.3162472988521,43.056831004971265 141.3162472988521,43.056831004971265 141.35259659542191,43.08256990265745 141.35259659542191,43.08256990265745 141.3162472988521)) |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
```

INSERTしたデータはPOLYGONで、北海道あたりで四角形のポリゴンです。

# INDEXが使われるのかどうか検証

このデータに対してSELECTをかけたときに、INDEXが使われるのでしょうか？

## ST_Intersects関数の場合

上記のポリゴンに重なるどうか判定するSELECT文を実行してみました。重なりの判定にはST_Intersects関数を使用しています。

```
mysql> SELECT ST_AsText(geom) FROM test WHERE ST_Intersects(geom, ST_GeomFromText('POINT(43.06 141.34)', 4326));
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ST_AsText(geom)                                                                                                                                                                                    |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| POLYGON((43.08256990265745 141.3162472988521,43.056831004971265 141.3162472988521,43.056831004971265 141.35259659542191,43.08256990265745 141.35259659542191,43.08256990265745 141.3162472988521)) |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
```

上記SQLでSPATIAL INDEXが使われたかどうか確認するためにEXPLAIN文を使用します。

```
mysql> EXPLAIN SELECT ST_AsText(geom) FROM test WHERE ST_Intersects(geom, ST_GeomFromText('POINT(43.06 141.34)', 4326));
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
```

残念ながらpossible_keysがNULLとなっており、INDEXが使用されていないことがわかりました。

## ST_Contains関数の場合

では、ST_Intersects関数と似たような関数であるST_Containsではどうでしょうか？

```
mysql> EXPLAIN SELECT ST_AsText(geom) FROM test WHERE ST_Contains(geom, ST_GeomFromText('POINT(43.06 141.34)', 4326));
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
```

こちらもはやりINDEXは使用されませんでした。

## 等価演算子の場合

もしかして、空間関数を使った場合にSPATIAL INDEXが使われないのでは？と思い単純な等価演算子(=)で検証してみました。

```
mysql>  EXPLAIN SELECT ST_AsText(geom) FROM test WHERE geom=ST_GeomFromText('POLYGON((43.08256990265745 141.3162472988521,43.056831004971265 141.3162472988521,43.056831004971265 141.35259659542191,43.08256990265745 141.35259659542191,43.08256990265745 141.3162472988521))', 4326);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
```

あれ？やっぱりSPATIAL INDEXは使用されません。

こうなってくるとPOLYGONに対してのINDEXが使用されないのでは？と思えます。

## POINTデータの場合

ではPOINTデータでならばSPATIAL INDEXは使用されるのでしょうか？
POINTデータをINSERTしてみます。

```
mysql> INSERT INTO test VALUES (ST_GeomFromText('POINT(43.06 141.34)', 4326));
Query OK, 1 row affected (0.01 sec)

mysql> SELECT ST_AsText(geom) FROM test;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ST_AsText(geom)                                                                                                                                                                                    |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| POLYGON((43.08256990265745 141.3162472988521,43.056831004971265 141.3162472988521,43.056831004971265 141.35259659542191,43.08256990265745 141.35259659542191,43.08256990265745 141.3162472988521)) |
| POINT(43.06 141.34)                                                                                                                                                                                |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
```

POLYGONの時と同じように等価記号による条件で検索してみます。

```
mysql> EXPLAIN SELECT ST_AsText(geom) FROM test WHERE geom=ST_GeomFromText('POINT(43.06 141.34)', 4326);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
```

やっぱりSPATIAL INDEXは使われませんでした。。。

# 最後に

こうなってくると一体どういうケースでSPATIAL INDEXが使われるのか？のほうが気になります。