This topic describes how to use search indexes, including an overview of capabilities, prerequisites, supported data types and predicates, common DDL statements, and query examples.
A search index can be built on a single column or multiple columns to create a unified inverted index structure. It creates index entries for multiple paths within a JSON column (or for the entire value of a scalar column). A single index can cover multi-path access and support various predicates. Compared with creating separate function-based indexes for each access path or using multi-valued indexes that expand array elements, search indexes emphasize creating one index that covers multiple paths, reducing the number of indexes and maintenance costs, and making it easier to handle scenarios with variable paths and diverse query patterns in semi-structured data.
Overview
- Typical scenarios: Used together with Hybrid search or with multi-column search indexes and multi-index joint queries (Index Merge) on analytical wide tables to improve performance.
- AI hybrid search scenario: Full-text tokenization and keyword search are handled by the full-text index. The search index focuses on structured/semi-structured conditions within complex types (can also be used for simple scalars). The two apply to different column types, can coexist in the same table, and can be used together with vector indexes.
- AP analytical scenario: Assuming search indexes are built separately for wide tables and multiple columns, the optimizer can leverage Index Merge to automatically identify available index combinations and select a better scan path, improving performance.
- DDL operations: The DDL operations for search indexes are consistent with those for regular indexes, supporting changes such as creation, deletion, renaming, and visibility. Adding the
ASYNCkeyword beforeSEARCHallows creating asynchronous search indexes, changing index maintenance to background incremental refresh. - Nested queries: JSON object arrays are stored in a flattened format by default, which loses associations between fields of the same object. By declaring nested paths with
NESTED_PATHS, nested queries can be used in hybrid search syntax, requiring multiple conditions to match the same array element, preventing the system from "piecing together" content from different documents.
Limitations
- Tenants: This feature is only applicable to MySQL-compatible tenants.
- Table type: The target table must be a
ORGANIZATION = HEAPheap table. - Direct load: Tables containing synchronous or asynchronous search indexes support full direct load, but do not support incremental direct load.
- Column-level configuration: Column-level
WITH (...)/NESTED_PATHSonly take effect on JSON columns. - JSON path length: The maximum encoded JSON path length is 2KB. If a JSON document has very long keys or extremely deep nesting, DML operations may fail due to path length exceeding the limit (
ERROR 1071). It is recommended to avoid designing JSON documents with excessively long keys. - Array type: Currently, only single-level arrays are supported; nested arrays are not supported.
- Nested paths:
- Paths in
NESTED_PATHScan only consist of object member names (e.g.,$.comments,$.comments.replies), and do not allow wildcards (e.g.,$.comments.*) or array indices (e.g.,$.comments[0]); - The number of path segments (excluding the root path
$) does not exceed 20; - Each search index column allows only one
NESTED_PATHSclause; - Declaring the root path
$as a nested path is not supported.
- Paths in
- Partitioned tables: Currently, partitioned tables support only local search indexes (
LOCAL). - Indexed columns: Accelerated matching in
LIKEmode is not supported for indexed columns. - Generated columns: Creating search indexes on generated columns (
GENERATED) is not supported; theSTORINGclause is not supported. - Asynchronous search indexes: Temporary tables and global indexes (
GLOBAL) are not supported; synchronous and asynchronous search indexes on the same table are mutually exclusive, but multiple asynchronous search indexes are allowed and can coexist with synchronous or asynchronous full-text indexes. The query syntax is consistent with that of synchronous search indexes. Index data is refreshed incrementally in the background, ensuring eventual consistency only.
Usage
Supported data types
Search indexes are primarily used to accelerate queries on scalar columns and JSON or single-level array columns. The specific supported types are as follows:
Category |
Supported types |
|---|---|
| Integer types | TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, and their UNSIGNED variants |
| Floating-point and fixed-point types | FLOAT, DOUBLE, DECIMAL, NUMBER |
| String types | VARCHAR, CHAR |
| Text types | TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT |
| Binary types | BINARY, VARBINARY |
| Binary large objects | TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB |
| Date and time types | DATE, TIME, DATETIME, TIMESTAMP, YEAR |
| JSON type | JSON |
| Array types | ARRAY(element_type) (single-level only, for example, ARRAY(INT) and ARRAY(VARCHAR(256))) |
Limitations are as follows:
- Creating search indexes on columns of the
BIT,ENUM,SET, orMAPtype is not supported. - For PICK columns, the
json_numberfield supports integer, floating-point, and fixed-point types, and thejson_stringfield supports string types.
Syntax and parameters
Search indexes can be created when creating a table, after the table is created, or appended to an existing table. The specific syntax and common parameters for each method are as follows:
CREATE TABLE table_name (
column_name type
[, column_name type ...]
SEARCH INDEX index_name (
column_name [WITH (option_list)]
[, column_name [WITH (option_list)] ...]
)
) ORGANIZATION = HEAP;
option_list:
[INCLUDE_PATHS = ('path1'[, 'path2']...)]
[| EXCLUDE_PATHS = ('path1'[, 'path2']...)]
[| INCLUDE_TYPES = (type1[, type2]...)]
[| NESTED_PATHS = ('path1'[, 'path2']...)]
CREATE SEARCH INDEX index_name ON table_name (
column_name [WITH (option_list)]
[, column_name [WITH (option_list)] ...]
) [LOCAL];
option_list:
[INCLUDE_PATHS = ('path1'[, 'path2']...)]
[| EXCLUDE_PATHS = ('path1'[, 'path2']...)]
[| INCLUDE_TYPES = (type1[, type2]...)]
[| NESTED_PATHS = ('path1'[, 'path2']...)]
You can also use the ALTER TABLE statement to create a search index:
ALTER TABLE table_name ADD SEARCH INDEX index_name (
column_name [WITH (option_list)]
[, column_name [WITH (option_list)] ...]
);
option_list:
[INCLUDE_PATHS = ('path1'[, 'path2']...)]
[| EXCLUDE_PATHS = ('path1'[, 'path2']...)]
[| INCLUDE_TYPES = (type1[, type2]...)]
[| NESTED_PATHS = ('path1'[, 'path2']...)]
Parameter |
Description |
Value range |
|---|---|---|
| option_list ... | Column-level options, which control the JSON path of the column used in index construction. | |
| INCLUDE_PATHS / EXCLUDE_PATHS | Specify either a column-level path allowlist or blocklist to control the JSON paths of this column used in index construction. By default, all paths are included. Two path configurations are supported:
|
|
| INCLUDE_TYPES | Limits the set of data types that can participate in index construction (the type values are subject to the support of the actual version). For example, INCLUDE_TYPES = (JSON_STRING) indicates that a path index is built only for the JSON_STRING type. By default, all types participate in index construction. |
JSON_STRING, JSON_NUMBER |
| NESTED_PATHS | Declares which paths in a JSON document are nested paths. Each path corresponds to an object array field, and all fields under that path are forcibly indexed. A path must start with $., be a dot-separated member path, and contain no column name. Multiple paths can be declared. For multi-level nesting, declare both the intermediate and deeper paths, for example, ('$.comments', '$.comments.replies'). This parameter can be used together with INCLUDE_PATHS or EXCLUDE_PATHS, and with INCLUDE_TYPES. NESTED_PATHS takes precedence over EXCLUDE_PATHS when overriding paths. |
|
| LOCAL | Optional. Specifies to create a local index. For a partitioned table, a local search index must be created after the table is created or during an append operation. |
Other change operations such as deletion, renaming, and visibility are consistent with regular indexes. For more information, see the related documentation at the end of this topic.
Asynchronous search index
Note
Asynchronous search indexes are supported starting from V4.6.2.1.
Notice
Asynchronous search indexes are an experimental feature in the current version and are not supported for production use.
Asynchronous search indexes reuse the DDL of synchronous search indexes. By adding the ASYNC keyword before SEARCH, they support creation with the table and after the table is created. SEARCH KEY and SEARCH INDEX are syntactic aliases that do not affect the index type. Independent creation later only supports CREATE ASYNC SEARCH INDEX and does not support CREATE ASYNC SEARCH KEY.
Enable and use
The asynchronous indexing feature is disabled by default and must be enabled using the _enable_async_index parameter:
ALTER SYSTEM SET _enable_async_index = true;
CREATE TABLE t (
id BIGINT,
doc JSON,
ASYNC SEARCH INDEX idx_search (doc)
) ORGANIZATION HEAP;
-- Configure nested paths (multiple paths can be declared simultaneously).
CREATE TABLE t (
id BIGINT,
doc JSON,
ASYNC SEARCH INDEX idx_search (
doc WITH (
NESTED_PATHS = ('$.comments', '$.items')
)
)
) ORGANIZATION HEAP;
CREATE ASYNC SEARCH INDEX idx_search ON t(doc);
ALTER TABLE t ADD ASYNC SEARCH INDEX idx_search(doc);
ALTER TABLE t ADD ASYNC SEARCH INDEX idx_search (
doc WITH (
NESTED_PATHS = ('$.comments')
)
);
The query syntax is the same as that of hybrid search on synchronous search indexes, supporting conditions such as term, terms, and range on scalars and JSON. After writing data, you must wait for the background incremental refresh to complete before the data becomes visible to queries.
SELECT id, doc FROM HYBRID_SEARCH(
TABLE t,
'{
"query": {
"term": {"doc.title": "OceanBase Manual A"}
}
}'
);
Table-level parameter ASYNC_INDEX_PARAMS
Asynchronous indexed search and asynchronous full-text index search share the table-level configuration ASYNC_INDEX_PARAMS. Users only need to specify the keys they want to modify; unspecified keys will retain their original values.
Parameter |
Type |
Value range |
Default value |
Meaning |
|---|---|---|---|---|
ASYNC_REFRESH_INTERVAL |
INT (seconds) | [1, 60], -1 |
5 |
The scan cycle of the background incremental refresh service. It checks whether there is any new incremental data in the table at regular intervals, and triggers an incremental refresh if any is found. -1 indicates that background refresh is disabled. This parameter is supported starting from V4.6.2.1. |
REFRESH_ON_ERROR |
Enumeration | SKIP / ABORT |
ABORT |
The strategy for handling single-row abnormal data during incremental refresh. ABORT indicates that the entire refresh fails and blocks subsequent incremental refreshes; SKIP indicates that the abnormal row is skipped and the refresh continues without blocking other rows or indexes, and the row is not written to the index. Resource or environment errors are not skipped even in SKIP mode. This parameter is supported starting from V4.6.2.1. |
DELETE_PURGE_THRESHOLD |
INT | [20, 50] |
33 |
A major compaction is triggered when the proportion of deleted rows exceeds this threshold to clean up the deleted data. |
MERGE_SEGMENT_THRESHOLD |
INT | [2, 48] |
10 |
A major compaction is triggered when the number of segments generated by incremental refreshes exceeds this value. |
Notice
REFRESH_ON_ERROR is used for scenarios where the index is created with the table: when writing data to a table, it does not check whether the data can be written to the asynchronous index. If an invalid row exists (for example, a JSON path exceeds 2 KB), the default ABORT policy will block the incremental refresh. To allow the refresh of subsequent data, change the strategy to SKIP.
-- Set the refresh cycle upon table creation
CREATE TABLE t_async_params (
c1 INT,
c2 JSON,
ASYNC SEARCH INDEX idx_si (c2)
) ORGANIZATION HEAP ASYNC_INDEX_PARAMS = 'ASYNC_REFRESH_INTERVAL=10';
-- Modify the exception row strategy and refresh cycle of an existing table
ALTER TABLE t_async_params SET ASYNC_INDEX_PARAMS = 'REFRESH_ON_ERROR=SKIP, ASYNC_REFRESH_INTERVAL=30';
For manual triggering of major compactions, see Overview of DBMS_ASYNC_INDEX. For similar configuration descriptions on asynchronous full-text indexes, see the "Asynchronous Full-text Index" section in Full-text search.
Nested paths and nested queries
Note
Nested queries are supported starting from V4.6.2.1.
JSON object arrays are flattened by path by default in search indexes, losing field associations within the same object. For example, if a movie has two reviews: Xiao Ming gave a score of 3 and Xiao Hong gave a score of 9. If you query Author is Xiao Ming and Score ≥ 8, under non-nested semantics, the system flattens each field into independent elements, only seeing Xiao Ming exists and 9 exists, which might lead to a false hit on this document. By declaring NESTED_PATHS, you can use nested queries in hybrid searches, requiring the conditions to act on the same review. In this case, Xiao Ming is associated with 3, and Xiao Hong is associated with 9. The system will determine that Xiao Ming does not have a score of ≥ 8, thus avoiding a false hit on this document. Document illustration:
{
"title": "A Movie",
"comments": [
{"author": "Xiao Ming", "rating": 3},
{"author": "Xiao Hong", "rating": 9}
]
}
Specify nested paths when creating indexes:
CREATE TABLE movie_rating (
k INT,
j1 JSON,
SEARCH INDEX idx_nested (
j1 WITH (
-- Declares a nested path. $ indicates the root path, and comments indicates the first-level object array field.
NESTED_PATHS = ('$.comments')
)
)
) ORGANIZATION = HEAP;
If you query the author is Xiao Ming and the rating is ≥ 8 in the same comment, the system determines that Xiao Ming does not have a rating of ≥ 8, so this document will not be hit:
SELECT k, j1 FROM HYBRID_SEARCH(
TABLE movie_rating,
'{
"query": {
-- Use nested clauses in hybrid searches
"nested": {
"path": "j1.comments",
"query": {
"bool": {
"must": [
{"term": {"j1_comments.author": "Xiao Ming"}},
{"range": {"j1.comments.rating": {"gte": 8}}}
]
}
}
}
}
}'
);
For the complete syntax, path matching rules, and limitations, see Related documents at the end of this topic.
Supported filter queries
Note
- Whether to use a search index for optimization is determined by the optimizer and the
EXPLAINexecution plan. This topic only describes the scope of filter queries that can generally be accelerated.
Scalar
For scalar columns (numeric, string, and time), the following filter queries can be accelerated:
- Equality:
col = value - Range:
col > / >= / < / <=,BETWEEN IN (list)IS NULL/IS NOT NULL- Multiple conditions combined with
AND/OR: If multiple columns are defined in the same search index or combined with other indexes, Index Merge may be used.
Recommendation: For high selectivity and ordered range access, retain B-tree indexes. For low selectivity, OR combinations, or coexistence of JSON and arrays, search indexes with Index Merge are usually more efficient.
JSON
After a search index is created, you can directly query the internal fields of a JSON column without full table scans.
Category |
Note |
|---|---|
| Value by path | In a JSON column, extract the specified scalar value by path, and then use the JSON_EXTRACT() function or -> operator to compare it with a value by using equality or range comparison operators (>, >=, <, <=, BETWEEN, etc.). For example, col->'$.price' > 100. |
MEMBER OF |
Determines whether a value is an element in a JSON array. The right operand can be a column or a path expression. For example, MEMBER OF(col). |
JSON_CONTAINS |
Determines whether a JSON document contains a specified scalar value or subdocument. For example, JSON_CONTAINS(c1->'$.tags', '"mobile"'). |
JSON_OVERLAPS |
Determines whether two JSON arrays have at least one common element. For example, JSON_OVERLAPS(col1, '[1, 2, 3]'). |
IN |
You can use the IN (...) clause to match the scalar value extracted by path or the entire column. For example, IN (col->'$.price', col2). |
PICK |
When the value of a JSON field may be of multiple types (such as both numeric and string values exist in the same path), you can use the PICK keyword to filter by type in queries. You can specify PICK json_number or PICK json_string after the path. Use it only in combination with equality and range comparisons on the same path (>, >=, <, <=, BETWEEN, and so on). Using it together with MEMBER OF, JSON_CONTAINS, or JSON_OVERLAPS is not recommended, because such statements usually cannot use the search index. json_number and json_string indicate numeric and string types, respectively. For more information, see Supported data types. |
Notice
When using PICK with JSON_VALUE(), if clauses such as RETURNING, ON ERROR, or ON EMPTY are also present, the search index acceleration typically cannot be achieved.
Recommendation: Specify paths explicitly in queries. Use the WITH clause to narrow the index scope when a document contains many fields. Use PICK for mixed types in the same path. Use full-text indexes for keyword searches.
Single-level arrays
For ARRAY(element_type) columns:
Function |
Description |
|---|---|
ARRAY_CONTAINS(col, value) |
Whether the array contains a specific element. |
ARRAY_CONTAINS_ALL(col, array_literal) |
Whether the array contains all elements in the specified array. |
ARRAY_OVERLAPS(col, array_literal) |
Whether the array has at least one common element. |
Recommendation: Use single-level arrays for tags and multi-value attributes. Select the contains, contains all, or overlaps function based on the semantics. You can also use these functions with B-tree conditions on tables for Index Merge.
Check if an index is used
You can use the EXPLAIN statement to view the execution plan and confirm whether the query actually uses a search index. For example:
EXPLAIN SELECT * FROM products WHERE info->'$.price' > 100 AND category = 'electronics';
If the execution plan includes the name of the search index (for example, idx_products), it indicates that the query actually uses the search index.
Scenario examples
The following are some common scenario examples for reference.
Scalar column queries
The following example demonstrates how to create search indexes on integer and string columns and perform various queries:
Create the sample table and search indexes, and insert test data.
CREATE TABLE orders ( order_id INT, user_id INT, amount DOUBLE, status VARCHAR(64), SEARCH INDEX idx_orders (user_id, amount, status) ) ORGANIZATION = HEAP; INSERT INTO orders VALUES (1, 100, 99.5, 'paid'); INSERT INTO orders VALUES (2, 101, 200.0, 'pending'); INSERT INTO orders VALUES (3, 100, 50.0, 'paid'); INSERT INTO orders VALUES (4, 102, 150.0, 'shipped'); INSERT INTO orders VALUES (5, 100, 300.0, 'refund');Various queries
-- Equal-value query: Find all orders where user_id = 100. SELECT * FROM orders WHERE user_id = 100; -- The expected return is three rows with order_id=1, 3, and 5. -- Range query: Find orders with an amount between 80 and 200. SELECT * FROM orders WHERE amount > 80 AND amount < 200; -- Expected to return two rows with order_id=1 and 4. -- IN query: Find orders with the status paid or shipped. SELECT * FROM orders WHERE status IN ('paid', 'shipped'); -- The expected response is three rows with order_id=1, 3, and 4. -- Multiple conditions combined SELECT * FROM orders WHERE user_id = 100 AND status = 'paid'; -- Expected return two rows where order_id = 1 and 3.Confirm that the index is used
EXPLAIN SELECT * FROM orders WHERE user_id = 100;The expected execution plan should include the search index name
idx_orders.
JSON queries
Create the sample table and search indexes, and insert test data.
CREATE TABLE products ( id INT, -- Scalar column info JSON, -- JSON column SEARCH INDEX idx_products (info) ) ORGANIZATION = HEAP; INSERT INTO products VALUES (1, '{"name": "Phone", "price": 999, "brand": "Apple", "tags": ["electronics", "mobile"]}'), (2, '{"name": "Book", "price": 29, "brand": "O\'Reilly", "tags": ["education"]}'), (3, '{"name": "Laptop", "price": 1999, "brand": "Dell", "tags": ["electronics", "computer"]}'), (4, '{"name": "Pen", "price": 5, "brand": "Pilot", "tags": ["stationery"]}');Various queries
-- Path comparison: Price is between 10 and 1,000 SELECT * FROM products WHERE info->'$.price' BETWEEN 10 AND 1000; -- Expected return Book and Phone -- MEMBER OF query: Query products whose tags contain "electronics" SELECT * FROM products WHERE '"electronics"' MEMBER OF (info->'$.tags'); -- Expected response: Phone and Laptop -- JSON_CONTAINS query: tags must contain both electronics and mobile SELECT * FROM products WHERE JSON_CONTAINS(info->'$.tags', '["electronics", "mobile"]'); -- Expected to return PhoneConfirm that the index is used
EXPLAIN SELECT * FROM products WHERE info->'$.price' BETWEEN 10 AND 1000;The expected execution plan should include the search index name
idx_products.
JSON filter queries
The following example demonstrates how to use the WITH clause to limit the index range in a user profile scenario.
Create a sample table and search index, and insert test data.
CREATE TABLE users ( id INT, profile JSON, SEARCH INDEX idx_profile (profile WITH (INCLUDE_PATHS = ('$.name', '$.address.*'))) ) ORGANIZATION = HEAP; INSERT INTO users VALUES (1, '{"name": "Zhang San", "age": 30, "address": {"city": "Beijing", "zip": 100000}, "debug": "xxx"}'), (2, '{"name": "Li Si", "age": 25, "address": {"city": "Shanghai", "zip": 200000}, "debug": "yyy"}');Various queries
-- Queries using indexes (within the INCLUDE_PATHS scope) SELECT * FROM users WHERE JSON_EXTRACT(profile, '$.name') = '"Zhang San"'; -- Expected to use the index SELECT * FROM users WHERE JSON_EXTRACT(profile, '$.address.city') = '"Beijing"'; -- Expected to use the index -- Queries that do not use indexes (outside the INCLUDE_PATHS scope) SELECT * FROM users WHERE JSON_EXTRACT(profile, '$.age') = 30; -- Expected full table scan (age path not indexed)Confirm index usage
EXPLAIN SELECT * FROM users WHERE JSON_EXTRACT(profile, '$.age') = 30;The expected execution plan does not include the search index name
idx_profile, indicating a full table scan is performed.
Multi-index joint query (index merge)
The following example demonstrates how to use a multi-index joint query. The Index Merge capability of the search index allows it to work together with the table's B-tree indexes and full-text indexes.
Create a table with multiple indexes and insert test data.
CREATE TABLE products_v2 ( id INT PRIMARY KEY, name VARCHAR(128), category INT, price INT, tags JSON, INDEX idx_category(category), -- B-tree index INDEX idx_price(price), -- B-tree index FULLTEXT INDEX ft_name(name), -- Full-text index SEARCH INDEX idx_tags(tags) -- Search index ) ORGANIZATION HEAP; INSERT INTO products_v2 VALUES (1, 'OceanBase Database', 1, 0, '{"type": "database", "license": "open-source"}'), (2, 'MySQL Server', 1, 0, '{"type": "database", "license": "open-source"}'), (3, 'Premium Support', 2, 999, '{"type": "service"}');Various queries
-- The category uses a B-tree index, the tags use a search index, and the results are the intersection of both. SELECT * FROM products_v2 WHERE category = 1 AND json_extract(tags, '$.license') = '"open-source"'; -- Full-text search for "database" or tags with type "service" SELECT * FROM products_v2 WHERE MATCH(name) AGAINST ("database") OR json_extract(tags, '$.type') = '"service"'; -- Complex combination: B-tree AND (B-tree OR search index) SELECT * FROM products_v2 WHERE category = 1 AND (price = 0 OR json_extract(tags, '$.type') = '"service"');
Nested queries
The following example demonstrates how to declare NESTED_PATHS and filter by the same comment object in a hybrid search.
Create tables and search indexes, and insert test data.
CREATE TABLE movie_rating ( k INT, j1 JSON, SEARCH INDEX idx_nested ( j1 WITH ( NESTED_PATHS = ('$.comments') ) ) ) ORGANIZATION = HEAP; INSERT INTO movie_rating VALUES (1, '{"title":"doc1","comments":[{"author":"alice","rating":4},{"author":"bob","rating":5}]}'), (2, '{"title":"doc2","comments":[{"author":"alice","rating":5},{"author":"bob","rating":4}]}');Nested query: Find the same comment that has
author=aliceandrating>=5.SELECT k, j1 FROM HYBRID_SEARCH( TABLE movie_rating, '{ "query": { "nested": { "path": "j1.comments", "query": { "bool": { "must": [ {"term": {"j1.comments.author": "alice"}}, {"range": {"j1.comments.rating": {"gte": 5}}} ] } } } } }' );The expected result is only
k=2: in Document 1,aliceand the rating5are not on the same comment.
References
- This feature is mainly used in hybrid search scenarios. For related scenario examples, see Hybrid search.
- For supplementary information on asynchronous full-text indexing and table-level refresh parameters, see the "Asynchronous full-text indexing" section in Full-text search.
- For the syntax of nested queries in hybrid search, see HYBRID_SEARCH.
- For complete syntax and parameter descriptions, see CREATE INDEX.
- For complete syntax and parameter descriptions, see CREATE TABLE.
- For complete syntax and parameter descriptions, see ALTER TABLE.