MySQL Blog Archive
For the latest blogs go to blogs.oracle.com/mysql
Upgrading Spatial Indexes to MySQL 8.0

MySQL has had spatial indexes for many years, but they have all been Cartesian (X and Y coordinates) indexes. MySQL 8.0 adds support for geographic (latitude-longitude) indexes. In an earlier blog post, I described how the feature works. In this post, we’ll go into the details of how to upgrade from 5.7 to 8.0 if you have spatial indexes. If you haven’t done so already, now would be a good idea to go back and refresh your understanding of that post.

The Problem

MySQL 8.0 needs to know if the index is Cartesian or geographic. That depends on the SRID of the data in the column. Unfortunately, 5.7 and earlier doesn’t restrict an index to a single SRID, so the index may contain both Cartesian and geographic data. That is fine as long as MySQL ignores the SRID (as 5.7 and earlier does), but it won’t work in 8.0. Therefore, 5.7 indexes are not used by the 8.0 optimizer.

So if we have a spatial index and upgrade to 8.0, we have to

  1. Make sure there’s only geometries in one SRID in the indexed column
  2. Tell MySQL which SRID to use for the index
  3. Recreate the index

The first point is something we can do before upgrading. The two last steps have to be done after upgrading.

Use Only One SRID

This is the only step that can be done before upgrading, and probably one we should do anyway, independent of the upgrade. It has never and will never make sense to have geometries in different SRIDs in the same index, and that is mostly true for columns as well. Most columns should contain geometries in only one SRID.

First we have to find out if there are any indexes over spatial columns:

So, in this example, there are two tables, demo and test, that each have one indexed column with geometry data. Both columns are named position, which is also the name of the indexes.

For this step, we’re only interested in those columns where there are multiple SRIDs used, so we have to go through this list and check:

While the test table is OK (it has only one SRID used in the position column), the demo table contains geometries in 2 different SRIDs.

We can also look at which SRIDs are used:

It’s easy to find this information. The real problem is what to do with it. There isn’t one solution that fits all cases. That is the reason why MySQL can’t do this automatically on upgrade, and why we’re left to do it ourselves.

In this case, we should probably pick either SRID 0 or SRID 4326. Maybe SRID 4326 is the best choice for this application? But remember this: Older MySQL versions only understand SRID 0. That is the safe choice if we want to preserve behavior from older versions after upgrading to MySQL 8.0.

For the sake of our example, let’s follow 5.7 best practice and use only SRID 0. We therefore need to change the SRID of all the geometries that have different SRIDs. We’ll do that by exporting the geometry to a data format that doesn’t preserve SRIDs and then re-import it in SRID 0:

And that’s it! We’ve fixed our problematic column. Of course, real life situations are different, and there may be harder choices deciding which SRID should be used. Maybe the coordinates are wrong, too. That’s why we have to do it ourselves. MySQL doesn’t know enough to make the decision.

Changes in 8.0

As mentioned above, using only one SRID in each column is a good thing regardless of upgrade plans. There’s no reason to wait — do it before you upgrade! But if we find ourselves in the situation of not having fixed this before upgrading to MySQL 8.0, there are a few details to adjust.

First, some of the INFORMATION_SCHEMA views have different names in 8.0, so the query to discover spatial indexes is different:

Second, we can update the SRID in an easier way, using the second parameter to the ST_SRID function:

Restrict the Column to One SRID

Once a column contains only geometries in one SRID, we can add an SRID restriction to that column. That puts the responsibility of making sure we don’t mix SRIDs in the future on MySQL.

Above, we only modified the demo table. The test table only used one SRID to begin with, so we didn’t have to touch it. In this and the next step, we modify all indexed columns, regardless of how many SRIDs were used in 5.7. This has to be done after the upgrade to 8.0.

The restriction is easy to add. Simply put “SRID xxxx” in the column definition. In our case, the old definition was “POINT NOT NULL“. The new one is “POINT NOT NULL SRID 0“. However, if we try to restrict the column without dropping the old index first, we’ll get an error:

The error message really says it all. So, let’s remove the index and try again:

The column now contains only data in SRID 0, and the new constraint will make sure that attempts to insert geometries in other SRIDs are rejected.

In our case, we should repeat this for table test, too. Even if there was an index in 5.7 and the column (and therefore also index) only contained geometries in one SRID, MySQL has no guarantee that we won’t insert a geometry in a different SRID in the future. Therefore, MySQL 8.0 will never use spatial indexes on columns that are not restricted to a single SRID. For upgrade reasons, we’re allowed to create and have such indexes, but they are marked as invalid and never considered by the optimizer.

Recreate the Index

When the column restriction is in place, we can recreate the index:

We have to repeat that for the test table, too.

Notice that there are no warnings. If we had tried to create a spatial index on a column that didn’t have an SRID restriction, there would be a warning telling us that this index would never be used.

For more information about spatial indexes, SRID restrictions, warnings and errors, please see my earlier post on Geographic Indexes in InnoDB.

Thank you for using MySQL !