Create an index

This topic describes how to create an index on a non-partitioned table.

OceanBase Database allows you to create an index on both partitioned and non-partitioned tables. An index can be either a local index or a global index. In addition, the index can either be a unique index or a normal index. A local unique index on a partitioned table must include a partitioning key of the table.

OceanBase Database in MySQL mode supports creating SPATIAL indexes from V3.2.4.

For more information about normal indexes and SPATIAL indexes on partitioned tables, see Indexing on partitioned tables and SPATIAL indexes.

Syntax

Syntax for creating a normal index

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

  CREATE TABLE table_name(column_name data_type,[column_name data_type,...] INDEX index_name(column_name));
  

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

  ALTER TABLE table_name ADD INDEX index_name(column_name);
  

  This statement allows you to create multiple indexes at a time. You can use either the INDEX or the KEY keyword.

• To use the CREATE INDEX statement to create an index, use the following syntax:

  CREATE INDEX index_name ON table_name(column_name);
  

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

Syntax for creating a SPATIAL index

Note

You must specify SRID and NOT NULL for a column in a SPATIAL index that references a data type. Otherwise, the created index cannot take effect.

• 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);
  

  Note

  In OceanBase Database V3.2.4, you cannot use the ALTER TABLE statement to modify the NOT NULL and SRID attributes of a column. Therefore, you must define these attributes for the SPATIAL column when you create the table so that the SPATIAL index can take effect.````

• To use the CREATE INDEX statement to create a SPATIAL index, use the following syntax:

  CREATE SPATIAL INDEX index_name ON table_name(column_g_name);
  

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

Parameters

Examples

Create normal indexes

• Create a table named tbl1 and create an index named tbl1_idx1 on this table.

  obclient [test]> CREATE TABLE tbl1(id INT,name VARCHAR(18),date DATE,PRIMARY KEY (id),INDEX tbl1_idx1 (date));
  Query OK, 0 rows affected
  
  obclient [test]> SHOW INDEX FROM tbl1;
  +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  | Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
  +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  | tbl1  |          0 | PRIMARY   |            1 | id          | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
  | tbl1  |          1 | tbl1_idx1 |            1 | date        | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     |
  +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  2 rows in set
  

• Create a table named tbl2 and then use the ALTER TABLE statement to create an index named tbl2_idx1 on this table.

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

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

  obclient [test]> CREATE TABLE tbl3(id INT,name VARCHAR(20));
  Query OK, 0 rows affected
  
  obclient [test]> CREATE INDEX tbl3_idx1 ON tbl3(id);
  Query OK, 0 rows affected
  
  obclient [test]> SHOW INDEX FROM tbl3;
  +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
  +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  | tbl3  |          1 | tbl3_idx1 |            1 | id          | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     |
  +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  1 row in set
  

Create 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 4326,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 4326);
  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 4326);
  Query OK, 0 rows affected
  
  obclient [test]> CREATE INDEX tbl3_g_idx1 ON tbl3_g(g);
  Query OK, 0 rows affected
  
  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