Create an index

This topic describes how to create an index with some examples.

Overview

An index, also known as a secondary index, is an optional structure. OceanBase Database uses the clustered index table model. The system automatically generates a primary key index for the specified primary key, and other indexes that you create are secondary indexes. You can determine the fields on which indexes are to be created based on business needs to speed up queries on these fields.

For more information about indexes in OceanBase Database, see About indexes.

Prerequisites

Before you create an index, make sure that:

• You have deployed an OceanBase cluster and created a MySQL tenant. For more information, see Deployment overview.

• You have connected to the MySQL tenant of OceanBase Database. For more information, see Overview of connection methods.

• You have created a database. For more information, see Create a database.

• You have created a table. For more information, see Create a table.

• You have the INDEX privilege. For more information about how to view your privileges, see View user privileges. If you do not have the required privileges, contact the administrator to obtain the privileges. For more information, see Grant direct privileges.

Limitations

• In OceanBase Database, the index name must be unique within the table.

• The length of the index name cannot exceed 64 bytes.

• Unique index restrictions:

  • Multiple unique indexes can be created on a table, but each unique index must ensure the uniqueness of the corresponding column values.

  • If a combination of columns, other than the primary key, needs to satisfy global uniqueness requirements, a global unique index must be used.

  • When using a local unique index, the index must include all columns from the table's partition function.

• When using a global index, the partition rules for the global index do not necessarily need to be completely identical to the partition rules for the table.

• Spatial index restrictions:

  • Spatial indexes only support local indexes and do not support global indexes.

  • Columns used for creating spatial indexes must be defined with the SRID attribute; otherwise, spatial indexes added on such columns will not be effective for subsequent queries. For information about SRID, see Spatial reference system (SRS).

  • Spatial indexes can only be created on columns with spatial data types. For information about spatial data types supported by OceanBase Database, see Overview.

  • The column attribute for creating a spatial index must be NOT NULL. If it is not NOT NULL, you can first modify the column attribute to NOT NULL using the ALTER TABLE statement before adding the spatial index. For more information about how to modify column attributes, see Define column constraints.

  • OceanBase Database does not currently support the modification of a column's SRID attribute via the ALTER TABLE statement. Therefore, the SRID attribute for spatial columns must be defined when creating the table in order for the spatial index to take effect.

Considerations

• We recommend that you use names that succinctly describe the columns covered by the index and its purpose, for example, idx_customer_name. For more information about naming conventions, see Object naming conventions.

• If the partition rules for a global index are the same as those for the primary table and have the same number of partitions, we recommend that you create a local index.

• We recommend that you limit the number of SQL statements for parallel index creation to the maximum number of CPU cores specified in the tenant's unit specifications. For example, if the tenant's unit specification is 4 CPU cores (4C), it is recommended to have no more than 4 index creation statements executed concurrently.

• We recommend that you create indexes on fields that are frequently used for queries, but do not create excessive indexes on tables that are frequently updated.

• Do not create indexes on tables with a small amount of data. For a table with a small data amount, it may take a shorter time to query all the data than to traverse the indexes. In this case, indexes cannot produce optimization effects.

• If modification requirements are far more than retrieval requirements, do not create indexes.

• Create efficient indexes:

  • Indexes must be created on all the columns to be queried. This can reduce the number of rows returned from table access by index primary key.

  • Indexes of equivalent conditions should always be placed in the front of the index table.

  • Indexes for large data filtering and sorting should be placed in the front of the index table.

Create an index by using a statement

You can use the CREATE INDEX statement to create an index.

Examples

Example 1: Create a unique index

If there is a need to ensure that the values in an indexed column are unique, a unique index can be created.

Use the following SQL statements to create a table named tbl1 and to create a unique index based on the col2 column in table tbl1.

1. Create a table named tbl1.

   obclient [test]> CREATE TABLE tbl1(col1 INT, col2 INT, col3 VARCHAR(50), PRIMARY KEY (col1));
   

2. Create a unique index named idx_tbl1_col2 on the col2 column in the tbl1 table.

   obclient [test]> CREATE UNIQUE INDEX idx_tbl1_col2 ON tbl1(col2);
   

3. Query indexes of the tbl1 table.

   obclient [test]> SHOW INDEX FROM tbl1;
   

   The result 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 |
   +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | tbl1  |          0 | PRIMARY       |            1 | col1        | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     | NULL       |
   | tbl1  |          0 | idx_tbl1_col2 |            1 | col2        | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
   +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   2 rows in set
   

Example 2: Create a non-unique index

Execute the following SQL statements to create a table named tbl2 and create an index on the col2 column in the tbl2 table.

1. Create a table named tbl2.

   obclient [test]> CREATE TABLE tbl2(col1 INT, col2 INT, col3 VARCHAR(50), PRIMARY KEY (col1));
   

2. Create an index named idx_tbl2_col2 on the col2 column in the tbl2 table.

   obclient [test]> CREATE INDEX idx_tbl2_col2 ON tbl2(col2);
   

3. Query indexes of the tbl2 table.

   obclient [test]> SHOW INDEX FROM tbl2;
   

   The result 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 |
   +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | tbl2  |          0 | PRIMARY       |            1 | col1        | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     | NULL       |
   | tbl2  |          1 | idx_tbl2_col2 |            1 | col2        | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
   +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   2 rows in set
   

Example 3: Create a local index

A local index, also known as a partitioned index, is created using the keyword LOCAL. The partition key for a local index is equivalent to the partition key of the table, and the number of partitions for a local index is the same as that for the table, so the partitioning mechanism for a local index is the same as that for the table. Local indexes and local unique indexes are supported. If a local unique index is to be used to enforce the uniqueness of data, the table partition key must be included in the local unique index.

Use the following SQL statements to create a sub-partitioned table named tbl3_rl and to create a local unique index on the col1 and col2 columns in table tbl3_rl.

1. Create a RANGE-LIST-subpartitioned table named tbl3_rl.

   obclient [test]> CREATE TABLE tbl3_rl(col1 INT,col2 INT)
     PARTITION BY RANGE(col1)
     SUBPARTITION BY LIST(col2)
       (PARTITION p0 VALUES LESS THAN(100)
         (SUBPARTITION sp0 VALUES IN(1,3),
         SUBPARTITION sp1 VALUES IN(4,6),
         SUBPARTITION sp2 VALUES IN(7,9)),
       PARTITION p1 VALUES LESS THAN(200)
         (SUBPARTITION sp3 VALUES IN(1,3),
         SUBPARTITION sp4 VALUES IN(4,6),
         SUBPARTITION sp5 VALUES IN(7,9))
       );
   

2. Create an index named idx_tbl3_rl_col1_col2 on the col1 and col2 columns in the tbl3_rl table.

   obclient [test]> CREATE UNIQUE INDEX idx_tbl3_rl_col1_col2 ON tbl3_rl(col1,col2) LOCAL;
   

3. Query indexes of the tbl3_rl table.

   obclient [test]> SHOW INDEX FROM tbl3_rl;
   

   The result 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 |
   +---------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | tbl3_rl |          0 | idx_tbl3_rl_col1_col2 |            1 | col1        | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
   | tbl3_rl |          0 | idx_tbl3_rl_col1_col2 |            2 | col2        | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
   +---------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   2 rows in set
   

Example 4: Create a global index

The keyword for creating a global index is GLOBAL.

Execute the following SQL statements to create a partitioned table named tbl4_h and create a global index on the col2 column in the tbl4_h table.

1. Create a HASH-partitioned table named tbl4_h.

   obclient [test]> CREATE TABLE tbl4_h(col1 INT PRIMARY KEY,col2 INT)
     PARTITION BY HASH(col1) PARTITIONS 5;
   

2. Create a RANGE-partitioned global index named idx_tbl4_h_col2 on the col2 column in the tbl4_h table.

   obclient [test]> CREATE INDEX idx_tbl4_h_col2 ON tbl4_h(col2) GLOBAL
     PARTITION BY RANGE(col2)
     (PARTITION p0 VALUES LESS THAN(100),
       PARTITION p1 VALUES LESS THAN(200),
       PARTITION p2 VALUES LESS THAN(300)
     );
   

3. Query indexes of the tbl4_h table.

   obclient [test]> SHOW INDEX FROM tbl4_h;
   

   The result 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 |
   +--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | tbl4_h |          0 | PRIMARY         |            1 | col1        | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     | NULL       |
   | tbl4_h |          1 | idx_tbl4_h_col2 |            1 | col2        | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
   +--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   2 rows in set
   

Example 5: Create a spatial index

A spatial index is a database index used to process and optimize spatial data. It is widely used in geographic information systems (GIS) and for storing and querying location data. OceanBase Database supports creating spatial indexes using the syntax for regular indexes, but spatial indexes require the use of the SPATIAL keyword.

Execute the following SQL statements to create a table named tbl5 and create a spatial index on the g column in the tbl5 table.

1. Create a table named tbl5.

   obclient [test]> CREATE TABLE tbl5(id INT,name VARCHAR(20),g GEOMETRY NOT NULL SRID 0);
   

2. Create a spatial index named idx_tbl5_g on the g column in the tbl5 table.

   obclient [test]> CREATE INDEX idx_tbl5_g ON tbl5(g);
   

3. Query indexes of the tbl5 table.

   obclient [test]> SHOW INDEX FROM tbl5;
   

   The result 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 |
   +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | tbl5  |          1 | idx_tbl5_g |            1 | g           | A         |        NULL | NULL     | NULL   |      | SPATIAL    | available |               | YES     | NULL       |
   +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   1 row in set
   

Example 6: Create a function-based index

An index created based on the results of calculations on one or more columns in a table is called a function-based index. A function-based index is an optimization technique that allows for the quick location of matching function values during queries, thus avoiding duplicate calculations and improving query efficiency.

In the MySQL mode of OceanBase Database, there are restrictions on the expressions for function-based indexes. Some system functions are prohibited from being used in function-based indexes. For the specific list of allowed and disallowed system functions in function-based indexes, see System functions supported for function-based indexes and System functions not supported for function-based indexes.

Execute the following SQL statements to create a table named tbl6 and create a function-based index on the c_time column in the tbl6 table.

1. Create a table named tbl6.

   obclient [test]> CREATE TABLE tbl6(id INT, name VARCHAR(18), c_time DATE);
   

2. Create an index named idx_tbl6_c_time on the year part of the c_time column in the tbl6 table.

   obclient [test]> CREATE INDEX idx_tbl6_c_time ON tbl6((YEAR(c_time)));
   

3. Query the created function-based index.

   SHOW INDEX FROM tbl6;
   

   The result is as follows:

   obclient [test]> SHOW INDEX FROM tbl6;
   +-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+----------------+
   | Table | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression     |
   +-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+----------------+
   | tbl6  |          1 | idx_tbl6_c_time |            1 | SYS_NC19$   | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | year(`c_time`) |
   +-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+----------------+
   1 row in set
   

What to do next

After you create an index, you can optimize query performance as needed. For more information about SQL tuning, see SQL tuning.

References

• For more information about how to query indexes, see Query indexes.
• For more information about how to manage indexes, see DROP INDEX and Drop an index.
• For more information about the system functions supported for function-based indexes, see System functions supported for function-based indexes.