MySQL 8 - Where MBRContains not using spatial index

Question

I'm using MySQL 8.0.29 on both Ubuntu & Windows 10 and I'm stumped as to why this query is running so slow (~15 seconds) and not utilizing the spatial index. I'm using MBRContains almost exactly as described in the MySQL documentation: 11.4.11 Using Spatial Indexes. The table location_coordinate has 735k rows of POINT type lat/long coordinates searching if contained within a POLYGON (box).

The query:

SELECT long_lat_id, ST_Latitude(long_lat), ST_Longitude(long_lat)
FROM location_coordinate
WHERE MBRContains(ST_GeomFromText(
    'POLYGON((
        40.79607446677 -73.919978196147, 
        40.70923553323 -73.919978196147, 
        40.70923553323 -74.034611803853, 
        40.79607446677 -74.034611803853, 
        40.79607446677 -73.919978196147))', 4326)
    , long_lat);

Explain on the query

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: location_coordinate
   partitions: NULL
         type: ALL
possible_keys: long_lat
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 735118
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

Table Definition and explain

CREATE TABLE location_coordinate (
    long_lat_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
    long_lat POINT NOT NULL SRID 4326,  
    PRIMARY KEY (long_lat_id),
    SPATIAL INDEX (long_lat)
);


mysql> EXPLAIN location_coordinate;
+--------------+--------------------+------+-----+---------+----------------+
| Field        | Type               | Null | Key | Default | Extra          |
+--------------+--------------------+------+-----+---------+----------------+
| long_lat_id  | mediumint unsigned | NO   | PRI | NULL    | auto_increment |
| long_lat     | point              | NO   | MUL | NULL    |                |
+--------------+--------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

Answer 1

Following Rick James' comment, I was going to submit a bug report but it turns out this is an acknowledged bug in MySQL 8.0.29

From https://bugs.mysql.com/bug.php?id=107320

Documented fix as follows in the MySQL 8.0.31 changelog:

Upgrading to MySQL 8.0.29 led to issues with existing spatial
indexes. The root cause of the problem was a change in how
geographic area computations were performed by the included
Boost library, which was upgraded to version 1.77.0 in MySQL
8.0.29. We fix this by ensuring that we accommodate the new
method whenever such computations are performed.

Closed.

A temporary fix is to use the FORCE INDEX () function in the query. This reduces the query time from 15 seconds to .062 seconds.

SELECT long_lat_id, ST_Latitude(long_lat), ST_Longitude(long_lat)
FROM location_coordinate FORCE INDEX(long_lat)
WHERE MBRContains(ST_GeomFromText(
    'POLYGON((
        40.79607446677 -73.919978196147, 
        40.70923553323 -73.919978196147, 
        40.70923553323 -74.034611803853, 
        40.79607446677 -74.034611803853, 
        40.79607446677 -73.919978196147))', 4326)
    , long_lat);

Update October 3, 2022: This bug was fixed in MySQL 8.0.30.