Create an index

This topic describes how to create an index.

Background information

OceanBase Database allows you to create local indexes, global indexes, unique indexes, non-unique indexes, spatial indexes, or function-based indexes on non-partitioned and partitioned tables. A local unique index on a partitioned table must include a partitioning key of the table. For more information about indexes, see About indexes.

This topic describes how to create an index on a non-partitioned table. For information about how to create an index on a partitioned table, see Create an index on a partitioned table.

Considerations

When you create an index on a table in OceanBase Database, make sure that the index name is unique within the table.

Create a normal index

You can use the CREATE TABLE, CREATE INDEX, or ALTER TABLE statement to create a normal index.

Use the CREATE TABLE statement to create a normal index

You can create an index when you create a table by using the CREATE TABLE statement.

The syntax is as follows:

CREATE TABLE table_name(column_name column_definition,[column_name column_definition,...] [UNIQUE] INDEX|KEY [index_name](column_name));

where

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

• column_name specifies the name of the index to be created.

• column_definition specifies the data type of the column in the table.

• UNIQUE indicates a unique index. This parameter is optional. You must specify this parameter when you create a unique index.

• INDEX|KEY indicates that you can use either the INDEX or the KEY keyword in the statement.

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

Example: Create a table named tbl1 and an index named tbl1_idx1.

CREATE TABLE tbl1(id INT,name VARCHAR(18),date DATE,PRIMARY KEY (id),INDEX tbl1_idx1 (date));

Use the CREATE INDEX statement to create a normal index

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

The syntax is as follows:

CREATE [UNIQUE] INDEX index_name ON table_name (column_name);

where

• UNIQUE indicates a unique index. This parameter is optional. You must specify this parameter when you create a unique index.

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

• table_name specifies the name of the table on which the index is to be created.

• column_name specifies the name of the column to be indexed. If you specify multiple columns, separate the columns with commas (,).

Example: Create a table named tbl2 table and then create an index named tbl2_idx1.

1. Create a table named tbl2.

   CREATE TABLE tbl2(id INT,name VARCHAR(20));
   

2. Create an index named tbl2_idx1 on the tbl2 table.

   CREATE INDEX tbl2_idx1 ON tbl2(id);
   

Use the ALTER TABLE statement to create a normal index

You can use the ALTER TABLE statement to create one or more indexes on an existing table.

The syntax is as follows:

ALTER TABLE table_name ADD [UNIQUE] INDEX|KEY [index_name](column_name);

where

• table_name specifies the name of the table on which the index is to be created.

• UNIQUE indicates a unique index. This parameter is optional. You must specify this parameter when you create a unique index.

• INDEX|KEY indicates that you can use either the INDEX or the KEY keyword in the statement.

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

• column_name specifies the name of the column to be indexed. If you specify multiple columns, separate the columns with commas (,).

Example: Create a table named tbl3 and then create an index named tbl3_idx1.

1. Create a table named tbl3.

   CREATE TABLE tbl3(id INT,name VARCHAR(20));
   

2. Create an index named tbl3_idx1 on the table tbl3.

   ALTER TABLE tbl3 ADD INDEX tbl3_idx1(id);
   

Create a spatial index

The MySQL mode of OceanBase Database supports spatial indexes. A spatial index describes the location of data stored on a medium and is used to improve the efficiency of data acquisition by the system.

Similar to creating a normal index, you can create a spatial index by using the CREATE TABLE, CREATE INDEX, or the ALTER TABLE statement. The differences are that you must specify the SPATIAL keyword in the statement and the columns in a spatial index must have a NOT NULL constraint.

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

Use the CREATE TABLE statement to create a spatial index

The syntax is as follows:

CREATE TABLE table_name(column_g_name GEOMETRY NOT NULL SRID 4326,[column_name column_definition,...] SPATIAL INDEX|KEY [index_name](column_g_name));

where

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

• column_g_name or column_name specifies the name of the column in the table.

• SRID 4326 specifies the SRID of the column on which a spatial index is to be created.

• column_definition specifies the data type of the column in the table. The column on which a spatial index is to be created must be of a spatial data type. For more information about the supported spatial data types, see Spatial data types.

• INDEX|KEY indicates that you can use either the INDEX or the KEY keyword in the statement.

• index_name specifies the name of the index to be created. If you do not specify a value for this parameter, the index name is the same as the column name by default.

Example: Create a table named tbl1_g and a spatial index named tbl1_g_idx1.

CREATE TABLE tbl1_g (id INT,name VARCHAR(18),g GEOMETRY NOT NULL SRID 4326,SPATIAL INDEX tbl1_g_idx1(g));

After you create an index, you can execute the SHOW INDEX statement to view its information.

SHOW INDEX FROM tbl1_g;

The query 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 |
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
| tbl1_g |          1 | tbl1_g_idx1 |            1 | g           | A         |        NULL | NULL     | NULL   |      | SPATIAL    | available |               | YES     |
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
1 row in set

Use the CREATE INDEX statement to create a spatial index

The syntax is as follows:

ALTER TABLE table_name ADD SPATIAL INDEX index_name(column_g_name);

where

• table_name specifies the name of the table on which the index is to be created.

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

• column_g_name specifies the name of the column to be indexed. If you specify multiple columns, separate the columns with commas (,). The column on which a spatial index is to be created must be of a spatial data type. For more information about the supported spatial data types, see Spatial data types.

Example: Create a table named tbl2_g and then create a spatial index named tbl2_g_idx1.

1. Create a table named tbl2_g.

   CREATE TABLE tbl2_g(id INT,name VARCHAR(20),g GEOMETRY NOT NULL SRID 4326);
   

2. Create a spatial index named tbl2_g_idx1 on the table.

   CREATE INDEX tbl2_g_idx1 ON tbl2_g(g);
   

3. View the index information.

   SHOW INDEX FROM tbl2_g;
   

   The query 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 |
   +--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   | tbl2_g |          1 | tbl2_g_idx1 |            1 | g           | A         |        NULL | NULL     | NULL   |      | SPATIAL    | available |               | YES     |
   +--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   1 row in set
   

Use the ALTER TABLE statement to create a spatial index

ALTER TABLE table_name ADD INDEX|KEY [index_name](column_g_name);

where

• table_name specifies the name of the table on which the index is to be created.

• INDEX|KEY indicates that you can use either the INDEX or the KEY keyword in the statement.

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

• column_g_name specifies the name of the column to be indexed. If you specify multiple columns, separate the columns with commas (,). The column on which a spatial index is to be created must be of a spatial data type. For more information about the supported spatial data types, see Spatial data types.

Example: Create a table named tbl3_g and then create a spatial index named tbl3_g_idx1.

1. Create a table named tbl3_g.

   CREATE TABLE tbl3_g(id INT,name VARCHAR(20),g GEOMETRY NOT NULL SRID 4326);
   

2. Create a spatial index on this table.

   ALTER TABLE tbl3_g ADD SPATIAL INDEX tbl3_g_idx1(g);
   

3. View the index information.

   SHOW INDEX FROM tbl3_g;
   

   The query 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 |
   +--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   | 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.

Assume that you have a table named sale and that the table has a column named date_col of the date type.

CREATE TABLE sale(date_col date, sales_col int, name_col varchar(10));

If you want to obtain the data of March, you can use the Month() function.

SELECT * FROM sale WHERE month(date_col) = 3;

In this scenario, the database must calculate the month(date_col) value for each row in the table and filter out rows that do not meet the condition month(date_col) = 3. If you need to frequently filter data by month and the month(date_col) values need to be calculated each time, the overhead is high. In this case, you can create a function-based index on the date_col column to store the month(date_col) values in an index table to accelerate queries.

CREATE INDEX idx1 ON sale((month(date_col)));

You can use the CREATE TABLE, CREATE INDEX, or ALTER TABLE statement to create a function-based index. A function-based index can be a unique or spatial index.

Limitations

The MySQL mode of OceanBase Database imposes limitations on the expressions of function-based indexes. Specifically, the expressions of some system functions cannot be used as function-based indexes. For more information, see System functions supported for function-based indexes and System functions not supported for function-based indexes.

Use the CREATE TABLE statement to create a function-based index

You can create a function-based index on a table when you create the table. The syntax is as follows:

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

where

• table_name specifies the name of the table on which the index is to be created.

• column_definition specifies the data type of the column in the table.

• [UNIQUE | SPATIAL] specifies an optional keyword. Here, UNIQUE indicates a unique index, and SPATIAL indicates a spatial index. When you create a unique index or a spatial index, you must add the corresponding keyword.

• INDEX|KEY indicates that you can use either the INDEX or the KEY keyword in the statement.

• index_name specifies the name of the function-based index to be created. This parameter is optional. If this parameter is not specified, the system automatically generates a name in the functional_index_xx format, in which xx is the index ID.

• expr specifies the expression of the function-based index. It cannot be a Boolean expression, such as c1=c1.

Example: Create a function-based index named tbl1_func_idx1.

CREATE TABLE tbl1_func (c1 int, c2 int, index tbl1_func_idx1 ((c1+1)), UNIQUE KEY ((c1+c2)));

Execute the following SHOW CREATE TABLE statement to view the table creation statement:

SHOW CREATE TABLE tbl1_func;

The query result is as follows:

+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                                                                                                        |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl1_func | CREATE TABLE `tbl1_func` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  UNIQUE KEY `functional_index` ((`c1` + `c2`)) BLOCK_SIZE 16384 LOCAL,
  KEY `tbl1_func_idx1` ((`c1` + 1)) BLOCK_SIZE 16384 LOCAL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

Use the CREATE INDEX statement to create a function-based index

After you create a table, you can create a function-based index on it. The syntax is as follows:

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

where

• [UNIQUE | SPATIAL] specifies an optional keyword. Here, UNIQUE indicates a unique index, and SPATIAL indicates a spatial index. When you create a unique index or a spatial index, you must add the corresponding keyword.

• index_name specifies the name of the function-based index to be created.

• table_name specifies the name of the table on which the index is to be created.

• expr specifies the expression of the function-based index. It cannot be a Boolean expression, such as c1=c1.

Example: Create a function-based index named tbl2_func_idx1 with an expression of c1+c2 < 1.

1. Create a table named tbl2_func.

   CREATE TABLE tbl2_func(c1 int, c2 int);
   

2. Create a function-based index named tbl2_func_idx1.

   CREATE INDEX tbl2_func_idx1 on tbl2_func ((c1+c2 < 1));
   

3. View the created function-based index.

   SHOW INDEX FROM tbl2_func;
   

   The query 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_func |          1 | tbl2_func_idx1 |            1 | SYS_NC18$   | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | ((`c1` + `c2`) < 1) |
   +-----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+---------------------+
   1 row in set
   

Use the ALTER TABLE statement to create a function-based index

The syntax is as follows:

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

where

• table_name specifies the name of the table on which the index is to be created.

• [UNIQUE | SPATIAL] specifies an optional keyword. Here, UNIQUE indicates a unique index, and SPATIAL indicates a spatial index. When you create a unique index or a spatial index, you must add the corresponding keyword.

• INDEX|KEY indicates that you can use either the INDEX or the KEY keyword in the statement.

• index_name specifies the name of the function-based index to be created. This parameter is optional. If you do not specify a value for this parameter, the system automatically generates an index name in the functional_index_xx format, in which xx is the index ID.

• expr specifies the expression of the function-based index. It cannot be a Boolean expression, such as c1=c1.

1. Create a table named tbl3_func.

   CREATE TABLE tbl3_func(c1 int, c2 int);
   

2. Create two function-based indexes on the table.

   ALTER TABLE tbl3_func ADD INDEX ((c1+1));
   

   ALTER TABLE tbl3_func ADD INDEX ((concat(c1,'a')));
   

3. View the index information.

   SHOW INDEX FROM tbl3_func;
   

   The query 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_func |          1 | functional_index   |            1 | SYS_NC18$   | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | (`c1` + 1)       |
   | tbl3_func |          1 | functional_index_2 |            1 | SYS_NC19$   | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | concat(`c1`,'a') |
   +-----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------------+
   2 rows in set
   

References

For more information about indexes of the MySQL mode of OceanBase Database, see the following topics:

• About indexes

• Create an index on a partitioned table

• Query indexes

• Drop an index