Skip to main content

Index operations

This topic describes how to create, view, add, delete, and rename indexes in seekdb.

Create an index​

You can create an index in the following two ways:

  • 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 parameters are described as follows:

    • index_name: specifies the name of the index to be created.

    • table_name: specifies the name of the table to which the index is to be added.

    • sort_column_key: specifies the key of a sorting column. You can specify multiple sorting columns when you create an index. Separate the keys of multiple sorting columns with commas (,).

    • index_option: specifies the index options. You can specify multiple index options when you create an index. Separate the index options with spaces.

    • partition_option: specifies the options for creating index partitions.

    For more information, see CREATE INDEX.

    When you create an index on a table, you can still read from and write to the table. Here is an example:

    • Assume that a table named t1 exists in the database. Create an index named index1 on the c1 column of the t1 table and specify that the index sorts the values in the c1 column in ascending order.

      CREATE INDEX index1 ON t1 (c1 ASC);

    • Assume that a table named t2 exists in the database. Create an index named index2 on the t2 table and specify that the index expression is c1 + c2.

      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 parameters are described as follows:

    • table_name: specifies the name of the table to be created.

    • column_name: specifies the columns of the table.

    • column_definition: specifies the data types of the columns of the table.

    • INDEX | KEY: specifies the keyword for an index. You can use either INDEX or KEY.

    • index_name: specifies the name of the index to be created. This parameter is optional. If you do not specify this parameter, the index name is the same as the column name.

    • index_type: specifies the type of the index. This parameter is optional.

    • key_part: specifies the key part of the index. You can create a function index by using this parameter.

    • index_option: specifies the index options. You can specify multiple index options. Separate the index options with spaces.

    For more information, see CREATE TABLE.

    Here is an example of creating a table named t3 and an index named index3 on the t3 table. Specify that the index3 sorts the values in the id column in ascending order.

    CREATE TABLE t3
        (id int,
        name varchar(50),
        INDEX index3 (id ASC));

View an index​

You can execute the SHOW INDEX statement to view the indexes of a table. Here is an example of viewing the indexes of the test table:

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 related fields in the query result.

  • Non_unique: 0 indicates that the index does not allow duplicate values. 1 indicates that the index allows duplicate values. In other words, this field indicates whether the index is a unique index.

  • Key_name: the name of the index.

  • Seq_in_index: the sequence number of the column in the index. 1 or 2 indicates the order of the column in the composite index.

  • Column_name: the name of the column in the index.

  • Collation: the way in which the column is stored in the index.

  • Cardinality: the estimated number of unique values in the index.

  • Sub_part: the number of characters indexed for a prefix index. If the entire column is indexed, this field is NULL.

  • Packed: the compression method for the index. If the index is not compressed, this field is NULL.

  • Index_type: the type of the index. Currently, only the BTREE type is 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 is as follows:

ALTER TABLE table_name ADD {INDEX | KEY} index_name [index_type] (key_part,...) [index_option];

The parameters are described as follows:

  • table_name: specifies the name of the table to which the index is to be added.

  • INDEX | KEY: specifies the keyword for an index. You can use either INDEX or KEY.

  • index_name: specifies the name of the index to be created.

  • index_type: specifies the type of the index. This parameter is optional.

  • key_part: specifies the key part of the index. You can create a function index by using this parameter.

  • index_option: specifies the index options. You can specify multiple index options. Separate the index options with spaces.

For more information, see ALTER TABLE.

Assume that a table named t1 exists in the database. Add an index named index2 to the t1 table and specify that the index sorts the values in the c2 and c3 columns in ascending order. Here is an example:

ALTER TABLE t1 ADD INDEX index2 (c2,c3 ASC);

Drop an index​

You can drop an index in the following two ways:

  • DROP INDEX

    DROP INDEX index_name ON table_name;

    In the statement, index_name specifies the name of the index to be dropped, and table_name specifies the name of the table to which the index belongs. For more information, see DROP INDEX.

  • ALTER TABLE

    ALTER TABLE table_name DROP {INDEX | KEY} index_name;

    The parameters are described as follows:

    • table_name: specifies the name of the table to which the index belongs.

    • INDEX | KEY: specifies the keyword for an index. You can use either INDEX or KEY.

    • index_name: specifies the name of the index to be dropped.

    For more information, see ALTER TABLE.

When you drop an index on a table, you can still read from and write to the table.

Assume that a table named t1 exists in the database and an index named index1 exists on the t1 table. Here is an example of dropping the index:

  • DROP INDEX

    DROP INDEX index1 ON t1;

  • ALTER TABLE

    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 following table describes the parameters in the syntax.

  • table_name: specifies the name of the table that contains the index to be renamed.

  • INDEX | KEY: specifies the INDEX or KEY keyword.

  • old_index_name: specifies the name of the index to be renamed.

  • new_index_name: specifies the new name of the index.

For more information about the syntax, see ALTER TABLE.

Assume that the t1 table exists in the database and that an index named index2 exists on the t1 table. The following example renames the index2 index to index3:

ALTER TABLE t1 RENAME INDEX index2 TO index3;

References​

Contents

Edit this page