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:
|
1
2
3
4
5
6
|
mysql> CREATE TABLE places ( -> pk INT PRIMARY KEY, -> position POINT NOT NULL SRID 4326, -> name VARCHAR(200) -> ); Query OK, 0 rows affected (0,00 sec) |
If we try to insert a geometry in a different SRID, we get an error:
|
1
2
|
mysql> INSERT INTO places VALUES (1, ST_GeomFromText('POINT(63.4269 10.3958)', 0), 'Nidaros Cathedral'); ERROR 3643 (HY000): The SRID of the geometry does not match the SRID of the column 'position'. The SRID of the geometry is 0, but the SRID of the column is 4326. Consider changing the SRID of the geometry or the SRID property of the column. |
If we comply with the SRID restriction, the point is inserted:
|
1
2
|
mysql> INSERT INTO places VALUES (1, ST_GeomFromText('POINT(63.4269 10.3958)', 4326), 'Nidaros Cathedral'); Query OK, 1 row affected (0,00 sec) |
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:
|
1
2
|
mysql> DROP SPATIAL REFERENCE SYSTEM 4326; ERROR 3716 (SR005): Can't modify SRID 4326. There is at least one column depending on it. |
Which column is that?
|
1
2
3
4
5
6
7
8
9
10
|
mysql> SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS WHERE SRS_ID=4326\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: test TABLE_NAME: places COLUMN_NAME: position SRS_NAME: WGS 84 SRS_ID: 4326 GEOMETRY_TYPE_NAME: point 1 row in set (0,00 sec) |
Indexes
We can easily create an index on our geometry column.
|
1
2
3
|
mysql> CREATE SPATIAL INDEX position ON places (position); Query OK, 0 rows affected (0,00 sec) Records: 0 Duplicates: 0 Warnings: 0 |
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:
|
1
2
3
4
5
6
7
8
9
10
11
12
|
mysql> CREATE TABLE dont_do_this ( -> pk INT PRIMARY KEY, -> position POINT NOT NULL, -> name VARCHAR(200) -> ); Query OK, 0 rows affected (0,00 sec) mysql> CREATE SPATIAL INDEX position ON dont_do_this (position); Query OK, 0 rows affected, 1 warning (0,00 sec) Records: 0 Duplicates: 0 Warnings: 1 Warning (Code 3674): The spatial index on column 'position' will not be used by the query optimizer since the column does not have an SRID attribute. Consider adding an SRID attribute to the column. |
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:
|
1
2
3
4
5
6
|
mysql> CREATE TABLE places ( -> pk INT PRIMARY KEY, -> position POINT NOT NULL SRID 4326, -> name VARCHAR(200) -> ) ENGINE=MyISAM; ERROR 1178 (42000): The storage engine for the table doesn't support geographic spatial reference systems |
If we try the same with the SRID of a Cartesian SRS, we are allowed to create the table:
|
1
2
3
4
5
6
|
mysql> CREATE TABLE places ( -> pk INT PRIMARY KEY, -> position POINT NOT NULL SRID 3857, -> name VARCHAR(200) -> ) ENGINE=MyISAM; Query OK, 0 rows affected (0,00 sec) |
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!