1

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?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
andyde
  • 11
  • 1
  • 1
    show us the create table – nbk Dec 05 '22 at 12:11
  • 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 Dec 05 '22 at 13:13
  • And when you use a function in your WHERE clause, the system may not use the index – nacho Dec 05 '22 at 13:16
  • See if swapping the order of the AND clauses helps. – Rick James Dec 06 '22 at 19:20

0 Answers0