Create an index

This topic describes the definition of indexes, as well as the prerequisites, limitations, considerations, and examples for creating an index by using an SQL statement.

About indexes

An index, also known as a secondary index, is an optional structure. OceanBase Cloud 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.

Prerequisites

Before you create an index, make sure that:

• You have deployed an OceanBase cluster and created a MySQL-compatible tenant.

• You have connected to the MySQL-compatible tenant of OceanBase Cloud.

• You have created a database.

• You have created a table.

• You have the INDEX privilege. If you do not have the required privileges, contact the administrator to obtain the privileges.

Limitations

• In OceanBase Cloud, an index name must be unique in a table.

• An index name cannot exceed 64 bytes in length.

• The limitations on unique indexes are as follows:

  • You can create multiple unique indexes that have unique column values on a table.

  • If you want to ensure global uniqueness for other column combinations besides a primary key, you can create a global unique index.

  • A local unique index must contain all columns in the partitioning function of the table.

• The partitioning rules of a global index are not necessarily the same as those of the table.

• The limitations on spatial indexes are as follows:

  • You can create only a local spatial index. Global spatial indexes are not supported.

  • You must define the SRID attribute for the columns on which a spatial index is to be created. Otherwise, the spatial index cannot take effect in subsequent queries.

  • You can create a spatial index only on columns of spatial data types.

  • You can create a spatial index only on columns that have the NOT NULL constraint. For a column without the NOT NULL constraint, you can use the ALTER TABLE statement to add the NOT NULL constraint to the column and then create a spatial index on the column.

  • At present, OceanBase Cloud does not allow you to modify the SRID attribute of a column by using the ALTER TABLE statement. Therefore, when you create a table, you must properly define the SRID attribute for a spatial column so that the spatial index can take effect.

Considerations

• We recommend that you use a name that succinctly describes the columns covered by the index and its purpose, for example, idx_customer_name.

• If a global index has the same partitioning rules and the same number of partitions as the table, we recommend that you create a local index in this case.

• We recommend that the number of parallel SQL statements that you issue to create indexes do not exceed the maximum number of CPU cores specified for the unit config of the tenant. For example, if the unit config of a tenant has 4 CPU cores, we recommend that you create no more than 4 indexes concurrently.

• Create indexes on fields that are frequently used for queries, but do not create excess 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:

  • Include all necessary columns required for queries. The more relevant columns included in the index, the better it can minimize the number of rows returned.

  • 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

Use the CREATE INDEX statement to create an index.

Examples

Example 1: Create a unique index

To ensure that an indexed column has no duplicate values, you can create a unique index.

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

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 return 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 return 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 is also referred to as a partitioned index. The keyword for creating a local index is LOCAL. A local index has the same partitioning key and number of partitions as the table, meaning that the index and the table have the same partitioning mechanism. You can create local indexes and local unique indexes. To use a local unique index to ensure data uniqueness, this local unique index must contain the partitioning key of the table.

Execute the following SQL statements to create a subpartitioned table named tbl3_rl and create a local unique index on the col1 and col2 columns in the tbl3_rl table.

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 return 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 return 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. Spatial indexes are extensively used in the geographic information system (GIS) and in the storage and query of location data. OceanBase Cloud allows you to create spatial indexes in the same way of creating regular indexes. However, you must use the SPATIAL keyword when you create a spatial index.

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 return 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

A function-based index is created based on the result of any function applied to a column or multiple columns. Function-based indexing is an optimization technique. You can use function-based indexes to quickly locate function values that match query conditions. This avoids repeated calculation and improves query efficiency.

OceanBase Cloud in the MySQL compatible mode imposes limitations on the expressions of function-based indexes. The expressions of some system functions cannot be used as 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 return 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.