You can create an index on one or more columns of a table to speed up SQL statements on the table. Well-designed indexes can reduce physical or logical I/O operations.
If you specify a primary key when you create a table, OceanBase Database automatically creates a UNIQUE index. Example:
obclient> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.01 sec)
obclient> CREATE TABLE t1(id bigint not null primary key, name varchar(50));
Query OK, 0 rows affected (0.05 sec)
obclient> show indexes from t1\G
*************************** 1. row ***************************
Table: t1
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment: available
Index_comment:
Visible: YES
1 row in set (0.01 sec)
Create an index
You can use the CREATE INDEX statement to create an index on a table. OceanBase enables you to create indexes on partitioned and non-partitioned tables. Indexes are classified into local and global indexes. An index can either be a UNIQUE or a normal index. A UNIQUE index on a partitioned table must contain the partition key of the partition.
SQL syntax for creating an index:
CREATE [UNIQUE] INDEX index_name ON table_name ( column_list ) [LOCAL | GLOBAL] [ PARTITION BY column_list PARTITIONS N ];
In a MySQL tenant, index names are unique across a table. You can use the SHOW INDEXES command to view the indexes.
In a MySQL tenant, you can create an index in another way. SQL syntax:
ALTER TABLE table_name
ADD|DROP INDEX|KEY index_name ( column_list ) ;
You can create multiple indexes at a time and use either INDEX or KEY as the keyword.
- Example: Creating an index on a partitioned table
obclient> create table t3(
id bigint not null primary KEY
, name varchar(50)
, gmt_create timestamp not null default current_timestamp
) partition by hash(id) partitions 8;
Query OK, 0 rows affected (0.14 sec)
obclient> alter table t3 add unique key t3_uk (name) local;
ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function
obclient> alter table t3
add unique key t3_uk (name, id) LOCAL
, add key t3_ind3(gmt_create) global;
Query OK, 0 rows affected (18.03 sec)
obclient> show indexes from t3;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
| t3 | 0 | PRIMARY | 1 | id | A | NULL | NULL | NULL | | BTREE | available | | YES |
| t3 | 0 | t3_uk | 1 | name | A | NULL | NULL | NULL | YES | BTREE | available | | YES |
| t3 | 0 | t3_uk | 2 | id | A | NULL | NULL | NULL | | BTREE | available | | YES |
| t3 | 1 | t3_ind3 | 1 | gmt_create | A | NULL | NULL | NULL | | BTREE | available | | YES |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
4 rows in set (0.01 sec)
Delete an index
Syntax for deleting an index:
ALTER TABLE table_name DROP key|index index_name ;
- Example: Deleting an index on a table
obclient> alter table t3 drop key t3_uk, drop key t3_ind3;
Query OK, 0 rows affected (0.07 sec)