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, see Section 11.4.1, “Spatial Data Types” , and Section 8.3.3, “SPATIAL Index Optimization” . 

For more information on indexing spatial columns, see Section 13.1.15, “CREATE INDEX Statement” . 

To drop spatial indexes, use ALTER TABLE  or DROP INDEX : 

•With ALTER TABLE : 

ALTER TABLE geom DROP INDEX g; 

•With DROP INDEX : 

DROP INDEX g 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. 

## 11.4.11 Using Spatial Indexes 

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 

The optimizer investigates whether available spatial indexes can be involved in the search for queries that use a function such as MBRContains()  or MBRWithin()  in the WHERE  clause. The following query finds all objects that are in the given rectangle: 

mysql> SET @poly = -> ’Polygon((30000 15000, 31000 15000, 31000 16000, 30000 16000, 30000 15000))’; mysql> SELECT fid,ST_AsText(g) FROM geom WHERE -> MBRContains(ST_GeomFromText(@poly),g); +-----+---------------------------------------------------------------+ | fid | ST_AsText(g) | +-----+---------------------------------------------------------------+ 

