1

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;
  • 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 at 18:30
  • @Vérace good point, done – Lee Armstrong Feb 27 at 18:33
  • what if you use geo_point point NOT NULL SRID 4326 when creating table? – Nikita Feb 27 at 19:02
  • It was created that way already. Also verified using SELECT DISTINCT ST_SRID(geo_point)from METAR; – Lee Armstrong Feb 27 at 19:26
  • how many rows does the table contain and how many of them meet the conditions? – Nikita Feb 27 at 21:13
0

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.

| improve this answer | |
  • The column geo_point does have a SRID set. 4326 in fact. /*!80003 SRID 4326 */ – Lee Armstrong Feb 27 at 19:22
0

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 )
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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