3

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;
7
  • 1
    I'm afraid that I don't know anything about MySQL's spatial functionality so I can't answer the question, but could you please take a look at this link to see why the posting of images of text to StackExchange is not a good idea?
    – Vérace
    Feb 27, 2020 at 18:30
  • @Vérace good point, done Feb 27, 2020 at 18:33
  • what if you use geo_point point NOT NULL SRID 4326 when creating table? Feb 27, 2020 at 19:02
  • It was created that way already. Also verified using SELECT DISTINCT ST_SRID(geo_point)from METAR; Feb 27, 2020 at 19:26
  • how many rows does the table contain and how many of them meet the conditions? Feb 27, 2020 at 21:13

3 Answers 3

2

You need to add SRID to column definition:

The SRID attribute makes a spatial column SRID-restricted, which has these implications:

The column can contain only values with the given SRID. Attempts to insert values with a different SRID produce an error.

The optimizer can use SPATIAL indexes on the column. See Section 8.3.3, “SPATIAL Index Optimization”.

Spatial columns with no SRID attribute are not SRID-restricted and accept values with any SRID. However, the optimizer cannot use SPATIAL indexes on them until the column definition is modified to include an SRID attribute, which may require that the column contents first be modified so that all values have the same SRID.

1
  • The column geo_point does have a SRID set. 4326 in fact. /*!80003 SRID 4326 */ Feb 27, 2020 at 19:22
2

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 )
1
  • st_buffer(POINT, ...) has not been implemented for geographic spatial reference systems. MySQL does not support ST_BUFFER on SRID other than 0. Future versions will probably change that though. Aug 29, 2021 at 15:13
0

When comparing return values from spatial functions in where clause, MySQL just ignores the index. This bug is reported in https://bugs.mysql.com/bug.php?id=76384, and confirmed in my local env with 8.0.27.

Your goal in the question can be translated into another one: Search for points which are within RADIUS unit distance from the given point (LNG, LAT). This can be done by ST_Contains/ST_Within.

Since ST_Contains/ST_Within returns 1 or 0, it’s ok to ignore = 1 in the where clause which will only select values with 1. The where clause should be something like:

WHERE ST_Contains(
  ST_Buffer(ST_SRID(Point(LNG, LAT), 4326), RADIUS),
  geo_point
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.