This topic describes how to create an index.
Background information
OceanBase AI Database supports creating indexes on non-partitioned tables and partitioned tables. Indexes can be local or global, unique or non-unique, spatial or functional. For a unique index on a partitioned table, the local unique index must include the partitioning key of the table partition. For more information about indexes, see Indexes.
This section describes how to create indexes on non-partitioned tables. For information about creating indexes on partitioned tables, see Local index.
Considerations
When creating an index, note that in OceanBase AI Database, index names must be unique within the database.
Create a normal index
You can use the CREATE TABLE statement, CREATE INDEX statement, or ALTER TABLE statement to create a normal index.
Create a normal index by using the CREATE TABLE statement
You can use the CREATE TABLE statement to create an index for 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] INDEX|KEY [index_name](column_name));
The relevant parameters are described as follows:
table_name: The name of the table to be created.column_name: The column in the table.column_definition: Defines the data type corresponding to each column in the table.UNIQUE: Optional. Indicates a unique index. This keyword is required when creating a unique index.INDEX|KEY: Indicates that the index keyword can beINDEXorKEYin this statement.index_name: Optional. The name of the index to be created. If not specified, the default index name is the same as the specified column name.
The following example creates table tbl1 and index tbl1_idx1 at the same time:
CREATE TABLE tbl1(id INT,name VARCHAR(18),date DATE,PRIMARY KEY (id),INDEX tbl1_idx1 (date));
Create a normal index by using the CREATE INDEX statement
You can use the CREATE INDEX statement to create an index for an existing table.
The syntax is as follows:
CREATE [UNIQUE] INDEX index_name ON table_name (column_name);
The relevant parameters are described as follows:
UNIQUE: Optional. Indicates a unique index. This keyword is required when creating a unique index.index_name: The name of the index to be added.table_name: The name of the table for which the index is to be created.column_name: Specifies which columns to index. When specifying multiple columns, separate them with commas.
The following example creates table tbl2 and then creates index tbl2_idx1:
Create table
tbl2.CREATE TABLE tbl2(id INT,name VARCHAR(20));Create index
tbl2_idx1on tabletbl2.CREATE INDEX tbl2_idx1 ON tbl2(id);
Create a normal index by using the ALTER TABLE statement
You can use the ALTER TABLE statement to create an index for an existing table. This statement also supports adding multiple indexes at once.
The syntax is as follows:
ALTER TABLE table_name ADD [UNIQUE] INDEX|KEY [index_name](column_name);
The relevant parameters are described as follows:
table_name: The name of the table for which you want to create an index.UNIQUE: Optional. Indicates a unique index. Add this keyword when creating a unique index.INDEX|KEY: Indicates that you can use eitherINDEXorKEYas the index keyword in this statement.index_name: Optional. The name of the index to be created. If not specified, the default index name is the same as the specified column name.column_name: Specifies the columns to be indexed. When specifying multiple columns, separate them with commas.
After creating table tbl3, the following example shows how to create index tbl3_idx1:
Create table
tbl3.CREATE TABLE tbl3(id INT,name VARCHAR(20));Create index
tbl3_idx1for tabletbl3.ALTER TABLE tbl3 ADD INDEX tbl3_idx1(id);
Create a spatial index
OceanBase AI Database supports creating spatial indexes. A spatial index describes the location information of data stored on media and can improve the system's efficiency in retrieving data.
The syntax for creating a spatial index is essentially the same as that for creating a regular index, using the CREATE TABLE statement, CREATE INDEX statement, or ALTER TABLE statement. The difference is that a spatial index must use the SPATIAL keyword, and the column in the spatial index must be defined as NOT NULL.
Limitations on using spatial indexes
Before creating a spatial index, note the following limitations:
Spatial indexes support only local indexes; global indexes are not supported.
The column used to create the spatial index must be defined with the
SRIDattribute. Otherwise, the spatial index created on that column will not take effect in subsequent queries. This is because when using a spatial index for comparison, the coordinate system corresponding to theSRIDin the column definition is required. Spatial indexes are built using the Minimum Bounding Rectangle (MBR) of geometric objects, and the comparison of MBRs also depends on theSRID.For more information about SRIDs, see Spatial Reference System (SRS).
You can create a spatial index only on columns with spatial data types. For information about the spatial data types supported by OceanBase AI Database, see Overview of spatial data types.
The column attribute for the column used to create the spatial index must be
NOT NULL.If it is not
NOT NULL, you can also first modify the column attribute toNOT NULLusing theALTER TABLEstatement, and then add the spatial index. For specific operations on modifying column attributes, see Define column constraints.
Create a spatial index using the CREATE TABLE statement
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));
The relevant parameters are described as follows:
table_name: Specifies the name of the table to be created.column_g_name,column_name: Define the column names of the table.SRID 4326: Explicitly specifies theSRIDof the spatial column.column_definition: Defines the data type corresponding to each column in the table. The column type for the column where the spatial index is to be added must be a spatial data type. For information about the spatial data types supported by OceanBase AI Database, see Overview of spatial data types.INDEX|KEY: Indicates that eitherINDEXorKEYcan be used as the index keyword in this statement.index_name: Specifies the name of the index to be added. If not specified, the default index name is the same as the specified column name.
The following example creates the tbl1_g table and adds the spatial index 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 creation, you can use the SHOW INDEX statement to view index 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
Create a spatial index by using the CREATE INDEX statement
The syntax is as follows:
CREATE SPATIAL INDEX index_name ON table_name(column_g_name);
The related parameters are described as follows:
table_name: Specifies the name of the table for which you want to create an index.index_name: Specifies the name of the index to be added.column_g_name: Specifies the columns to be indexed. When specifying multiple columns, separate them with commas. The column types of the columns for which you want to add a spatial index must be spatial data types. For information about the spatial data types supported by OceanBase AI Database, see Overview of spatial data types.
The following example shows how to create the tbl2_g table and then create the spatial index tbl2_g_idx1:
Create the
tbl2_gtable.CREATE TABLE tbl2_g(id INT,name VARCHAR(20),g GEOMETRY NOT NULL SRID 4326);Create the spatial index
tbl2_g_idx1for the table.CREATE SPATIAL INDEX tbl2_g_idx1 ON tbl2_g(g);After successful creation, 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
Create a spatial index by using the ALTER TABLE statement
ALTER TABLE table_name ADD SPATIAL INDEX|KEY [index_name](column_g_name);
The related parameters are described as follows:
table_name: Specifies the name of the table for which you want to create an index.INDEX|KEY: Indicates that you can use eitherINDEXorKEYas the index keyword in this statement.index_name: Optional. Specifies the name of the index to be added. If not specified, the default index name is the same as the specified column name.column_g_name: Specifies the columns to be indexed. When specifying multiple columns, separate them with commas. The column types of the columns for which you want to add a spatial index must be spatial data types. For information about the spatial data types supported by OceanBase AI Database, see Overview of spatial data types.
The following example shows how to create the table tbl3_g and then create a spatial index tbl3_g_idx1 on it:
Create the table
tbl3_g.CREATE TABLE tbl3_g(id INT,name VARCHAR(20),g GEOMETRY NOT NULL SRID 4326);Create a spatial index on the table.
ALTER TABLE tbl3_g ADD SPATIAL INDEX tbl3_g_idx1(g);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 an index created on the result of a calculation based on one or more columns in a table. Function-based indexing is an optimization technique that allows you to quickly locate matching function values during queries, thereby avoiding repeated calculations and improving query efficiency.
Assume you have a table named sale with a column named date_col of data type date:
CREATE TABLE sale(date_col date, sales_col int, name_col varchar(10));
If you need to retrieve data for March, you can use the Month() function to query the data:
SELECT * FROM sale WHERE month(date_col) = 3;
In this scenario, the database needs to calculate month(date_col) for each row in the table and filter out rows that do not meet the condition month(date_col) = 3. If you frequently need to filter data using month information, recalculating month(date_col) every time would incur significant overhead. Therefore, you can create a function-based index on date_col to store month(date_col) in the index table, accelerating queries.
CREATE INDEX idx1 ON sale((month(date_col)));
You can use the CREATE TABLE statement, CREATE INDEX statement, or ALTER TABLE statement to create a function-based index. A function-based index can also be a unique index or a spatial index.
Limitations on function-based indexes
In OceanBase AI Database, expressions for function-based indexes are restricted. Expressions that use certain system functions are prohibited. For details about the specific functions, see List of system functions supported for function-based indexes and List of system functions not supported for function-based indexes.
Create a function-based index using the CREATE TABLE statement
You can create a function-based index for 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));
The relevant parameters are described as follows:
table_name: Specifies the name of the table for which you want to create the index.column_definition: Defines the data types of the columns in the table.[UNIQUE | SPATIAL]: Optional.UNIQUEindicates a unique index;SPATIALindicates a spatial index. Add the corresponding keyword when creating a unique index or a spatial index.INDEX|KEY: Indicates that you can use eitherINDEXorKEYfor the index keyword in this statement.index_name: Optional. The name of the function-based index to be created. If not specified, the system automatically generates a name in the formatfunctional_index_xx, wherexxis the index number.expr: A valid function-based index expression, which can be a boolean expression, such asc1=c1.
The following example shows how to 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)));
After successful creation, you can use the SHOW CREATE TABLE statement to view the table creation statement, which is as follows:
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
Create a function-based index by using the CREATE INDEX statement
After a table is created, you can create a function-based index on it. The syntax is as follows:
CREATE [UNIQUE | SPATIAL] INDEX index_name ON table_name (expr);
The relevant parameters are described as follows:
[UNIQUE | SPATIAL]: Optional.UNIQUEindicates a unique index;SPATIALindicates a spatial index. Add the corresponding keyword when creating a unique or spatial index.index_name: The name of the function-based index to be created.table_name: The table on which to create the index.expr: A valid function-based index expression, which can be a Boolean expression, such asc1=c1.
Create a function-based index named tbl2_func_idx1 on the table with the definition c1+c2 < 1, as shown in the following example:
Create the table
tbl2_func.CREATE TABLE tbl2_func(c1 int, c2 int);Create the function-based index
tbl2_func_idx1.CREATE INDEX tbl2_func_idx1 on tbl2_func ((c1+c2 < 1));After successful creation, you can view the created function-based index.
SHOW INDEX FROM tbl2_func;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 | +-----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+---------------------+ | tbl2_func | 1 | tbl2_func_idx1 | 1 | SYS_NC18$ | A | NULL | NULL | NULL | YES | BTREE | available | | YES | ((`c1` + `c2`) < 1) | +-----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+---------------------+ 1 row in set
Create a function-based index by using the ALTER TABLE statement
The syntax is as follows:
ALTER TABLE table_name ADD [UNIQUE | SPATIAL] {INDEX | KEY} [index_name](expr);
The relevant parameters are described as follows:
table_name: The table on which to create the index.[UNIQUE | SPATIAL]: Optional.UNIQUEindicates a unique index;SPATIALindicates a spatial index. Add the corresponding keyword when creating a unique or spatial index.INDEX|KEY: Indicates that in this statement, the index keyword can be used as eitherINDEXorKEY.index_name: Optional. The name of the function index to be created. If not specified, the system will automatically generate a name in the formatfunctional_indexfollowed by a number.expr: A valid function index expression, which can be a boolean expression, for example,c1=c1.
Create table
tbl3_func.CREATE TABLE tbl3_func(c1 int, c2 int);Create two function indexes for the table.
ALTER TABLE tbl3_func ADD INDEX ((c1+1));ALTER TABLE tbl3_func ADD INDEX ((concat(c1,'a')));View 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
Create a columnstore index
You can use the CREATE TABLE statement, CREATE INDEX statement, or ALTER TABLE statement to create a columnstore index.
Create a columnstore index by using the CREATE TABLE statement
You can use the CREATE TABLE statement to create a columnstore index for 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] INDEX|KEY [index_name](column_name) [STORING(column_name, ...)] WITH COLUMN GROUP([all columns, ]each column));
The related parameters are described as follows:
table_name: The name of the table to be created.column_name: The name of a column in the table.column_definition: The data type of each column in the table.UNIQUE: Optional. Indicates a unique index. This keyword is required when creating a unique index.INDEX|KEY: Indicates that the index keyword can beINDEXorKEYin this statement.index_name: Optional. The name of the index to be created. If not specified, the default index name is the same as the specified column name.STORING(column_name, ...): Optional. Specifies redundant columns on the index table. Even if thecolumn_namecolumn is not an index field, it will be redundantly stored in columnstore format.WITH COLUMN GROUP([all columns, ]each column): Specifies the columnstore attribute of the index.WITH COLUMN GROUP(all columns, each column): Specifies to create a hybrid rowstore-columnstore index.WITH COLUMN GROUP(all columns): Specifies to create a rowstore index.WITH COLUMN GROUP(each column): Specifies to create a columnstore index.
Examples:
Create table
tbl4and create a hybrid rowstore-columnstore indexidx1_tbl4_cgat the same time.CREATE TABLE tbl4(id INT, name VARCHAR(18), date DATE, PRIMARY KEY (id), INDEX idx1_tbl4_cg (date) WITH COLUMN GROUP(all columns, each column));Create table
tbl5, create a columnstore indexidx1_tbl5_cgat the same time, and store redundant data of theidcolumn on the index table.CREATE TABLE tbl5(id INT, name VARCHAR(18), date DATE, INDEX idx1_tbl5_cg (date) STORING(id) WITH COLUMN GROUP(each column));
Create a columnstore index by using the CREATE INDEX statement
You can use the CREATE INDEX statement to create a columnstore index for an existing table.
The syntax is as follows:
CREATE [UNIQUE] INDEX index_name ON table_name (column_name) [STORING(column_name, ...)] WITH COLUMN GROUP([all columns, ]each column);
The relevant parameters are described as follows:
UNIQUE: Optional. Indicates a unique index. This keyword is required when creating a unique index.index_name: Specifies the name of the index to be created.table_name: Specifies the name of the table on which to create the index.column_name: Specifies the columns to be indexed. When specifying multiple columns, separate them with commas.STORING(column_name, ...): Optional. Specifies redundant columns on the index table. Even if acolumn_namecolumn is not an index field, it will be redundantly stored in columnar format.WITH COLUMN GROUP([all columns, ]each column): Specifies the columnar storage attributes of the index.WITH COLUMN GROUP(all columns, each column): Specifies to create an index that is redundant for both row and column storage.WITH COLUMN GROUP(all columns): Specifies to create a row-based index.WITH COLUMN GROUP(each column): Specifies to create a columnar index.
Example:
After creating the tbl6 table, create a columnar index named idx1_tbl6_cg.
Create the
tbl6table.CREATE TABLE tbl6(id INT, name VARCHAR(20), age INT);Create a columnar index named
idx1_tbl6_cgon thetbl6table, and store redundant data for theagecolumn on the index table.CREATE INDEX idx1_tbl6_cg ON tbl6(id) STORING(age) WITH COLUMN GROUP(each column);
Create a columnar index using the ALTER TABLE statement
You can use the ALTER TABLE statement to create a columnar index for an existing table.
The syntax is as follows:
ALTER TABLE table_name ADD [UNIQUE] INDEX|KEY [index_name](column_name) [STORING(column_name, ...)] WITH COLUMN GROUP([all columns, ]each column);
The relevant parameters are described as follows:
table_name: Specifies the name of the table on which to create the index.UNIQUE: Optional. Indicates a unique index. This keyword is required when creating a unique index.INDEX|KEY: Indicates that you can use eitherINDEXorKEYas the index keyword in this statement.index_name: Optional. Specifies the name of the index to be created. If not specified, the default index name is the same as the specified column name.column_name: Specifies which columns to index. When specifying multiple columns, separate them with commas.STORING(column_name, ...): Optional. Specifies redundant columns on the index table. Even if acolumn_namecolumn is not an index field, it will be redundantly stored in columnar format.WITH COLUMN GROUP([all columns, ]each column): Specifies the columnar attributes of the index.WITH COLUMN GROUP(all columns, each column): Specifies to create a hybrid row-column storage index.WITH COLUMN GROUP(all columns): Specifies to create a row storage index.WITH COLUMN GROUP(each column): Specifies to create a columnar index.
Example:
After creating table tbl7, create a columnar index idx1_tbl6_cg.
Create table
tbl7.CREATE TABLE tbl7(id INT, name VARCHAR(20), age INT);Create a columnar index
idx1_tbl7_cgfor tabletbl7and store redundant data for theagecolumn on the index table.ALTER TABLE tbl7 ADD INDEX idx1_tbl7_cg(id) STORING(age) WITH COLUMN GROUP(each column);
Create a multi-valued index
OceanBase AI Database supports creating multi-valued indexes in advance when you create a table (CREATE TABLE). Currently, you cannot add a multi-valued index after the table is created, but you can drop an existing index using the ALTER TABLE statement. Complex DML operations are supported on primary tables that have multi-valued indexes created.
Limitations on multi-valued indexes
Each multi-valued index allows only one multi-valued key part. However, the
CAST(... AS ... ARRAY)expression can reference multiple arrays in a JSON document, as shown below:CAST(data->'$.arr[*][*]' AS UNSIGNED ARRAY)In this case, all values that match the JSON expression are stored in the index as a single flat array.
A multi-valued index cannot be a covering index.
The only type of expression allowed in the multi-valued key part is a JSON expression. The expression does not have to reference existing elements in the JSON documents inserted into the index columns, but it must be syntactically valid.
Because index records for the same clustered index are scattered across multiple entries in a multi-valued index, range scans or index-only scans are not supported.
Multi-valued indexes are not allowed in foreign key specifications.
You cannot define an index prefix for a multi-valued index.
You cannot define a multi-valued index on data of the Binary type.
For information about the support of offline DDL for multi-valued indexes, see Online DDL and Offline DDL Operations.
Create a multi-valued index using the CREATE TABLE statement
The simplified syntax for creating a multi-valued index using the CREATE TABLE statement is as follows:
Create a multi-valued index:
CREATE TABLE table_name(column_name column_definition, [column_name column_definition, ...],
json_column_name JSON,
[UNIQUE] INDEX idx_json_name((CAST(json_column_name->'$.json_field_name' AS UNSIGNED ARRAY)))
);
Create a composite multi-valued index:
CREATE TABLE table_name(column_name column_definition, [column_name column_definition, ...],
json_column_name JSON,
INDEX idx_json_name(column_name, [column_name, ...] (CAST(json_column_name->'$.json_field_name' AS CHAR(n) ARRAY)))
);
The related parameters are described as follows:
table_name: Specifies the name of the table to be created.column_name: Specifies the name of a column with a data type other thanJSONin the table.column_definition: Defines the data type corresponding to each column in the table.json_column_name: Specifies the name of theJSONdata type column in the table.UNIQUE: Optional. Indicates that a unique multi-valued index is to be created. In a unique multi-valued index, the index keys must not be repeated.Notice
A multi-valued index can be defined as a unique index. If defined as a unique index, attempting to insert a value already present in the multi-valued index will return a duplicate key error. If duplicate values already exist, attempting to add a unique multi-valued index will fail.
idx_json_name: Specifies the name of the multi-valued index to be created.json_field_name: The name of the field in the JSON column for which you want to create an index.
For more information about table creation syntax, see CREATE TABLE.
Example of creating a multi-valued index:
Create the
json_tbl1table and theidx1_json_tbl1multi-valued index.CREATE TABLE json_tbl1(col1 INT, col2 VARCHAR(18), col3 JSON, INDEX idx1_json_tbl1((CAST(col3->'$.ipcode' AS UNSIGNED ARRAY))) );Use the following statement to insert test data into the
json_tbl1table.INSERT INTO json_tbl1 VALUES(1, 'a1', '{"user":"Jk001","user_id":37,"ipcode":[94582,94536]}'), (2, 'a2', '{"user":"Jk002","user_id":22,"ipcode":[94568,94507,94582]}'), (3, 'a3', '{"user":"Jk003","user_id":31,"ipcode":[94477,94507]}'), (4, 'a4', '{"user":"Jk004","user_id":72,"ipcode":[94536]}'), (5, 'a5', '{"user":"Jk005","user_id":56,"ipcode":[94507,94582]}');
Example of creating a multi-valued index:
Create the json_tbl2 table and the composite multi-valued index idx1_json_tbl2.
CREATE TABLE json_tbl2(col1 INT, col2 VARCHAR(18), col3 JSON,
INDEX idx1_json_tbl2(col1, (CAST(col3->'$.ipcode' AS UNSIGNED ARRAY)))
);
Support for complex DML
For a primary table that contains a multi-valued index, complex DML operations are supported. Examples:
INSERT INTO ON DUPLICATE KEYCREATE TABLE customers(id INT PRIMARY KEY, uid INT, j JSON); INSERT INTO customers VALUES (10, 21, '{"user":"Jack","user_id":37,"zipcode":[94582,94536]}'); CREATE INDEX zips ON customers((CAST(j->'$.zipcode' AS UNSIGNED ARRAY))); INSERT INTO customers VALUES (10, 22, '{"user":"Jack","user_id":37,"zipcode":[24582,34536]}') ON DUPLICATE KEY UPDATE j = '{"user":"Jack","user_id":37,"zipcode":[34582,44536]}';REPLACE INTOCREATE TABLE test_array(id int,a varchar(50),j json,INDEX index_1( (CAST(j->'$.key' AS UNSIGNED ARRAY))) ); INSERT INTO test_array VALUES(1,'test','{"key":[1,2,3,4]}'); REPLACE INTO test_array VALUES(1,'test','{"key":[8,9,13,43]}');Multi-table
UPDATEandDELETEPrepare the sample tables as follows:
CREATE TABLE table_1(a int PRIMARY KEY, b json, INDEX index_1( (CAST(b->'$.key' AS UNSIGNED ARRAY))) ); CREATE TABLE table_2(a int PRIMARY KEY, b json); INSERT INTO table_1 values(1, '{"key":[1,2,3,4]}'); INSERT INTO table_2 values(1, '{"key":[1,2,3,4]}');Example of
UPDATE:UPDATE table_1 JOIN table_2 ON table_1.a = table_2.a SET table_1.b = '{"key":[11,12,13,14]}', table_2.b = '{"key":[21,22,23,24]}'; UPDATE table_1 JOIN table_2 ON table_1.a = table_2.a SET table_1.b = '{"key":[31,32,33,34]}'; UPDATE table_1 JOIN table_2 ON table_1.a = table_2.a SET table_2.b = table_1.b;Example of
DELETE:DELETE table_1, table_2 FROM table_1 JOIN table_2 ON table_1.a = table_2.a; DELETE table_1 FROM table_1 JOIN table_2 ON table_1.a = table_2.a; DELETE table_1 FROM table_1 JOIN table_2 ON table_1.a = table_2.a;Updatable views
CREATE TABLE test(id int,a varchar(50),j json,INDEX index_1( (CAST(j->'$.key' AS UNSIGNED ARRAY))) ); INSERT INTO test values(1,'test','{"key":[1,2,3,4]}'); CREATE VIEW vtest AS SELECT * FROM test; UPDATE vtest SET j='{"key":[1,2]}';
Add a multi-value index afterward
To add a multi-value index afterward, you must first enable the _enable_add_fulltext_index_to_existing_table parameter.
ALTER SYSTEM SET _enable_add_fulltext_index_to_existing_table = true;
Multi-value indexes added afterward can be classified as follows:
Category |
Description |
|---|---|
| Multi-valued index | A multi-valued index created by using the ADD INDEX or ALTER TABLE statement. |
| Composite multi-valued index | A non-unique composite multi-valued index created by using the ADD INDEX or ALTER TABLE statement. A composite multi-valued index allows only one column to be a multi-valued index, while the other columns are regular columns. |
| Unique multi-valued index | A unique multi-valued index created by using the ADD INDEX or ALTER TABLE statement. |
| Unique composite multi-valued index | A unique composite multi-valued index created by using the ADD INDEX or ALTER TABLE statement. A composite multi-valued index allows only one column to be a multi-valued index, while the other columns are regular columns. |
Examples are as follows:
Create table
json_tbl1and create a non-unique multi-value indexidx1_json_tbl1at the same time.CREATE TABLE json_tbl1(id INT PRIMARY KEY, uid INT, j JSON); INSERT INTO json_tbl1 VALUES (10, 21, '{"user":"Jack","user_id":37,"zipcode":[94582,94536]}'), (11, 22, '{"user":"Jill","user_id":22,"zipcode":[94568,94507,94582]}'), (12, 23, '{"user":"Bob","user_id":31,"zipcode":[94477,94507]}'), (13, 24, '{"user":"Mary","user_id":72,"zipcode":[94536]}'), (14, 25, '{"user":"Ted","user_id":56,"zipcode":[94507,94582]}'); CREATE INDEX idx1_json_tbl1 ON json_tbl1((CAST(j->'$.zipcode' AS UNSIGNED ARRAY)));Create a unique multi-value index
idx1_json_tbl2.CREATE TABLE json_tbl2(id INT PRIMARY KEY, uid INT, j JSON); INSERT INTO json_tbl2 VALUES (10, 21, '{"user":"Jack","user_id":37,"zipcode":[94507,94536]}'), (11, 22, '{"user":"Jill","user_id":22,"zipcode":[94568,94582]}'), (12, 23, '{"user":"Bob","user_id":31,"zipcode":[94477]}'); CREATE UNIQUE INDEX idx1_json_tbl2 ON json_tbl2((CAST(j->'$.zipcode' AS UNSIGNED ARRAY)));Create a composite multi-value index
idx1_json_tbl3.CREATE TABLE json_tbl3 ( id BIGINT NOT NULL PRIMARY KEY, c1 BIGINT NOT NULL, modified BIGINT NOT NULL, custinfo JSON ); INSERT INTO json_tbl3 VALUES (10, 21, 101, '{"user":"Jack","user_id":37,"zipcode":[94582,94536]}'), (11, 22, 102, '{"user":"Jill","user_id":22,"zipcode":[94568,94507,94582]}'), (12, 23, 103, '{"user":"Bob","user_id":31,"zipcode":[94477,94507]}'), (13, 24, 104, '{"user":"Mary","user_id":72,"zipcode":[94536]}'), (14, 25, 105, '{"user":"Ted","user_id":56,"zipcode":[94507,94582]}'); CREATE INDEX idx1_json_tbl3 ON json_tbl3 (c1, modified, (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );Create a unique composite multi-value index
idx1_json_tbl4.CREATE TABLE json_tbl4 ( id BIGINT NOT NULL PRIMARY KEY, c1 BIGINT NOT NULL, modified BIGINT NOT NULL, custinfo JSON ); INSERT INTO json_tbl4 VALUES (10, 21, 101, '{"user":"Jack","user_id":37,"zipcode":[94582,94536]}'), (11, 22, 102, '{"user":"Jill","user_id":22,"zipcode":[94568,94507,94582]}'), (12, 23, 103, '{"user":"Bob","user_id":31,"zipcode":[94477,94507]}'), (13, 24, 104, '{"user":"Mary","user_id":72,"zipcode":[94536]}'), (14, 25, 105, '{"user":"Ted","user_id":56,"zipcode":[94507,94582]}'); CREATE UNIQUE INDEX idx1_json_tbl4 ON json_tbl4 (c1, modified, (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );
Use multi-value indexes in queries
The optimizer uses multi-value indexes for queries when the following functions are specified in the WHERE clause:
Example:
Example query using
MEMBER OF().SELECT * FROM json_tbl1 WHERE 94507 MEMBER OF(col3->'$.ipcode');The result is as follows:
+------+------+-------------------------------------------------------------------+ | col1 | col2 | col3 | +------+------+-------------------------------------------------------------------+ | 2 | a2 | {"user": "Jk002", "ipcode": [94568, 94507, 94582], "user_id": 22} | | 3 | a3 | {"user": "Jk003", "ipcode": [94477, 94507], "user_id": 31} | | 5 | a5 | {"user": "Jk005", "ipcode": [94507, 94582], "user_id": 56} | +------+------+-------------------------------------------------------------------+ 3 rows in setExecute a query on table
json_tbl1to filter out rows where theipcodefield in the JSON data of columncol3contains the value 94507. Use theEXPLAINcommand to view the query execution plan.EXPLAIN SELECT * FROM json_tbl1 WHERE 94507 MEMBER OF(col3->'$.ipcode');The query result is as follows:
+-------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +-------------------------------------------------------------------------------------------------------------------------------------------------------+ | ===================================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | --------------------------------------------------------------------- | | |0 |TABLE RANGE SCAN|json_tbl1(idx1_json_tbl1)|3 |13 | | | ===================================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([json_tbl1.col1], [json_tbl1.col2], [json_tbl1.col3]), filter(nil) | | access([json_tbl1.__pk_increment], [json_tbl1.col3], [json_tbl1.col1], [json_tbl1.col2]), partitions(p0) | | is_index_back=true, is_global_index=false, | | range_key([json_tbl1.SYS_NC_mvi_19], [json_tbl1.__pk_increment], [json_tbl1.__doc_id_1715756608129539]), range(94507,MIN,MIN ; 94507,MAX,MAX), | | range_cond([JSON_MEMBER_OF(94507, json_tbl1.SYS_NC_mvi_19)]) | +-------------------------------------------------------------------------------------------------------------------------------------------------------+ 12 rows in setExample of using
JSON_CONTAINS().SELECT * FROM json_tbl1 WHERE JSON_CONTAINS(col3->'$.ipcode', CAST('[94507,94582]' AS JSON));The query result is as follows:
+------+------+-------------------------------------------------------------------+ | col1 | col2 | col3 | +------+------+-------------------------------------------------------------------+ | 2 | a2 | {"user": "Jk002", "ipcode": [94568, 94507, 94582], "user_id": 22} | | 5 | a5 | {"user": "Jk005", "ipcode": [94507, 94582], "user_id": 56} | +------+------+-------------------------------------------------------------------+ 2 rows in setExample of using
JSON_OVERLAPS().SELECT * FROM json_tbl1 WHERE JSON_OVERLAPS(col3->'$.ipcode', CAST('[94507,94582]' AS JSON));The query result is as follows:
+------+------+-------------------------------------------------------------------+ | col1 | col2 | col3 | +------+------+-------------------------------------------------------------------+ | 1 | a1 | {"user": "Jk001", "ipcode": [94582, 94536], "user_id": 37} | | 2 | a2 | {"user": "Jk002", "ipcode": [94568, 94507, 94582], "user_id": 22} | | 3 | a3 | {"user": "Jk003", "ipcode": [94477, 94507], "user_id": 31} | | 5 | a5 | {"user": "Jk005", "ipcode": [94507, 94582], "user_id": 56} | +------+------+-------------------------------------------------------------------+ 4 rows in set
Create a full-text index
In the current version of OceanBase AI Database, you can use the CREATE TABLE statement, CREATE FULLTEXT INDEX statement, or ALTER TABLE statement to create a full-text index.
For more information about full-text indexes, see Full-text search.
For information about querying data using a full-text index, see Full-text query.
Limitations and considerations for full-text indexes
You can create a full-text index on a non-partitioned table or a partitioned table with or without a primary key. The limitations are as follows:
- Full-text indexes are supported only on columns of the
CHAR,VARCHAR, andTEXTtypes. - The current version supports only creating local (
LOCAL) full-text indexes. - You cannot specify the
UNIQUEkeyword when creating a full-text index. - To create a full-text index on multiple columns, ensure these columns have the same character set.
- For information about the support of offline DDL operations for full-text indexes, see Online DDL and Offline DDL Operations.
Create a full-text index by using the CREATE TABLE statement
You can create a full-text index for a table when you create the table using the CREATE TABLE statement. The simplified syntax is as follows:
CREATE TABLE table_name(column_name column_definition,[column_name column_definition,...]
FULLTEXT [INDEX | KEY] [index_name](column_name)
[WITH PARSER tokenizer_option]
[PARSER_PROPERTIES[=](parser_properties_list)]
[LOCAL]);
tokenizer_option:
SPACE
| NGRAM
| BENG
| IK
| NGRAM2
| analyzer
parser_properties_list:
parser_properties, [parser_properties]
parser_properties:
min_token_size = int_value
| max_token_size = int_value
| ngram_token_size = int_value
| ik_mode = 'char_value'
| min_ngram_size = int_value
| max_ngram_size = int_value
| analysis = json_string
For more information about the CREATE TABLE syntax, see CREATE TABLE.
The relevant parameters are described as follows:
table_name: The name of the table to be created.column_name: The column in the table.column_definition: Defines the data type corresponding to each column in the table.FULLTEXT: Specifies to create a full-text index.Notice
The current version supports only creating local full-text indexes.
INDEX | KEY: Indicates that you can use eitherINDEXorKEYas the index keyword in this statement.index_name: Optional. Specifies the name of the index to be created. If not specified, the default index name is the same as the specified column name.WITH PARSER tokenizer_option: Optional. Specifies the tokenizer for the full-text index. Valid values:SPACE: The default value. Indicates to tokenize by spaces. The following attributes can be specified:AttributeValue rangemin_token_size [1, 16] max_token_size [10, 84] NGRAM: Indicates to tokenize based on N-grams (for Chinese). The following attributes can be specified:AttributeValue rangengram_token_size [1, 10] NGRAM2: Indicates that the text will be split into consecutive characters within the range ofmin_ngram_sizetomax_ngram_size.AttributeValue rangemin_ngram_size [1, 16] max_ngram_size [1, 16] BENG: A word segmentation method based on Beng (Basic English). The following attributes can be specified:AttributeValue rangemin_token_size [1, 16] max_token_size [10, 84] IK: Indicates a word segmentation method based on IK (Chinese). Currently, only theutf-8character set is supported. The following attributes can be specified:AttributeValue rangeik_mode smartmax_word
You can use the TOKENIZE function to view the word segmentation result of a text using the specified tokenizer and JSON-formatted parameters.
PARSER_PROPERTIES[=](parser_properties_list): Optional. Specifies the properties of the tokenizer. Valid values are as follows:min_token_size: Indicates the minimum token length. Default value is 3. Value range is 1 to 16.max_token_size: Indicates the maximum token length. Default value is 84. Value range is 10 to 84.ngram_token_size: Indicates the segmentation length forNGRAM. This parameter is valid only for theNGRAMtokenizer. Default value is 2. Value range is 1 to 10.ik_mode: Indicates the segmentation mode for theIKtokenizer. Valid values are as follows:smart: Default value. Indicates that dictionary words will be used to improve segmentation accuracy. Dictionary word boundaries will be prioritized, which may reduce unnecessary expansions.max_word: Indicates that dictionary-defined words will be recognized, but it will not affect the maximization of segmentation expansions. Even if a word is defined in the dictionary, themax_wordmode will still attempt to split the text into more words.
LOCAL: Optional. Specifies to create a local index.
Example:
Create table
tbl1and a full-text indexfull_idx1_tbl1at the same time.CREATE TABLE tbl1(id INT, name VARCHAR(18), date DATE, PRIMARY KEY (id), FULLTEXT INDEX full_idx1_tbl1(name));Create table
tbl2, a full-text indexfull_idx1_tbl2, specify theNGRAMtokenizer for the full-text index, and set the tokenizer properties usingPARSER_PROPERTIES.CREATE TABLE tbl2(id INT, name VARCHAR(18), doc TEXT, FULLTEXT INDEX full_idx1_tbl2(name, doc) WITH PARSER NGRAM PARSER_PROPERTIES=(ngram_token_size=3));
Create a full-text index by using the CREATE FULLTEXT INDEX statement
You can execute the CREATE FULLTEXT INDEX statement to create a full-text index for an existing table. The syntax is as follows:
CREATE FULLTEXT INDEX index_name ON table_name (column_name, [column_name ...])
[WITH PARSER tokenizer_option]
[PARSER_PROPERTIES[=](parser_properties_list)]
[LOCAL];
tokenizer_option:
SPACE
| NGRAM
| BENG
| IK
| NGRAM2
| analyzer
parser_properties_list:
parser_properties, [parser_properties]
parser_properties:
min_token_size = int_value
| max_token_size = int_value
| ngram_token_size = int_value
| ik_mode = 'char_value'
| min_ngram_size = int_value
| max_ngram_size = int_value
| analysis = json_string
For more information about the CREATE INDEX syntax, see CREATE INDEX.
The relevant parameters are described as follows:
index_name: The name of the index to be created.table_name: The name of the table on which to create the index.column_name: The columns to be indexed. When specifying multiple columns, separate them with commas.WITH PARSER tokenizer_option: Optional. Specifies the tokenizer for the full-text index. Valid values:SPACE: The default value, which specifies to tokenize by spaces. You can specify the following attributes:AttributeValue rangemin_token_size [1, 16] max_token_size [10, 84] NGRAM: Specifies to tokenize based on N-Grams (Chinese). You can specify the following attributes:AttributeValue rangengram_token_size [1, 10] NGRAM2: Splits text into consecutive characters within the range ofmin_ngram_sizetomax_ngram_size. You can specify the following attributes:AttributeValue rangemin_ngram_size [1, 16] max_ngram_size [1, 16] BENG: Specifies to tokenize based on Beng (Basic English). You can specify the following attributes:AttributeValue rangemin_token_size [1, 16] max_token_size [10, 84] IK: Specifies to tokenize based on IK (Chinese). Currently, only theutf-8character set is supported. You can specify the following attributes:AttributeValue rangeik_mode smartmax_word
You can use the TOKENIZE function to view the tokenization result of a text using the specified tokenizer and JSON-formatted parameters.
PARSER_PROPERTIES[=](parser_properties_list): Optional. Specifies the properties of the tokenizer. Valid values:min_token_size: The minimum token length. Default value: 3. Value range: 1 to 16.max_token_size: The maximum token length. Default value: 84. Value range: 10 to 84.ngram_token_size: The token length forNGRAM. This parameter is valid only for theNGRAMtokenizer. The default value is 2, and the valid range is 1 to 10.ik_mode: The tokenization mode for theIKtokenizer. Valid values:smart: The default value. In this mode, dictionary words are used to improve tokenization accuracy. Dictionary word boundaries are prioritized, which may reduce unnecessary expansions.max_word: In this mode, dictionary-defined words are recognized, but it does not affect the maximization of token expansions. Even if a word is defined in the dictionary, themax_wordmode still attempts to split the text into more words.
LOCAL: Optional. Specifies to create a local index.
Example:
After creating table tbl3, create a full-text index named ft_idx1_tbl3.
Create table
tbl3.CREATE TABLE tbl3(col1 INT, col2 VARCHAR(4096));Create a full-text index named
ft_idx1_tbl3on tabletbl3. Specify the tokenizer for the full-text index asIKand set the tokenizer properties usingPARSER_PROPERTIES.CREATE FULLTEXT INDEX ft_idx1_tbl3 ON tbl3(col2) WITH PARSER IK PARSER_PROPERTIES=(ik_mode='max_word');
Create a full-text index by using the ALTER TABLE statement
You can execute the ALTER TABLE statement to add a full-text index to an existing table. The syntax is as follows:
ALTER TABLE table_name ADD FULLTEXT [INDEX | KEY] [index_name](column_name, [column_name ...])
[WITH PARSER tokenizer_option]
[PARSER_PROPERTIES[=](parser_properties_list)]
[LOCAL];
tokenizer_option:
SPACE
| NGRAM
| BENG
| IK
| NGRAM2
| analyzer
parser_properties_list:
parser_properties, [parser_properties]
parser_properties:
min_token_size = int_value
| max_token_size = int_value
| ngram_token_size = int_value
| ik_mode = 'char_value'
| min_ngram_size = int_value
| max_ngram_size = int_value
| analysis = json_string
For more information about the syntax for adding a full-text index to an existing table by using the ALTER TABLE statement, see ALTER TABLE.
The relevant parameters are described as follows:
table_name: The name of the table for which you want to create the index.INDEX | KEY: Optional. In this statement, you can use eitherINDEXorKEYas the index keyword. The default value isKEY.index_name: Optional. The name of the index to be created. If not specified, the default index name is the same as the specified column name.column_name: Specifies the columns to be indexed. When specifying multiple columns, separate them with commas.WITH PARSER tokenizer_option: Optional. Specifies the tokenizer for the full-text index. Valid values:SPACE: The default value. Indicates that tokens are separated by spaces. You can specify the following attributes:AttributeValue rangemin_token_size [1, 16] max_token_size [10, 84] NGRAM: Indicates that tokens are separated based on N-grams (Chinese). You can specify the following attributes:AttributeValue rangengram_token_size [1, 10] NGRAM2: Indicates that text is split into consecutive characters within the range ofmin_ngram_sizetomax_ngram_size. You can specify the following attributes:AttributeValue rangemin_ngram_size [1, 16] max_ngram_size [1, 16] BENG: Indicates that tokens are separated based on Beng (basic English). You can specify the following attributes:AttributeValue rangemin_token_size [1, 16] max_token_size [10, 84] IK: Indicates that tokens are separated based on IK (Chinese). Currently, only theutf-8character set is supported. You can specify the following attributes:AttributeValue rangeik_mode smartmax_word
You can use the TOKENIZE function to view the tokenization result of text using the specified tokenizer and JSON-formatted parameters.
PARSER_PROPERTIES[=](parser_properties_list): Optional. Specifies the properties of the tokenizer. Valid values:min_token_size: The minimum token length. Default value: 3. Value range: 1 to 16.max_token_size: The maximum token length. Default value: 84. Value range: 10 to 84.ngram_token_size: The token length forNGRAM. This parameter is valid only for theNGRAMtokenizer. Default value: 2. Value range: 1 to 10.ik_mode: The segmentation mode for theIKtokenizer. Valid values:smart: The default value. Lexical terms in the dictionary are used to improve tokenization accuracy. Dictionary boundaries are prioritized, which may reduce unnecessary expansions.max_word: Defined terms in the dictionary are recognized, but this does not affect the maximization of token expansions. Even if a term is defined in the dictionary, themax_wordmode still attempts to split the text into more words.
LOCAL: Optional. Specifies to create a local index.
Example:
After creating table tbl4, add a full-text index ft_idx1_tbl4.
Create table
tbl4.CREATE TABLE tbl4(col1 INT, col2 TEXT);Add a full-text index
ft_idx1_tbl4to tabletbl4. Specify the tokenizer for the full-text index asBENG, and set tokenizer properties usingPARSER_PROPERTIES.ALTER TABLE tbl4 ADD FULLTEXT INDEX ft_idx1_tbl4(col2) WITH PARSER BENG PARSER_PROPERTIES=(min_token_size=2, max_token_size=64);
Create a full-text index by using an analyzer (Analyzer)
Note
This feature is supported starting with V4.6.2.1.
The full-text index of OceanBase AI Database introduces an analyzer (Analyzer), allowing users to flexibly define text analysis pipelines (pipelines) using declarative JSON syntax, covering multiple stages such as character-level preprocessing (CharFilter), tokenization (Tokenizer), and post-tokenization processing (TokenFilter). You can select the tokenization method using the WITH PARSER keyword: specifying analyzer uses the Analyzer, while other tokenizer names follow the traditional plugin path, enabling compatibility with various text analysis requirements.
Syntax (applicable to table creation, index creation after table creation, and ALTER TABLE to add an index):
-- During table creation
CREATE TABLE docs (
id BIGINT PRIMARY KEY,
content TEXT,
FULLTEXT INDEX ft_idx_content(content)
WITH PARSER analyzer
PARSER_PROPERTIES=(analysis='{"analyzer": "standard"}')
);
-- Create an index afterward
CREATE FULLTEXT INDEX ft_idx_body ON articles(body)
WITH PARSER analyzer
PARSER_PROPERTIES=(analysis='{"analyzer": "english"}');
-- ALTER TABLE ADD
ALTER TABLE articles ADD FULLTEXT INDEX ft_idx_title(title)
WITH PARSER analyzer
PARSER_PROPERTIES=(analysis='{"analyzer": "indonesian"}');
Constraints:
- One full-text index corresponds to one Analyzer definition. Multiple full-text indexes can be created on the same column, each using a different Analyzer.
- The index column type must be a character or large object type (
CHAR,VARCHAR,TINYTEXT,TEXT,MEDIUMTEXT,LONGTEXT,STRING). - Modifying the Analyzer definition requires rebuilding the index.
- If
WITH PARSERdoes not match any registered tokenizer, an error is returned. - By default, the Analyzer uniformly encodes input as
utf8mb4_binto avoid encoding limitations of some tokenizers (Tokenizer) and token filters (TokenFilter). - The Analyzer performs tokenization based on ICU BreakIterator, supporting multi-language Unicode and emoji recognition; output tokens are automatically converted to lowercase.
You can use TOKENIZE to preview the tokenization results before creating the index.
Create a search index (Search Index)
Notice
WITH (...)applies only to JSON columns.INCLUDE_PATHSandEXCLUDE_PATHScannot be specified at the same time.NESTED_PATHSforces indexing for all fields under the specified path, with a higher priority thanEXCLUDE_PATHS.
Create a search index using the CREATE SEARCH INDEX statement
You can execute the CREATE SEARCH INDEX statement to create a search index on an existing table. The syntax is as follows:
CREATE SEARCH INDEX index_name ON table_name (
search_index_column
[, search_index_column ...]
) [LOCAL];
search_index_column:
column_name [WITH (search_col_option_list)]
search_col_option_list:
[INCLUDE_PATHS = ('path1'[, 'path2']...)]
[| EXCLUDE_PATHS = ('path1'[, 'path2']...)]
[| INCLUDE_TYPES = (type1[, type2]...)]
[| NESTED_PATHS = ('path1'[, 'path2']...)]
For more information about the syntax and supported data types of CREATE SEARCH INDEX, see CREATE INDEX.
The parameters are described as follows:
index_name: The name of the search index to be created.table_name: The name of the table on which to create the index.search_index_column: The definition of the search index column. You can specify one or more columns. For each column, you can useWITH (...)to specify column-level path and type options.INCLUDE_PATHS / EXCLUDE_PATHS: Column-level path allowlist/blocklist. Choose one of these two options to control the JSON paths of the column used in index construction.INCLUDE_TYPES: Column-level type filtering. Only indexes the specified JSON scalar types (currentlyJSON_STRINGandJSON_NUMBERare supported).NESTED_PATHS: Declares nested paths within JSON (must start with$.and cannot contain column names). This forces indexing for all fields under the path, used for nested queries in hybrid searches. For more information, see Search index.LOCAL: Optional. Used to create a local search index on a partitioned table. Currently, only local search indexes are supported for partitioned tables.
Example:
After creating the table tbl_search_1, create a search index named search_idx_1.
Create the table
tbl_search_1.CREATE TABLE tbl_search_1 ( id INT, doc JSON, c2 VARCHAR(64) ) ORGANIZATION = HEAP;Create the search index
search_idx_1on the tabletbl_search_1and configure path allowlists and type filtering for the JSON column.CREATE SEARCH INDEX search_idx_1 ON tbl_search_1( doc WITH ( INCLUDE_PATHS = ('$.name', '$.score'), INCLUDE_TYPES = (JSON_STRING, JSON_NUMBER) ), c2 );
Create a search index using the CREATE TABLE statement
You can execute the CREATE TABLE statement to create a search index when creating a table. The syntax is as follows:
CREATE TABLE table_name (
...,
SEARCH INDEX index_name (
search_index_column
[, search_index_column ...]
)
) ORGANIZATION = HEAP;
For more information about the syntax and supported data types of CREATE TABLE, see CREATE TABLE.
The parameters are described as follows:
table_name: The name of the table for which you want to create an index.index_name: The name of the search index to be created.search_index_column: The definition of the columns for the search index. You can specify one or more columns. For each column, you can useWITH (...)to specify column-level path and type options.
Example:
Create a table named tbl_search_3 and create a search index named search_idx_3 in the same operation.
CREATE TABLE tbl_search_3 (
id INT,
profile JSON,
tag VARCHAR(32),
SEARCH INDEX search_idx_3 (
profile WITH (EXCLUDE_PATHS = ('$.debug')),
tag
)
) ORGANIZATION = HEAP;
Create a search index by using the ALTER TABLE statement
You can execute the ALTER TABLE statement to add a search index to an existing table. The syntax is as follows:
ALTER TABLE table_name
ADD SEARCH INDEX [index_name] (
search_index_column
[, search_index_column ...]
);
search_index_column:
column_name [WITH (search_col_option_list)]
search_col_option_list:
[INCLUDE_PATHS = ('path1'[, 'path2']...)]
[| EXCLUDE_PATHS = ('path1'[, 'path2']...)]
[| INCLUDE_TYPES = (type1[, type2]...)]
[| NESTED_PATHS = ('path1'[, 'path2']...)]
For more information about the syntax for adding a search index to an existing table by using the ALTER TABLE statement, see ALTER TABLE.
The related parameters are described as follows:
table_name: The name of the table to which you want to add a search index.index_name: Optional. The name of the search index. If you do not specify this parameter, the system generates a default index name based on the rules.search_index_column: The definition of the columns for the search index. You can specify one or more columns. For each column, you can useWITH (...)to specify column-level path and type options.INCLUDE_PATHS / EXCLUDE_PATHS: Either allowlist or blocklist of column-level paths. You must specify one of these two options to control the JSON paths of the columns involved in index construction.INCLUDE_TYPES: Column-level type filtering. Only indexes the specified JSON scalar types (currentlyJSON_STRINGandJSON_NUMBER).NESTED_PATHS: Declares nested paths in JSON for nested queries in hybrid searches.
Example:
After creating a table named tbl_search_2, add a search index named search_idx_2 by using the ALTER TABLE statement.
Create a table named
tbl_search_2.CREATE TABLE tbl_search_2 ( id INT, profile JSON, tag VARCHAR(32) ) ORGANIZATION = HEAP;Add a search index named
search_idx_2to the tabletbl_search_2.ALTER TABLE tbl_search_2 ADD SEARCH INDEX search_idx_2 ( profile WITH (EXCLUDE_PATHS = ('$.debug')), tag );
Create an asynchronous index
Asynchronous indexes support full-text indexes and search indexes. By adding the ASYNC keyword before FULLTEXT or SEARCH, you can create them either at the same time as a table or after the table is created. For complete descriptions and examples, see the "Asynchronous Full-Text Indexes" section in Full-text search and the "Asynchronous Search Indexes" section in Search index.
Create an asynchronous full-text index
Note
Asynchronous full-text indexes are supported starting from V4.6.2.1.
For complete descriptions and examples, see the "Asynchronous Full-Text Indexes" section in Full-text search.
CREATE TABLE t_async_params (
c1 INT,
c2 TEXT,
ASYNC FULLTEXT INDEX idx_ft (c2)
) ORGANIZATION HEAP ASYNC_INDEX_PARAMS = 'ASYNC_REFRESH_INTERVAL=10';
ALTER TABLE t_async_params SET ASYNC_INDEX_PARAMS = 'REFRESH_ON_ERROR=SKIP, ASYNC_REFRESH_INTERVAL=30';
Create an asynchronous search index
Note
Asynchronous search indexes are supported starting from V4.6.2.1.
The table-level refresh parameters for asynchronous search indexes, ASYNC_INDEX_PARAMS (including ASYNC_REFRESH_INTERVAL and REFRESH_ON_ERROR), are shared with asynchronous full-text indexes. For complete descriptions and examples, see the "Asynchronous Search Indexes" section in Search index.
CREATE TABLE t (
id BIGINT,
doc JSON,
ASYNC SEARCH INDEX idx_search (doc)
) ORGANIZATION HEAP;
CREATE ASYNC SEARCH INDEX idx_search ON t(doc);
ALTER TABLE t ADD ASYNC SEARCH INDEX idx_search(doc);
References
For more information about indexes in MySQL-compatible mode, see the following topics: