MySQLでのSpatialIndexの効かし方

MySQL、InnoDBで任意の位置座標から最も近い何件かみたいな情報を取得したい時のメモ

テーブルはこんな感じ

mysql> desc shop;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| name    | varchar(100) | NO   |     | NULL    |       |
| address | varchar(100) | NO   |     | NULL    |       |
| lnglat  | geometry     | NO   | MUL | NULL    |       |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.04 sec)

mysql> show create table shop;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                  |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| shop  | CREATE TABLE `shop` (
  `name` varchar(100) NOT NULL,
  `address` varchar(100) NOT NULL,
  `lnglat` geometry NOT NULL,
  SPATIAL KEY `lnglat` (`lnglat`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

で普通に任意の位置から最も近い10件を取得したいとするとこんな感じになります

mysql> explain select * from shop ORDER BY ST_Distance(lnglat,ST_GeomFromText('POINT(135.9876 35.9876)')) limit 10\G 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: shop
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5459
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.00 sec)

SpatialIndex貼っているのに効かない感じです
どうやらST_Distanceでは効かない模様

それで悩んだあげくこうしたら効きました

mysql> explain select * from shop where ST_Within(lnglat,ST_Buffer(POINT(139.663987,35.676577), 0.01)) ORDER BY ST_Distance(lnglat,ST_GeomFromText('POINT(135.9876 35.9876)')) limit 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: shop
   partitions: NULL
         type: range
possible_keys: lnglat
          key: lnglat
      key_len: 34
          ref: NULL
         rows: 25
     filtered: 100.00
        Extra: Using where; Using filesort
1 row in set, 1 warning (0.00 sec)

キモは where ST_Within(lnglat,ST_Buffer(POINT(139.663987,35.676577), 0.01)) で
lnglat が ST_Buffer(POINT(139.663987,35.676577), 0.01) が示すポリゴン内にあるかを ST_Within で調べています
ST_Buffer で指定座標から半径0.01度のポリゴンを作成します
それ何ｍやねんという話がありますが、詳しくは地球の円周から導いてもらうとして
すごくざっくり1度あたり10000m(正確には111000m強)です、つまり1mあたり0.0001度です
なので0.01度だとすごくざっくり半径100mの円のポリゴンができあがります

ググるとCONCATして近傍を探るSQLを組み立てるような記事が多いのですが、そんなものは
SpatialIndex効かないので、今のところこのやり方しかSpatialIndex効かなさそうです

参考情報： MySQL (InnoDB) で空間検索 (st_within)