ApsaraDB for SelectDB:Inverted index

How it works

In Alibaba Cloud SelectDB, the inverted index treats a table row as a document and a column as a field. This enables the inverted index to quickly locate rows containing a specific keyword, improving the performance of queries that use a WHERE clause.

Unlike a regular index, an inverted index is stored in a separate inverted file. This file is logically mapped to the segment file but is not integrated with it. This approach avoids rewriting the segment file for index updates and deletions, significantly reducing processing overhead.

Scenarios

• Accelerates full-text search on string data types.

• Accelerates =, !=, >, >=, <, <= filtering for string, numeric, and datetime types.

Benefits

• Comprehensive support for logical operators.

  • Added support for index pushdown of OR and NOT logic.

  • Supports any AND, OR, and NOT combination of multiple conditions.

• Flexible and fast index management.

  • Create an inverted index when creating a table.

  • Add an inverted index to an existing table.

  • Delete an inverted index from a table.

Limitations

• The FLOAT and DOUBLE floating-point data types do not support an inverted index due to precision issues. Instead, use the DECIMAL fixed-point data type, which supports an inverted index.

• Some complex data types do not support an inverted index, including MAP, STRUCT, JSON, HLL, BITMAP, QUANTILE_STATE, and AGG_STATE. To use an inverted index with JSON data, convert the column to the VARIANT data type.

• You can create an inverted index on a field of a numeric type, but you cannot specify a parser, such as english, chinese, or unicode.

• The DUPLICATE model and the UNIQUE model with Merge on Write enabled support an inverted index on any column. In contrast, the AGGREGATE model and the UNIQUE model with Merge on Write disabled support an inverted index only on a Key column. Because these models must read and merge all data, the system cannot use the index for pre-filtering.

Create index on table creation

This is a synchronous operation. Index creation completes when the table is successfully created.

Syntax

CREATE TABLE  [IF NOT EXISTS] [db_name.]<table_name>
(
  <column_definition_list>,
  [<index_definition_list>] 
)
table_properties;

Parameters

Table creation parameters

index_definition_list

You can define one or more indexes when you create a table. Use the following format: index_definition[, index_definition][, index_definition]....

index_definition

INDEX <index_name>(<column_name>) <index_type> [PROPERTIES("<key>" = "<value>")] [COMMENT '<comment>']

Parameters

Example: Create a table with an index

-- Create a table and an inverted index named idx_comment on the comment column.
-- USING INVERTED sets the index type to inverted index.
-- PROPERTIES("parser" = "english") sets the tokenizer to "english". Other supported tokenizers include "chinese" for Chinese text and "unicode" for mixed-language text. If the "parser" property is omitted, no tokenization occurs.
CREATE TABLE hackernews_1m
(
    `id` BIGINT,
    `deleted` TINYINT,
    `type` String,
    `author` String,
    `timestamp` DateTimeV2,
    `comment` String,
    `dead` TINYINT,
    `parent` BIGINT,
    `poll` BIGINT,
    `children` Array<BIGINT>,
    `url` String,
    `score` INT,
    `title` String,
    `parts` Array<INT>,
    `descendants` INT,
    INDEX idx_comment (`comment`) USING INVERTED PROPERTIES("parser" = "english") COMMENT 'inverted index for comment'
)
DUPLICATE KEY(`id`)
DISTRIBUTED BY HASH(`id`) BUCKETS 10;

Add an index

This operation is an asynchronous operation. You can check the index creation progress by using SHOW ALTER TABLE COLUMN;.

Syntax

ALTER TABLE <table_name> ADD INDEX <index_name>(<column_name>) <index_type> [PROPERTIES("<key>" = "<value>")];

Parameters

The parameters are the same as those for creating a table.

Examples

Add an index without tokenization.

ALTER TABLE user_tb ADD INDEX index_userId(user_id) USING INVERTED ;

Add an index that uses english for tokenization.

ALTER TABLE user_tb ADD INDEX index_city(city) USING INVERTED PROPERTIES("parser" = "english");

View inverted indexes

View index change progress

Modifying an inverted index using the ALTER and DROP commands is an asynchronous process. To view its progress, use the following statement.

SHOW ALTER TABLE COLUMN;

View all inverted table indexes

Syntax

SHOW INDEXES FROM <table_name>;

Example

SHOW INDEXES FROM user_tb;

Drop an index

Dropping an index is an asynchronous process. To check the progress, see Query the information about inverted indexes.

Syntax

-- Syntax 1
DROP INDEX <index_name> ON <table_name>;
-- Syntax 2
ALTER TABLE <table_name> DROP INDEX <index_name>;

Example

DROP INDEX index_userId ON user_tb;
ALTER TABLE user_tb DROP INDEX index_city;

Full-text search

Syntax

SELECT * FROM <table_name> WHERE <column_name> <conditional_logic> '<keywords>';

Parameters

Examples

-- Retrieve rows where logmsg contains keyword1.
SELECT * FROM log_tb WHERE logmsg MATCH_ANY 'keyword1';

-- Retrieve rows where logmsg contains keyword1 or keyword2.
SELECT * FROM log_tb WHERE logmsg MATCH_ANY 'keyword1 keyword2';

-- Retrieve rows where logmsg contains both keyword1 and keyword2.
SELECT * FROM log_tb WHERE logmsg MATCH_ALL 'keyword1 keyword2';

-- Retrieve rows where logmsg contains the exact phrase "keyword1 keyword2".
SELECT * FROM log_tb WHERE logmsg MATCH_PHRASE 'keyword1 keyword2';

Equality and range queries for numeric and date

The query syntax is standard SQL syntax.

Example

-- Equality, range, IN, and NOT IN queries
SELECT * FROM user_tb WHERE id = 123;
SELECT * FROM user_tb WHERE ts > '2023-01-01 00:00:00';
SELECT * FROM user_tb WHERE op_type IN ('add', 'delete');

Query performance comparison

This topic uses a hackernews dataset with 1 million rows to compare query performance with and without an inverted index.

Tokenize function

The TOKENIZE function breaks a text string into a sequence of terms. Tokenization is a core component for building and using an inverted index. The quality of tokenization directly impacts index performance.

To see how a text string is tokenized, use the TOKENIZE function to view the result. The TOKENIZE function has two main parameters: parser and parser_mode. The following table describes these parameters.

Examples:

-- English tokenization result.
SELECT TOKENIZE('I love CHINA','"parser"="english"');
+------------------------------------------------+
| tokenize('I love CHINA', '"parser"="english"') |
+------------------------------------------------+
| ["i", "love", "china"]                         |
+------------------------------------------------+
1 row in set (0.02 sec)

-- Fine-grained tokenization result from the Chinese tokenizer.
SELECT TOKENIZE('武汉长江大桥','"parser"="chinese","parser_mode"="fine_grained"');
+-----------------------------------------------------------------------------------+
| tokenize('武汉长江大桥', '"parser"="chinese","parser_mode"="fine_grained"')       |
+-----------------------------------------------------------------------------------+
| ["武汉", "武汉长江大桥", "长江", "长江大桥", "大桥"]                              |
+-----------------------------------------------------------------------------------+
1 row in set (0.02 sec)

-- Coarse-grained tokenization result from the Chinese tokenizer.
SELECT TOKENIZE('武汉市长江大桥','"parser"="chinese","parser_mode"="coarse_grained"');
+----------------------------------------------------------------------------------------+
| tokenize('武汉市长江大桥', '"parser"="chinese","parser_mode"="coarse_grained"')        |
+----------------------------------------------------------------------------------------+
| ["武汉市", "长江大桥"]                                                                 |
+----------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

-- Mixed-language tokenization result.
SELECT TOKENIZE('I love CHINA 我爱我的祖国','"parser"="unicode"');
+-------------------------------------------------------------------+
| tokenize('I love CHINA 我爱我的祖国', '"parser"="unicode"')       |
+-------------------------------------------------------------------+
| ["i", "love", "china", "我", "爱", "我", "的", "祖", "国"]        |
+-------------------------------------------------------------------+
1 row in set (0.02 sec)