MySQL R-Tree의 활용 (미완성)

MySQL에서 위경도를 기준으로 몇 km 내로 데이터를 뽑아와야하는 경우가 생겼다.

CREATE TABLE geomk (g GEOMETRY NOT NULL SRID 4326, SPATIAL INDEX(g)); 

SELECT ST_PointFromText('POINT(37.5911968710618 126.966182351829)', 4326);


INSERT INTO geom VALUES (ST_PointFromText('POINT(37.5911968710618 126.966182351829)', 4326));

CREATE TABLE mytable (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50),
  location POINT
);


INSERT INTO mytable (name, location) VALUES ('Seoul1', POINT(127.0155574, 37.548572));
INSERT INTO mytable (name, location) VALUES ('Seoul2', POINT(127.012408, 37.548002));
INSERT INTO mytable (name, location) VALUES ('Seoul3', POINT(127.012503, 37.549821));
INSERT INTO mytable (name, location) VALUES ('Seoul4', POINT(130.03011111111111, 37.5561881765677));
INSERT INTO mytable (name, location) VALUES ('Seoul5', POINT(131.03011111111111, 37.5561881765677));
INSERT INTO mytable (name, location) VALUES ('Seoul6', POINT(130.03011111111111, 39.1561881765677));


SELECT * FROM mytable
WHERE ST_Distance_Sphere(location, POINT(127.0155574, 37.548572)) <= 400;

R_TREE INDEX를 걸은 경우

INDEX를 절대로 타지 않는다

참조 :
Real MySQL 8.0
https://devocean.sk.com/blog/techBoardDetail.do?ID=163268
https://jwkim96.tistory.com/298
https://dev.mysql.com/doc/refman/8.0/en/spatial-function-reference.html (공식문서)