Movatterモバイル変換

MySQL 8.0 Reference Manual

13.4.10 Creating Spatial Indexes

ForInnoDB andMyISAM tables, MySQL can create spatial indexes using syntax similar to that for creating regular indexes, but using theSPATIAL keyword. Columns in spatial indexes must be declaredNOT NULL. The following examples demonstrate how to create spatial indexes:

WithCREATE TABLE:

CREATE TABLE geom (g GEOMETRY NOT NULL SRID 4326, SPATIAL INDEX(g));

WithALTER TABLE:

CREATE TABLE geom (g GEOMETRY NOT NULL SRID 4326);ALTER TABLE geom ADD SPATIAL INDEX(g);

WithCREATE INDEX:

CREATE TABLE geom (g GEOMETRY NOT NULL SRID 4326);CREATE SPATIAL INDEX g ON geom (g);

SPATIAL INDEX creates an R-tree index. For storage engines that support nonspatial indexing of spatial columns, the engine creates a B-tree index. A B-tree index on spatial values is useful for exact-value lookups, but not for range scans.

The optimizer can use spatial indexes defined on columns that are SRID-restricted. For more information, seeSection 13.4.1, “Spatial Data Types”, andSection 10.3.3, “SPATIAL Index Optimization”.

For more information on indexing spatial columns, seeSection 15.1.15, “CREATE INDEX Statement”.

To drop spatial indexes, useALTER TABLE orDROP INDEX:

WithALTER TABLE:
```
ALTER TABLE geom DROP INDEX g;
```
WithDROP INDEX:
```
DROP INDEX g ON geom;
```

Example: Suppose that a tablegeom contains more than 32,000 geometries, which are stored in the columng of typeGEOMETRY. The table also has anAUTO_INCREMENT columnfid for storing object ID values.

mysql> DESCRIBE geom;+-------+----------+------+-----+---------+----------------+| Field | Type     | Null | Key | Default | Extra          |+-------+----------+------+-----+---------+----------------+| fid   | int(11)  |      | PRI | NULL    | auto_increment || g     | geometry |      |     |         |                |+-------+----------+------+-----+---------+----------------+2 rows in set (0.00 sec)mysql> SELECT COUNT(*) FROM geom;+----------+| count(*) |+----------+|    32376 |+----------+1 row in set (0.00 sec)

To add a spatial index on the columng, use this statement:

mysql> ALTER TABLE geom ADD SPATIAL INDEX(g);Query OK, 32376 rows affected (4.05 sec)Records: 32376  Duplicates: 0  Warnings: 0

PREV HOME UP NEXT