Create an index

This topic describes how to create an index by using SQL statements. It also covers the prerequisites, concepts, limitations, and best practices for creating an index, and provides some examples.

Overview

An index, also known as a secondary index, is an optional table structure. OceanBase Database uses clustered index tables. If you specify a primary key, the system automatically generates a primary key index. Other indexes that you create are secondary indexes. You can create indexes on fields based on your business needs to speed up queries on these fields.

For more information about indexes in OceanBase Database, see Overview of indexes.

Prerequisites

Before you create an index, make sure that the following conditions are met:

• You have deployed an OceanBase cluster and created a MySQL-compatible tenant. For more information about how to deploy an OceanBase cluster, see Deployment overview.

• You have connected to the MySQL-compatible tenant of OceanBase Database. For more information about how to connect to a database, see Overview of connection methods.

• You have created a database. For more information about how to create a database, see Create a database.

• You have created a table. For more information about how to create a table, see Create a table.

• You have the INDEX privilege. To view the privileges of the current user, see View user privileges. If you do not have this privilege, contact your administrator to request the privilege. For more information about how to directly grant a privilege, see Directly grant a privilege.

Limitations

• In OceanBase Database, an index name must be unique within a database.

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

• Limitations on unique indexes:

  • You can create multiple unique indexes on one table, but the values of the columns in the unique indexes must be unique.

  • If you want the combination of other columns to meet the global uniqueness requirement in addition to the primary key, you must use a global unique index.

  • When you use a local unique index, the index must contain all the columns in the partitioning function of the table.

• When you use a global index, the partitioning rules of the global index do not necessarily have to be the same as or consistent with those of the table.

• Limitations on spatial indexes:

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

  • The columns on which you want to create spatial indexes must be defined with the SRID attribute. Otherwise, the spatial index will not take effect during subsequent queries. For more information about SRID, see Spatial reference system (SRS).

  • You can create a spatial index only on a column containing spatial data. For more information about the spatial data types supported by OceanBase Database, see Overview of spatial data types.

  • The column attribute of the column on which you want to create a spatial index must be NOT NULL. If it is not NOT NULL, you can execute the ALTER TABLE statement to change the column attribute to NOT NULL before you create the spatial index. For more information about how to change the column attribute, see Define a constraint for a column.

  • OceanBase Database does not support changing the SRID attribute of a spatial column by using the ALTER TABLE statement. Therefore, you must define the SRID attribute of a spatial column when you create a table so that the spatial index takes effect.

• Limitations and considerations on full-text indexes

  You can create a full-text index on a non-partitioned table or a partitioned table without a primary key. The limitations on creating a full-text index are as follows:

  • Full-text indexes apply only to CHAR, VARCHAR, and TEXT columns.
  • Only local (LOCAL) full-text indexes can be created.
  • The UNIQUE keyword cannot be specified when you create a full-text index.
  • If you want to create a full-text index that involves multiple columns, make sure that these columns have the same character set.
  • For OceanBase Database V4.3.5 BP3, creating columnstore full-text indexes is currently not supported.

Recommendations for creating indexes

• We recommend that you use a name that clearly describes the columns included in the index and its purpose, such as idx_customer_name. For more information about naming conventions, see Overview of object naming conventions.

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

• We recommend that you set the maximum number of concurrent index creation statements to no more than the number of CPU cores specified in the tenant unit specification. For example, if the tenant unit specification specifies 4 CPU cores (4C), we recommend that you create no more than four indexes in parallel.

• Avoid creating too many indexes on frequently updated tables. Create indexes on frequently queried columns.

• We recommend that you do not create indexes on tables with a small amount of data. This is because queries may run faster when scanning all the data rather than traversing the index.

• We recommend that you do not create indexes when the modification performance is much higher than the retrieval performance.

• To create an efficient index:

  • The index should contain all the columns required for queries. The more columns included, the fewer rows need to be fetched from the table.

  • The equality condition should always be placed at the beginning.

  • The filter and sort conditions that involve a large amount of data should be placed at the beginning.

Create an index by using commands

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

Examples

Example 1: Create a unique index

You can create a unique index on a column that contains no duplicate values.

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

1. Create the tbl1 table.

   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 of the tbl1 table.

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

3. View the index information 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

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

1. Create the tbl2 table.

   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 of the tbl2 table.

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

3. View the index information 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, also known as a partitioned index, is created by using the LOCAL keyword. The partitioning key of a local index is the same as that of the table, and the number of partitions of a local index is the same as that of the table. Therefore, the partitioning mechanism of a local index is the same as that of the table. Local indexes and unique local indexes are supported. If you want to use a unique local index to constrain the uniqueness of data, the unique local index must contain the partitioning key of the table.

Run the following SQL statements to create a range-list subpartitioned table named tbl3_rl and create a unique local index on the col1 and col2 columns of the tbl3_rl table.

1. Create the range-list subpartitioned table 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 a unique local index named idx_tbl3_rl_col1_col2 on the col1 and col2 columns of the tbl3_rl table.

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

3. View the index information 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

A global index is created by using the GLOBAL keyword.

Run the following SQL statements to create a single-partition table named tbl4_h and create a global index on the col2 column of the tbl4_h table.

1. Create the hash-partitioned table tbl4_h.

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

2. Create a global index named idx_tbl4_h_col2 on the col2 column of the tbl4_h table. The index is a range-partitioned index.

   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. View the index information 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 that is 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 the syntax for creating a spatial index, but the SPATIAL keyword must be used when creating a spatial index.

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

1. Create the tbl5 table.

   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 of the tbl5 table.

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

3. View the index information 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

An index created based on the values of one or more columns in a table after they are calculated is called a function-based index. A function-based index is an optimization technique that allows you to quickly locate the matching function values during a query, thus avoiding repeated calculations and improving query efficiency.

In MySQL mode, OceanBase Database limits the expressions for function-based indexes and prohibits using expressions of some system functions as function-based indexes. For more information, see List of system functions supported by function-based indexes and List of system functions not supported by function-based indexes.

Use the following SQL statement to create a table named tbl6 and create a function-based index on the c_time column.

1. Create the tbl6 table.

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

2. Create a function-based index named idx_tbl6_c_time on the c_time column of the tbl6 table.

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

3. Use the following SQL statement to view 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
   

Example 7: Create a columnstore index

A columnstore index is an index structure for columnar databases, primarily used to optimize query performance in columnar databases.

Run the following SQL statements to create a columnstore index.

After you create the tbl7 table, create a columnstore index named idx1_tbl7_cg.

1. Create the tbl7 table.

   CREATE TABLE tbl7(id INT, name VARCHAR(20), age INT);
   

2. Create a columnstore index named idx1_tbl7_cg on the tbl7 table and store redundant data of the age column in the index table.

   CREATE INDEX idx1_tbl7_cg ON tbl7(id) STORING(age) WITH COLUMN GROUP(each column);
   

3. Use the following SQL statement to view the created function-based index.

   SHOW INDEX FROM tbl7;
   

   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 |
   +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | tbl7  |          1 | idx1_tbl7_cg |            1 | id          | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
   +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   

Example 9: Create a full-text index

Here is an example:

After you create the tbl8 table, you can create a full-text index named ft_idx1_tbl8 on the table.

1. Create the tbl8 table.

   CREATE TABLE tbl8(col1 INT, col2 VARCHAR(4096));
   

2. Create a full-text index named ft_idx1_tbl8 on the tbl8 table. Specify the full-text index analyzer as IK and set the analyzer properties by using the PARSER_PROPERTIES clause.

   CREATE FULLTEXT INDEX ft_idx1_tbl8 ON tbl8(col2)
       WITH PARSER IK
       PARSER_PROPERTIES=(ik_mode='max_word');
   

3. Run the following SQL statement to view the full-text index.

   SHOW INDEX FROM tbl8;
   

   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 |
   +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | tbl8  |          1 | ft_idx1_tbl8 |            1 | col2        | A         |        NULL | NULL     | NULL   | YES  | FULLTEXT   | available |               | YES     | NULL       |
   +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   

What to do next

After you create an index, you may need to optimize query performance. For more information about SQL tuning, see Overview of SQL tuning.

References

• For more information about how to view indexes, see View indexes.
• For more information about how to manage indexes, see DROP INDEX and Delete an index.
• For more information about the system functions supported by function-based indexes, see List of system functions supported by function-based indexes.