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
In MySQL you must use the MBR functions to use the index.
So don't use
ST_Distance_Sphere
instead,