MySQLでのSpatialIndexの効かし方 ref: http://qiita.com/akehoyayoi@github/items/1cc7d5bf5b8a922aa5d9

file0.txt

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)

file1.txt

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)

file2.txt

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)