content_image/710140.jpg
<img src='content_image/710140.jpg'>

## Syntax and Usage Notes 

•Creating or adding a secondary index 

CREATE INDEX name  ON table  ( col_list ); 

ALTER TABLE tbl_name  ADD INDEX name  ( col_list ); 

The table remains available for read and write operations while the index is being created. The CREATE INDEX  statement only finishes after all transactions that are accessing the table are completed, so that the initial state of the index reflects the most recent contents of the table. 

Online DDL support for adding secondary indexes means that you can generally speed the overall process of creating and loading a table and associated indexes by creating the table without secondary indexes, then adding secondary indexes after the data is loaded. 

A newly created secondary index contains only the committed data in the table at the time the CREATE INDEX  or ALTER TABLE  statement finishes executing. It does not contain any uncommitted values, old versions of values, or values marked for deletion but not yet removed from the old index. 

Some factors affect the performance, space usage, and semantics of this operation. For details, see Section 15.12.6, “Online DDL Limitations” . 

•Dropping an index 

DROP INDEX name  ON table ; 

ALTER TABLE tbl_name  DROP INDEX name ; 

The table remains available for read and write operations while the index is being dropped. The DROP INDEX  statement only finishes after all transactions that are accessing the table are completed, so that the initial state of the index reflects the most recent contents of the table. 

•Renaming an index 

ALTER TABLE tbl_name  RENAME INDEX old_index_name  TO new_index_name , ALGORITHM=INPLACE, LOCK=NONE; 

•Adding a FULLTEXT  index 

CREATE FULLTEXT INDEX name  ON table( column ); 

Adding the first FULLTEXT  index rebuilds the table if there is no user-defined FTS_DOC_ID  column. Additional FULLTEXT  indexes may be added without rebuilding the table. 

•Adding a SPATIAL  index 

CREATE TABLE geom (g GEOMETRY NOT NULL); ALTER TABLE geom ADD SPATIAL INDEX(g), ALGORITHM=INPLACE, LOCK=SHARED; 

•Changing the index type ( USING {BTREE | HASH} ) 

