(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