Why is MySQL Database not using my spatial index?

Question

In my setup I want to use a spatial index to search efficiently through a database, in particular to get all points in a radius around a second point. However, I'm kind of confused, why MySQL (version 8.0.29) is refusing to use my index. My query is the following:

SELECT * FROM `StationTable` WHERE
  st_contains( 
      st_makeEnvelope (
          point(53.6, 10),
          point(53.5, 9.9)
      ),
      Location
  )
AND
ST_Distance_Sphere(Location, ST_GeomFromText('POINT(9.95 53.55)')) < 1000;

I expected the table to use my spatial index on Location to find the 4-5 points inside this envelope and then to calculate the distance from my point, but my database (InnoDB) is performing the following: PHPMyAdmin - SQL Explain on my query Why doesn't it use my spatial index? SRID of the data is 0, but I also tested data with SRID = 4326 with the following error:

#3618 - st_makeenvelope(POINT, POINT) has not been implemented for geographic spatial reference systems.

I also tested to query with WHERE ST_X(Location) BETWEEN, but it also didn't used my index. How can I write my query, to use my index?

Mysql is intelligent enough to decide (in the execution plan) if it is needed to use the index or not. That decission may depend on the amount (number of rows) you have in your table. There are times when not using an index is faster than using it. Test it with 1M or 10M rows to see what happens — nacho
– nacho, Commented Dec 5, 2022 at 13:13
And when you use a function in your WHERE clause, the system may not use the index — nacho
– nacho, Commented Dec 5, 2022 at 13:16

andyde · Accepted Answer · 2024-07-14 08:50:34Z

1

Found the answer myself: The column needs to be SRID restricted (see dev.mysql.com for details). So the correct query would be

CREATE TABLE `StationTable` (
    `UUID` VARCHAR(32),
    `Location` POINT NOT NULL SRID 4326,
    PRIMARY KEY(`UUID`),
    SPATIAL INDEX(`Location`)
);

SELECT UUID, Location FROM `StationTable` WHERE
  ST_Distance_Sphere(ST_GeomFromText('POINT(9.95 53.55)', 4326), Location) < 1000;

answered Jul 14, 2024 at 8:50

andyde

212 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Why is MySQL Database not using my spatial index?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related