Create an index

After you create a table, you can create an index on one or more columns in the table to speed up the execution of most SQL queries on the table.

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.

Indexes are classified into unique and non-unique indexes.

• Unique indexes ensure that no two rows of a table have duplicate values in an indexed column. In OceanBase Database, NULL values are also stored in indexes.

• Non-unique indexes allow duplicate values in an indexed column.

For more information, see Unique and non-unique indexes.

Indexes on a partitioned table can be classified into local indexes and global indexes.

• A local index is created on data in a single partition. The key-value pairs of the index and the data in the table are in a one-to-one match. Each index partition maps to one table partition. They share the same partitioning rules. Therefore, a local unique index is only guaranteed to be unique in a partition. Its uniqueness within the table is not guaranteed.

• A global index has its independent partitioning rules, and one partition of a global index may point to multiple table partitions.

For more information, see Local and global indexes.

Prerequisites

Before you create indexes, make sure that:

• You have logged on to a MySQL tenant of OceanBase Database. For more information about how to connect to a database, see Connection methods.

  Note

  You can query the oceanbase.DBA_OB_TENANTS view in the sys tenant to confirm the mode of the tenant to which you have logged on.

• You have the INDEX privilege, and the ALTER privilege on the table on which indexes are to be created. For more information about how to view your privileges, see View user privileges. If you do not have the required privileges, contact the administrator. For more information, see Modify user privileges.

• A database has been created. For more information, see Create a database.

• A table has been created. For more information, see Create a table.

Considerations

• 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.

• 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 a unique index

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

You can use the CREATE UNIQUE INDEX statement to create a unique index on an existing table.

You can also create a unique index on an existing table when you use the ALTER TABLE statement to modify the table. This statement allows you to create multiple indexes at a time. You can use either the INDEX or KEY keyword.

Observe the following requirements when you create unique indexes:

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

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

• An indexed column can have NULL values.

Here are the examples:

• Use the CREATE UNIQUE INDEX statement to create a unique index on a table.

  obclient> CREATE TABLE test(c1 INT, c2 INT, c3 INT, c4 INT, PRIMARY KEY  (c1));
  Query OK, 0 rows affected
  
  obclient> CREATE UNIQUE INDEX idx1 ON test(c2);
  Query OK, 0 rows affected
  

• Use the ALTER TABLE statement to create a unique index on a table.

  obclient> CREATE TABLE test(c1 INT, c2 INT, c3 INT, c4 INT, PRIMARY KEY  (c1));
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE test ADD UNIQUE INDEX idx2 (c2);
  Query OK, 0 row affected
  

Create a non-unique index

You can use the CREATE INDEX statement to create a non-unique index on a table.

You can also create a non-unique index on an existing table when you use the ALTER TABLE statement to modify the table. This statement allows you to create multiple indexes at a time. You can use either the INDEX or KEY keyword.

Observe the following requirements when you create non-unique indexes:

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

• An indexed column can have duplicate values and NULL values.

Here are the examples:

• Use the CREATE INDEX statement to create a non-unique index on a table.

  obclient> CREATE TABLE test(c1 INT, c2 INT, c3 INT, c4 INT, PRIMARY KEY(c1));
  Query OK, 0 rows affected
  
  obclient> CREATE INDEX idx1 ON test(c2);
  Query OK, 0 rows affected
  

• Use the ALTER TABLE statement to create a non-unique index on a table.

  obclient> CREATE TABLE test(c1 INT, c2 INT, c3 INT, c4 INT, PRIMARY KEY  (c1));
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE test ADD INDEX idx2 (c1,c2);
  Query OK, 0 row affected
  

Create a local index

A local index is also referred to as a partition 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.

You can use the CREATE INDEX statement together with the LOCAL keyword to create a local index on an existing table.

You can also use the CREATE UNIQUE INDEX statement together with the LOCAL keyword to create a local unique index on an existing table.

Observe the following requirements when you create local indexes:

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

• In the MySQL mode of OceanBase Database, if no attribute keyword is specified for an index, the default index attribute LOCAL takes effect. In other words, a local index is created by default.

• To create a local unique index, make sure that it contains all columns in the partitioning function of the table.

Here are the examples:

• Create a local index.

  obclient> CREATE TABLE tbl1_h(col1 INT PRIMARY KEY,col2 INT)
         PARTITION BY HASH(col1) PARTITIONS 5;
  Query OK, 0 rows affected
  
  obclient> CREATE INDEX tbl1_h_idx1 ON tbl1_h(col2) LOCAL;
  Query OK, 0 rows affected
  

• Create a local unique index.

  obclient> CREATE TABLE tbl2_f_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))
        );
  Query OK, 0 rows affected
  
  obclient> CREATE UNIQUE INDEX tbl2_f_rl_idx1 ON tbl2_f_rl(col1,col2) LOCAL;
  Query OK, 0 rows affected
  

Create a global index

The keyword for creating a global index is GLOBAL.

You can use the CREATE INDEX statement together with the GLOBAL keyword to create a global index on an existing table.

You can also use the CREATE UNIQUE INDEX statement together with the GLOBAL keyword to create a global unique index on an existing table.

Observe the following requirements when you create global indexes:

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

• In the MySQL mode of OceanBase Database, if no attribute keyword is specified for an index, the default index attribute LOCAL takes effect. In other words, a local index is created by default.

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

• 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. The reasons are twofold. First, global indexes cost higher to maintain. Second, the physical storage location of a table and a global index are not necessarily the same, unless you manually include them in the same table group.

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

Here are the examples:

• Create a global index.

  Create a HASH-partitioned table tbl1_h, and then create a RANGE-partitioned global index tbl1_h_idx1 for the table.

  obclient> CREATE TABLE tbl1_h(col1 INT PRIMARY KEY,col2 INT)
        PARTITION BY HASH(col1) PARTITIONS 5;
  Query OK, 0 rows affected
  
  obclient> CREATE INDEX tbl1_h_idx1 ON tbl1_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)
        );
  Query OK, 0 rows affected
  

• Create a global unique index.

  Create a RANGE-LIST-subpartitioned table tbl2_f_rl without using a template, and then create a global unique index tbl2_f_rl_idx1 for the table.

  obclient> CREATE TABLE tbl2_f_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))
        );
  Query OK, 0 rows affected
  
  obclient> CREATE UNIQUE INDEX tbl2_f_rl_idx1 ON tbl2_f_rl(col1) GLOBAL;
  Query OK, 0 rows affected
  

Create spatial indexes

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.

A spatial index maps spatial data, such as dots, lines, and planes, to nodes and leaf nodes in the index structure to improve spatial data query performance. Spatial indexes can speed up queries, support spatial relationship queries, and optimize query plans. Spatial indexes are extremely helpful in application scenarios where a large amount of spatial data is processed, such as GIS, location service, logistics, and geographical analysis.

Limitations

When you create a spatial index, note that:

• 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. This is because, the coordinates corresponding to the SRID attribute are required for comparison based on the spatial index. In addition, the spatial index stores the minimum bounding rectangle (MBR) of a geometry, and the MBR comparison also depends on the SRID.

  For more information about SRID, see SRS.

• You can create a spatial index only on columns of spatial data types. For more information about the supported spatial data types, see Overview 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. For more information about modifying a column attribute, see Define column constraints.

• At present, OceanBase Database 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.

Methods for creating spatial indexes

OceanBase Database 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.

You can create a spatial index in the following ways:

• To create a spatial index when you create a table by using the CREATE TABLE statement, use the following syntax:

  CREATE TABLE table_name(column_g_name GEOMETRY NOT NULL,[column_name data_type,...] SPATIAL INDEX index_name(column_g_name));
  

• To create a spatial index when you modify a table by using the ALTER TABLE statement, use the following syntax:

  ALTER TABLE table_name ADD SPATIAL INDEX index_name(column_g_name);
  

• To create a spatial index by using the CREATE INDEX statement, use the following syntax:

  CREATE SPATIAL INDEX index_name ON table_name(column_g_name);
  

  In a MySQL tenant, index names must be unique in a table. You can use the SHOW INDEX statement to view the indexes on a table.

Parameters in the above statements are described as follows:

Examples of creating spatial indexes

• Create a table named tbl1_g and create an index named tbl1_g_idx1 on this table.

  obclient [test]> CREATE TABLE tbl1_g(id INT,name VARCHAR(18),g GEOMETRY NOT NULL SRID 0,SPATIAL INDEX tbl1_g_idx1(g));
  Query OK, 0 rows affected
  
  obclient [test]> SHOW INDEX FROM tbl1_g;
  +--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  | Table  | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
  +--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  | tbl1_g |          1 | tbl1_g_idx1 |            1 | g           | A         |        NULL | NULL     | NULL   |      | SPATIAL    | available |               | YES     |
  +--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  1 row in set
  

• Create a table named tbl2_g and then create an index named tbl2_g_idx1 when you modify the table.

  obclient [test]> CREATE TABLE tbl2_g(id INT,name VARCHAR(20),g GEOMETRY NOT NULL SRID 0);
  Query OK, 0 rows affected
  
  obclient [test]> ALTER TABLE tbl2_g ADD SPATIAL INDEX tbl2_g_idx1(g);
  Query OK, 0 rows affected
  
  obclient [test]> SHOW INDEX FROM tbl2_g;
  +--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  | Table  | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
  +--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  | tbl2_g |          1 | tbl2_g_idx1 |            1 | g           | A         |        NULL | NULL     | NULL   |      | SPATIAL    | available |               | YES     |
  +--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  1 row in set
  

• Create a table named tbl3_g and use the CREATE INDEX statement to create an index named tbl3_g_idx1 on this table.

  obclient [test]> CREATE TABLE tbl3_g(id INT,name VARCHAR(20),g GEOMETRY NOT NULL SRID 0);
  Query OK, 0 rows affected (0.060 sec)
  
  obclient [test]> CREATE INDEX tbl3_g_idx1 ON tbl3_g(g);
  Query OK, 0 rows affected (0.568 sec)
  
  obclient [test]> SHOW INDEX FROM tbl3_g;
  +--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  | Table  | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
  +--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  | tbl3_g |          1 | tbl3_g_idx1 |            1 | g           | A         |        NULL | NULL     | NULL   |      | SPATIAL    | available |               | YES     |
  +--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  1 row in set
  

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.

In the MySQL mode of OceanBase Database, the expressions of function-based indexes are restricted. The expressions of some system functions cannot be used as function-based indexes. For more information, see System functions supported by function-based indexes and System functions not supported by function-based indexes.

You can create a function-based index by using the following methods:

• Create a function-based index when you create a table by using the CREATE TABLE statement:

  CREATE TABLE table_name({column_name data_type,[column_name data_type,...]} [UNIQUE| SPATIAL] {INDEX|KEY} [index_name](expr));
  

• Create a function-based index when you modify a table by using the ALTER TABLE statement:

  ALTER TABLE table_name ADD [UNIQUE | SPATIAL] {INDEX | KEY} [index_name](expr);
  

• Create a function-based index by executing the CREATE INDEX statement:

  CREATE [UNIQUE | SPATIAL] INDEX index_name ON table_name (expr);
  

  In a MySQL tenant, index names must be unique in a table. You can use the SHOW INDEX statement to view the indexes on a table.

Parameters in the above statements are described as follows:

Examples of creating function-based indexes

1. Create a table named tbl1_f.

   CREATE TABLE tbl1_f(id INT,name VARCHAR(18),c_time DATE);
   

2. Create a function-based index named tbl1_f_idx1.

   CREATE INDEX tbl1_f_idx1 ON tbl1_f((YEAR(c_time)));
   

3. Query the created function-based index.

   SHOW INDEX FROM tbl1_f;
   

   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_f |          1 | tbl1_f_idx1 |            1 | SYS_NC19$   | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | year(`c_time`) |
   +--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+----------------+
   1 row in set
   

References

• Query indexes

• Drop an index

• Overview of spatial data types