This topic describes basic index operations, including creating, viewing, adding, dropping, and renaming indexes.
Create an index
You can create an index by using one of the following two methods:
CREATE INDEX
CREATE [SPATIAL | UNIQUE] INDEX [IF NOT EXISTS] index_name [USING BTREE] ON table_name (sort_column_key [, sort_column_key... ]) [index_option...] [partition_option];The related parameters are described as follows:
index_name: The name of the index to be created.table_name: the name of the table for which you want to create an index.sort_column_key: The key of the column to sort by. You can specify multiple columns when creating an index, which must be separated with commas (,).index_option: Specifies index options. Multiple index options can be specified when creating an index, separated by English spaces.partition_option: Specifies the options for creating an index partition.
For more information, see CREATE INDEX.
You can still use a table for read and write operations while creating an index on it. The following example shows how to create an index.
Assume that a table named t1 already exists in the database. Create an index named index1 on table t1 and specify that this index sorts data in column c1 in ascending order.
obclient> CREATE INDEX index1 ON t1 (c1 ASC);Assume that a table named t2 already exists in the database. Create an index named index2 on the t2 table and specify c1+c2 as the index expression.
obclient> CREATE INDEX IF NOT EXISTS index2 ON t2 (c1 + c2);
CREATE TABLE
CREATE TABLE [IF NOT EXISTS] table_name (column_name column_definition,[column_name column_definition,...], {INDEX | KEY} [index_name] [index_type] (key_part,...) [index_option...]);The related parameters are described as follows:
table_name: the name of the table to be created.column_name: the name of the column in the table.column_definition: Defines the data types of each column in the table.INDEX | KEY: Indicates that the index keyword can be used as either INDEX or KEY in this statement.index_name: Optional. The name of the index to be created. If not specified, the default index name is the same as the specified column name.index_type: Optional. Specifies the index type.key_part: Creates a (function-based) index.index_option: Specifies index options. Separate multipleindex_optionentries with spaces.
For more information, see CREATE TABLE.
This example shows how to create the
t3table and the indexindex3in the database, specifying thatindex3sorts data in theidcolumn in ascending order. The command is as follows:obclient> CREATE TABLE t3 (id int, name varchar(50), INDEX index3 (id ASC));
View indexes
You can execute the SHOW INDEX statement to view the indexes of a table. The following example shows how to view the indexes on the test table:
obclient> SHOW INDEX FROM test;
The output is as follows:
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
| test | 0 | PRIMARY | 1 | id | A | NULL | NULL | NULL | | BTREE | available | | YES | NULL |
| test | 1 | index1 | 1 | name | A | NULL | NULL | NULL | | BTREE | available | | YES | NULL |
| test | 1 | index2 | 1 | age | A | NULL | NULL | NULL | | BTREE | available | | YES | NULL |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
The following table describes the columns in the query results:
Non_unique:0if the index cannot contain duplicate values, and1if it can. This is what is commonly referred to as a unique index.Key_name: The name of the index.Seq_in_index: The column sequence number in the index,1or2, indicating the order of this index in the composite index.Column_name: the name of the index column.Collation: specifies how the column is stored in the index.Cardinality: The estimated number of unique values in the index.Sub_part: The prefix index. If only a portion of the column is included in the index, this is the number of characters that are indexed. If the entire column is indexed, this value isNULL.Packed: Indicates how the keywords are compressed. If not compressed, it isNULL.Index_type: the index type. Currently, onlyBTREEis supported.Comment: Indicates whether the index is available.Index_comment: the comment of the index.Visible: Indicates whether the index is visible.
Add an index
The syntax for adding an index is as follows:
ALTER TABLE table_name ADD {INDEX | KEY} index_name [index_type] (key_part,...) [index_option];
The related parameters are described as follows:
table_name: The name of the table to which an index is to be added.INDEX | KEY: Indicates that the index keyword can be used as either INDEX or KEY in this statement.index_name: The name of the index to be created.index_type: Optional. Specifies the index type.key_part: Creates a key (function) index.index_option: Specifies index options. Separate multipleindex_optionentries with spaces.
For detailed syntax information, see ALTER TABLE.
Assume that the database already contains a table named t1. Add an index named index2 on table t1, specifying that this index sorts the c2 and c3 columns in ascending order. The execution example is as follows:
obclient> ALTER TABLE t1 ADD INDEX index2 (c2,c3 ASC);
Drop an index
The syntax for dropping an index includes the following two forms:
DROP INDEX
DROP INDEX index_name ON table_name;Here,
index_namespecifies the name of the index to be dropped;table_namespecifies the name of the table that contains the index. For more information about the syntax, see DROP TABLE.ALTER TABLE
ALTER TABLE table_name DROP {INDEX | KEY} index_name;The related parameters are described as follows:
table_namespecifies the name of the table from which you want to drop an index.INDEX | KEYindicates that in this statement, eitherINDEXorKEYcan be used as the index keyword.index_namespecifies the name of the index to be dropped.
For more information, see ALTER TABLE.
When you drop an index on a table, the table can still be used for read and write operations.
Assume that the database already contains a table named t1 and an index named index1 on table t1. The following example shows how to drop an index:
DROP INDEX
obclient> DROP INDEX index1 ON t1;ALTER TABLE
obclient> ALTER TABLE t1 DROP INDEX index1;
Rename an index
The syntax for renaming an index is as follows:
ALTER TABLE table_name RENAME {INDEX | KEY} old_index_name TO new_index_name;
The related parameters are described as follows:
table_name: the name of the table that contains the index to be renamed.INDEX | KEY: Indicates that in this statement, the index keyword can be eitherINDEXorKEY.old_index_name: the name of the index to be renamed.new_index_name: The name of the index after renaming.
For detailed syntax information, see ALTER TABLE.
Assume that a table named t1 already exists in the database and an index named index2 exists on the table t1. To rename the index index2 to index3, execute the following statement:
obclient> ALTER TABLE t1 RENAME INDEX index2 TO index3;