はじめに
(追記)解決記事書きました。
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が使われるのか？のほうが気になります。