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
- Make sure there’s only geometries in one SRID in the indexed column
- Tell MySQL which SRID to use for the index
- 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:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> SELECT t.NAME AS 'schema/table', f.NAME AS 'column', i.NAME AS 'index' -> FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES AS i -> JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t -> ON i.TABLE_ID = t.TABLE_ID -> JOIN INFORMATION_SCHEMA.INNODB_SYS_FIELDS AS f -> ON i.INDEX_ID = f.INDEX_ID -> WHERE i.TYPE = 64; +--------------+----------+----------+ | schema/table | column | index | +--------------+----------+----------+ | test/test | position | position | | test/demo | position | position | +--------------+----------+----------+ 2 rows in set (0,00 sec) |
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:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql> SELECT COUNT(DISTINCT ST_SRID(position)) AS num_srids FROM demo; +-----------+ | num_srids | +-----------+ | 2 | +-----------+ 1 row in set (0,00 sec) mysql> SELECT COUNT(DISTINCT ST_SRID(position)) AS num_srids FROM test; +-----------+ | num_srids | +-----------+ | 1 | +-----------+ 1 row in set (0,00 sec) |
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:
|
1
2
3
4
5
6
7
8
|
mysql> SELECT DISTINCT ST_SRID(position) AS srid FROM demo; +------+ | srid | +------+ | 0 | | 4326 | +------+ 2 rows in set (0,00 sec) |
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:
|
1
2
3
4
5
|
mysql> UPDATE demo -> SET position = ST_GeomFromWKB(ST_AsBinary(position), 0) -> WHERE ST_SRID(position) != 0; Query OK, 2 rows affected (0,00 sec) Rows matched: 2 Changed: 2 Warnings: 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:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> SELECT t.NAME AS 'schema/table', f.NAME AS 'column', i.NAME AS 'index' -> FROM INFORMATION_SCHEMA.INNODB_INDEXES AS i -> JOIN INFORMATION_SCHEMA.INNODB_TABLES AS t -> ON i.TABLE_ID = t.TABLE_ID -> JOIN INFORMATION_SCHEMA.INNODB_FIELDS AS f -> ON i.INDEX_ID = f.INDEX_ID -> WHERE i.TYPE = 64; +--------------+----------+----------+ | schema/table | column | index | +--------------+----------+----------+ | test/test | position | position | | test/demo | position | position | +--------------+----------+----------+ 2 rows in set (0,00 sec) |
Second, we can update the SRID in an easier way, using the second parameter to the ST_SRID function:
|
1
2
3
4
5
|
mysql> UPDATE demo -> SET position = ST_SRID(position, 0) -> WHERE ST_SRID(position) != 0; Query OK, 2 rows affected (0,00 sec) Rows matched: 2 Changed: 2 Warnings: 0 |
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:
|
1
2
3
|
mysql> ALTER TABLE demo -> MODIFY COLUMN position POINT NOT NULL SRID 0; ERROR 3644 (HY000): The SRID specification on the column 'position' cannot be changed because there is a spatial index on the column. Please remove the spatial index before altering the SRID specification. |
The error message really says it all. So, let’s remove the index and try again:
|
1
2
3
4
5
6
7
8
|
mysql> DROP INDEX position ON demo; Query OK, 0 rows affected (0,00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE demo -> MODIFY COLUMN position POINT NOT NULL SRID 0; Query OK, 5 rows affected (0,00 sec) Records: 5 Duplicates: 0 Warnings: 0 |
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:
|
1
2
3
|
mysql> CREATE SPATIAL INDEX position ON demo (position); Query OK, 0 rows affected (0,00 sec) Records: 0 Duplicates: 0 Warnings: 0 |
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 !