MySQL 8 not using Spatial Index

indexMySQLmysql-8.0spatial

I have an issue where whatever I do I cannot get MySQL to use a spatial index.

A query like the following then takes 5 seconds!

Any way to force this to use the index at all?

WITH ranked_reports AS (
    SELECT
        station_id,
        raw_text,
        observation_time,
        RANK() OVER ( PARTITION BY station_id ORDER BY observation_time DESC ) order_rank 
    FROM
        METAR 
    WHERE
        ST_Distance_Sphere (
            geo_point,
        ST_GeomFromText ( 'POINT(51.85 -0.79)', 4326 )) <= 100 * 1609.34 
    ) SELECT
    * 
FROM
    ranked_reports 
WHERE order_rank =1

Explain of query

+----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-----------------------------+
| id | select_type | table      | partitions | type | possible_keys | key         | key_len | ref   | rows   | filtered | Extra                       |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-----------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ref  | <auto_key0>   | <auto_key0> | 8       | const |     10 |   100.00 | NULL                        |
|  2 | DERIVED     | METAR      | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  | 585814 |   100.00 | Using where; Using filesort |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-----------------------------+

Table Structure

CREATE TABLE `METAR` (
  `station_id` varchar(5) NOT NULL,
  `station_iata` varchar(5) DEFAULT NULL,
  `observation_time` datetime NOT NULL,
  `latitude` float(255,2) DEFAULT NULL,
  `longitude` float(255,2) DEFAULT NULL,
  `raw_text` varchar(255) DEFAULT NULL,
  `temp_c` float(255,1) DEFAULT NULL,
  `dewpoint_c` float(255,1) DEFAULT NULL,
  `wind_dir_degrees` int(255) DEFAULT NULL,
  `wind_speed_kt` int(11) DEFAULT NULL,
  `wind_gust_kt` int(11) DEFAULT NULL,
  `visibility_statute_mi` float(255,2) DEFAULT NULL,
  `altim_in_hg` float(255,2) DEFAULT NULL,
  `altim_in_hPa` float(255,0) DEFAULT NULL,
  `sea_level_pressure_mb` float(255,0) DEFAULT NULL,
  `wx_string` varchar(255) DEFAULT NULL,
  `sky_condition` mediumtext,
  `flight_category` varchar(255) DEFAULT NULL,
  `three_hr_pressure_tendency_mb` float DEFAULT NULL,
  `maxT_c` float(255,0) DEFAULT NULL,
  `minT_c` float(255,0) DEFAULT NULL,
  `maxT24hr_c` float DEFAULT NULL,
  `minT24hr_c` float(255,0) DEFAULT NULL,
  `precip_in` float(255,0) DEFAULT NULL,
  `pcp3hr_in` float(255,0) DEFAULT NULL,
  `pcp6hr_in` float(255,0) DEFAULT NULL,
  `pcp24hr_in` float(255,0) DEFAULT NULL,
  `snow_in` float(255,0) DEFAULT NULL,
  `vert_vis_ft` int(255) DEFAULT NULL,
  `time_added` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `geo_point` point NOT NULL /*!80003 SRID 4326 */,
  PRIMARY KEY (`station_id`,`observation_time`),
  KEY `IDX_Station` (`station_id`) USING BTREE,
  KEY `IDX_Deletion` (`observation_time`) USING BTREE,
  KEY `IDX_IATA` (`station_iata`,`observation_time`) USING BTREE,
  SPATIAL KEY `IDX_Spatial` (`geo_point`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Best Answer

Your query has

WHERE
    ST_Distance_Sphere (
        geo_point,
    ST_GeomFromText ( 'POINT(51.85 -0.79)', 4326 )) <= 100 * 1609.34 
)

In MySQL you must use the MBR functions to use the index.

So don't use ST_Distance_Sphere instead,

MBRContains( ST_Buffer( ST_SRID(POINT(51.85 -0.79), 4326), 100 * 1609.34), geog )