Geographic Indexes in InnoDB

0
47

One of the big features of MySQL 8.0 is geography support. MySQL now has a catalog of spatial reference systems (SRSs), of which almost 500 are geographic. Most functions also support geographic computations. What about indexes?

MySQL 8.0 comes with InnoDB spatial indexes for geographic data. Since computations are different for Cartesian and geographic data, these can’t be mixed in the same index. In fact, it doesn’t make sense to index data in more than one SRS in the same index. Because of this, MySQL has been extended to support restrictions on SRIDs in geometry column definitions.

SRID Restrictions

In 5.7 and earlier, the only requirements on an indexed geometry column were that the type should be a geometry type and that the column shouldn’t be nullable. Unfortunately, we were allowed to insert geometries in different SRIDs into the same index. That never made sense, and it gets worse when some geometries are in geographic SRSs.

Therefore, MySQL 8.0 adds a way to restrict geometry columns to only one SRID:

If we try to insert a geometry in a different SRID, we get an error:

If we comply with the SRID restriction, the point is inserted:

With this restriction in place, MySQL makes sure that we won’t mix data in different SRIDs in the same column, and that makes the column indexable.

Another thing it does is lock the SRID. The server won’t allow us to drop the SRS while it is used in an SRID restriction:

Which column is that?

Indexes

We can easily create an index on our geometry column.

Since this column is in SRID 4326, the index will also be in SRID 4326. SRID 4326 is a geographic SRS, so this will be a geographic index. The query optimizer will automatically use this index to optimize execution of queries with spatial relation functions (ST_Contains, ST_Within, etc.), if it finds that that is the cheapest access method. All the spatial relation functions support geographic computations.

One perhaps surprising fact is that the server still allows us to crate indexes on columns that are not restricted to a single column, but with a warning that the index will never be used:

The warnings says it all. This index will never be used. The server allows us to create the index for one reason only: backwards compatibility with mysqldump. We should be able to load a mysqldump from 5.7. If there are spatial indexes like this in the dump, they will be created but never used.

MyISAM

It’s worth noticing that this only applies to InnoDB. If we try to create an SRID restricted column on a geographic SRID in MyISAM, we get an error:

If we try the same with the SRID of a Cartesian SRS, we are allowed to create the table:

The reason is that MyISAM doesn’t support geographic spatial indexes. We’re stopped already when creating the table.

My advice: Use InnoDB for all spatial data!

The Source of subject Geographic Indexes in InnoDB