This topic describes how to create an index.
Background
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, it can be a unique index, a non-unique index, or a SPATIAL index. A local unique index on a partitioned table must include a partitioning key of the table.
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 in OceanBase Database, make sure that the index name is unique within a table.
Create a normal index
You can create a normal index by using the CREATE TABLE, CREATE INDEX, or ALTER TABLE statement.
Use the CREATE TABLE statement to create an index
You can create an index when you create a table by executing the CREATE TABLE statement.
Syntax:
obclient> CREATE TABLE table_name(column_name data_type,[column_name data_type,...] [UNIQUE] INDEX index_name(column_name));
Parameters:
table_name: the name of the table to be created.column_name: the name of a column in the table.data_type: the data type of a 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_name: the name of the index to be created.
Example: Create a table named tbl1 and an index named tbl1_idx1 at the same time.
obclient> CREATE TABLE tbl1(id INT,name VARCHAR(18),date DATE,PRIMARY KEY (id),INDEX tbl1_idx1 (date));
Query OK, 0 rows affected
obclient> 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
Use the CREATE INDEX statement to create an index
You can use the CREATE INDEX statement to create an index for an existing table.
Syntax:
obclient> CREATE [UNIQUE] INDEX index_name ON table_name (column_name);
Parameters:
UNIQUE: indicates a unique index. This parameter is optional. You must specify this parameter when you create a unique index.index_name: the name of the index to be created.table_name: the name of the table for which the index is created.column_name: the name of a 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.
obclient> CREATE TABLE tbl2(id INT,name VARCHAR(20));
Query OK, 0 rows affected
obclient> CREATE INDEX tbl2_idx1 ON tbl2(id);
Query OK, 0 rows affected
obclient> 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
Use the ALTER TABLE statement to create an index
You can use the ALTER TABLE statement to create one or more indexes for an existing table.
Syntax:
obclient> ALTER TABLE table_name ADD [UNIQUE] INDEX|KEY index_name (column_name);
Parameters:
table_name: the name of the table for which the index is 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 theINDEXor theKEYkeyword in the statement.index_name: the name of the index to be created.column_name: the name of a 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.
obclient> CREATE TABLE tbl3(id INT,name VARCHAR(20));
Query OK, 0 rows affected
obclient> ALTER TABLE tbl3 ADD INDEX tbl3_idx1(id);
Query OK, 0 rows affected
obclient> 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 a SPATIAL index
OceanBase Database in MySQL mode 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
Note the following limits when you create a SPATIAL index:
You can create only a local SPATIAL index. Global SPATIAL indexes are not supported.
You must define the
SRIDattribute 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 theSRIDattribute 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 theSRID.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.
You can create a SPATIAL index only on columns that have the
NOT NULLconstraint.For a column without the
NOT NULLconstraint, you can use theALTER TABLEstatement to add theNOT NULLconstraint 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
Syntax:
obclient> CREATE TABLE table_name(column_g_name GEOMETRY NOT NULL SRID 4326,[column_name data_type,...] SPATIAL INDEX index_name(column_g_name));
Parameters:
table_name: the name of the table to be created.column_g_name,column_name: the name of a column in the table.SRID 4326: theSRIDof a column on which a SPATIAL index is to be created.data_type: the data type of a 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 Overview.index_name: the name of the index to be created.
Example: Create a table named tbl1_g and a SPATIAL index named tbl1_g_idx1 at the same time.
obclient> 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> 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
Use the CREATE INDEX statement to create a SPATIAL index
Syntax:
obclient> ALTER TABLE table_name ADD SPATIAL INDEX index_name(column_g_name);
Parameters:
table_name: the name of the table on which the index is to be created.index_name: the name of the index to be created.column_g_name: the name of a column to be indexed. If you specify multiple columns, separate the columns with commas (,).
Example: Create a table named tbl2_g and then create a SPATIAL index named tbl2_g_idx1.
obclient> CREATE TABLE tbl2_g(id INT,name VARCHAR(20),g GEOMETRY NOT NULL SRID 4326);
Query OK, 0 rows affected
obclient> CREATE INDEX tbl2_g_idx1 ON tbl2_g(g);
Query OK, 0 rows affected
obclient> 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
Use the ALTER TABLE statement to create a SPATIAL index
obclient> CREATE SPATIAL INDEX index_name ON table_name(column_g_name);
Parameters:
index_name: the name of the index to be created.table_name: the name of the table on which the index is to be created.column_g_name: the name of a column to be indexed. If you specify multiple columns, separate the columns with commas (,).
Example: Create a table named tbl3_g and then create a SPATIAL index named tbl3_g_idx1.
obclient> CREATE TABLE tbl3_g(id INT,name VARCHAR(20),g GEOMETRY NOT NULL SRID 4326);
Query OK, 0 rows affected
obclient> ALTER TABLE tbl3_g ADD SPATIAL INDEX tbl3_g_idx1(g);
Query OK, 0 rows affected
obclient> 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
More information
For more information about indexes of OceanBase Database in MySQL mode, see the following topics: