(mysql.info.gz) Creating spatial indexes
Info Catalog
(mysql.info.gz) Optimizing spatial analysis
(mysql.info.gz) Optimizing spatial analysis
(mysql.info.gz) Using a spatial index
18.6.1 Creating Spatial Indexes
-------------------------------
MySQL can create spatial indexes using syntax similar to that for
creating regular indexes, but extended with the `SPATIAL' keyword.
Spatial columns that are indexed currently must be declared `NOT NULL'.
The following examples demonstrate how to create spatial indexes.
* With `CREATE TABLE':
mysql> CREATE TABLE geom (g GEOMETRY NOT NULL, SPATIAL INDEX(g));
* With `ALTER TABLE':
mysql> ALTER TABLE geom ADD SPATIAL INDEX(g);
* With `CREATE INDEX':
mysql> CREATE SPATIAL INDEX sp_index ON geom (g);
To drop spatial indexes, use `ALTER TABLE' or `DROP INDEX':
* With `ALTER TABLE':
mysql> ALTER TABLE geom DROP INDEX g;
* With `DROP INDEX':
mysql> DROP INDEX sp_index ON geom;
Example: Suppose that a table `geom' contains more than 32,000
geometries, which are stored in the column `g' of type `GEOMETRY'. The
table also has an `AUTO_INCREMENT' column `fid' 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 column `g', 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
Info Catalog
(mysql.info.gz) Optimizing spatial analysis
(mysql.info.gz) Optimizing spatial analysis
(mysql.info.gz) Using a spatial index
automatically generated byinfo2html