Full-text inverted index

Limitations

Supported only for instances of version 3.3.13-1.0.0 or later.
Supported only for duplicate key tables and primary key tables.
Inverted indexes can be created only for columns of type CHAR, VARCHAR, and STRING.
For shared-data instances, you must set the table property replicated_storage=false.

Create full-text inverted index

On the Parameter Configuration tab of the StarRocks console, set the FE configuration property enable_experimental_gin to true to enable the full-text inverted index.

On the SQL Editor tab, create a full-text inverted index using one of the following methods.

At table creation

This example creates a full-text inverted index on column v using the English tokenizer in a shared-data instance.

CREATE TABLE `t` (
  `k` BIGINT NOT NULL COMMENT "",
  `v` STRING COMMENT "",
   INDEX idx (v) USING GIN("parser" = "english")
) ENGINE=OLAP 
DUPLICATE KEY(`k`)
DISTRIBUTED BY HASH(`k`) BUCKETS 1
PROPERTIES (
  "replicated_storage" = "false"
);

On an existing table

Add a full-text inverted index using ALTER TABLE ADD INDEX.

ALTER TABLE t ADD INDEX idx (v) USING GIN('parser' = 'english');

Add a full-text inverted index using CREATE INDEX.

CREATE INDEX idx ON t (v) USING GIN('parser' = 'english');

The basic syntax is as follows.

INDEX <index name>(<column name>) USING GIN(properties)

The parameters are described below.

Parameter	Description
`<index_name>`	The custom name of the index.
`<column_name>`	The column to index.
`USING GIN`	Specifies the index type as GIN (Generalized Inverted Index) for creating an inverted index.
`properties`	Specifies the index configuration parameters as key-value pairs. The following properties are supported: `parser`: Specifies the tokenization method. Supported values are: `none`: Default. Disables tokenization. `english`: Uses CLucene's Simple Analyzer for tokenizing English text. This tokenizer is case-insensitive. `chinese`: Uses CLucene's CJK Analyzer for tokenizing Chinese text. `standard`: Uses CLucene's Standard Analyzer, suitable for most languages. This tokenizer is case-insensitive. `omit_term_freq_and_position`: Specifies whether to omit term frequency and term position when building the inverted index to reduce storage usage. The default is `false`. If set to `true`, phrase match (MATCH_PHRASE) is not supported.

View full-text inverted index

Execute the following command to view the full-text inverted index.
```
SHOW CREATE TABLE testdb.`t`;
```

In the Results area, right-click the Create Table column to preview its content.

This example shows the following information.

CREATE TABLE `t` (
  `k` bigint(20) NOT NULL COMMENT "",
  `v` varchar(65533) NULL COMMENT "",
  INDEX idx (`v`) USING GIN("parser" = "english") COMMENT ''
) ENGINE=OLAP 
DUPLICATE KEY(`k`)
DISTRIBUTED BY HASH(`k`) BUCKETS 1 
PROPERTIES (
"compression" = "LZ4",
"fast_schema_evolution" = "true",
"replicated_storage" = "false",
"replication_num" = "3"
);

Drop full-text inverted index

Drop an index using ALTER TABLE DROP INDEX.
```
ALTER TABLE t DROP index idx;
```
Drop an index using DROP INDEX.
```
DROP INDEX idx on t;
```

Accelerate queries

Note

Before using this feature, ensure that the enable_gin_filter and enable_prune_column_after_index_filter parameters are set to true. By default, both parameters are true.

If the full-text inverted index tokenizes the indexed column (that is, 'parser' = 'standard|english|chinese'), you can only use the MATCH predicate to accelerate queries with the full-text inverted index. The format must be <col_name> (NOT) MATCH '%keyword%'. The keyword must be a string literal and cannot be an expression.

Example setup

create database testdb;
CREATE TABLE testdb.`http_logs` (
  `@timestamp` int(11) NULL COMMENT "",
  `clientip` varchar(20) NULL COMMENT "",
  `request` varchar(65533) NULL COMMENT "",
  `status` int(11) NULL COMMENT "",
  `size` int(11) NULL COMMENT "",
  INDEX request_idx (`request`) USING GIN("parser" = "english") COMMENT ''
) ENGINE=OLAP
DUPLICATE KEY(`@timestamp`)
COMMENT "OLAP"
DISTRIBUTED BY RANDOM BUCKETS 1
PROPERTIES ("replicated_storage" = "false");
insert into testdb.http_logs values
  ('893964617','40.135.*.*','GET /images/hm_bg.jpg HTTP/1.0',200,24736),
  ('893964653','232.0.*.*','GET /images/hm_bg.jpg HTTP/1.0',200,24736),
  ('893964672','26.1.*.*','GET /images/hm_bg.jpg HTTP/1.0',200,24736),
  ('893964679','247.37.*.*','GET /french/splash_inet.html HTTP/1.0',200,3781),
  ('893964682','247.37.*.*','GET /images/hm_nbg.jpg HTTP/1.0',304,0),
  ('893964687','252.37.*.0','GET /images/hm_bg.jpg HTTP/1.0',200,24736),
  ('893964689','247.37.*.*','GET /images/hm_brdl.gif HTTP/1.0',304,0),
  ('893964689','247.37.*.*','GET /images/hm_arw.gif HTTP/1.0',304,0),
  ('893964692','247.37.*.*','GET /images/nav_bg_top.gif HTTP/1.0',200,929),
  ('893964703','247.37.*.*','GET /french/images/nav_venue_off.gif HTTP/1.0',304,0);

Supported query methods

StarRocks provides multiple query methods based on full-text inverted indexes for different use cases:

MATCH/MATCH_ANY
- Semantics: A match occurs with any single token (term) after splitting.
- Example
```
select * from testdb.http_logs where request match "images hm_bg";
```
  The query returns 9 rows. The request column in the results contains paths like /images/hm_bg.jpg, /images/hm_nbg.jpg, /images/hm_brdl.gif, /images/hm_arw.gif, /images/nav_bg_top.gif, and /french/images/nav_venue_off.gif. This indicates that MATCH tokenizes the search string and matches records containing any of the resulting tokens. The status codes in the results are 200 or 304.
MATCH_ALL
- Semantics: Every split term must match.
- Example
```
select * from testdb.http_logs where request match_all "images hm_bg";
```
  The query returns the four records whose request is GET /images/hm_bg.jpg HTTP/1.0. This shows that match_all successfully finds all records where the request field contains both "images" and "hm_bg".
MATCH_PHRASE
- Semantics: Phrase match. The indexed text must contain all tokens from the search string, appearing in the same order.
- Example
```
select * from testdb.http_logs where request match_phrase "GET /images";
```
  The query returns 8 records. The request field in all records contains the phrase GET /images (such as GET /images/hm_bg.jpg HTTP/1.0 and GET /images/hm_nbg.jpg HTTP/1.0), with status codes of 200 or 304. This demonstrates the effectiveness of MATCH_PHRASE for phrase matching.
MATCH_PHRASE_PREFIX
- Semantics: Prefix match. Matches if the indexed text starts with the specified phrase.
- Example
```
select * from testdb.http_logs where request match_phrase_prefix "GET /im";
```
  The query returns 8 records where the request field starts with the prefix GET /im, such as GET /images/hm_bg.jpg HTTP/1.0 and GET /images/hm_nbg.jpg HTTP/1.0.
MATCH_PHRASE_EDGE
- Semantics: A complex match pattern that supports prefix and suffix matching for the ends of the search phrase and exact matching for terms in the middle.
  
  For a search phrase with two or more tokens, StarRocks performs a suffix match on the first token, a prefix match on the last, and an exact match on any intermediate tokens. A successful match occurs only if all tokens match.
- Example
```
select * from testdb.http_logs where request match_phrase_edge 'et images hm';
```
  The query returns 7 records. The request column in these records matches the edge phrase condition 'et images hm', returning results like GET /images/hm_bg.jpg HTTP/1.0 and GET /images/hm_nbg.jpg HTTP/1.0.

Debug and validate inverted index

Verify tokenizer behavior

StarRocks provides the tokenize function to help you verify the behavior of the full-text inverted index. This function shows how the tokenizer processes your input data, which helps you debug queries and optimize conditions.

tokenize("<parser_type>", "<input_strings>");

Parameters:
- <parser_type>: The tokenizer type. This must be consistent with the parser supported by the inverted index.
- <input_strings>: The string or column name to be tokenized.
Return value: Returns an ARRAY where each element is a token.

Example usage

Verify tokenizer behavior

Before creating an inverted index, you can use the tokenize function to test the tokenizer's behavior and ensure that the tokenization result is as expected.
```
SELECT tokenize("english", "GET /images/hm_bg.jpg HTTP/1.0");
```
The command returns the following result.
```
["get","images","hm","bg","jpg","http","1","0"]
```
Debug query results

If a query returns unexpected results, you can use the tokenize function to check how the tokenizer is processing the input data.

Assume that the request column in the http_logs table contains the following data.
```
GET /images/hm_bg.jpg HTTP/1.0
GET /french/splash_inet.html HTTP/1.0
```
Use tokenize to verify the tokenization result.
```
SELECT tokenize("english", request) FROM http_logs;
```
The command returns the following information.
```
["get","images","hm","bg","jpg","http","1","0"]
["get","french","images","nav","venue","off","gif","http","1","0"]
```
Dynamic tokenization

Tokenize column content directly within a query. This is useful for ad-hoc analysis or for quickly viewing tokenization results without first creating an inverted index.
```
SELECT `@timestamp`, tokenize("english", request) AS tokens FROM testdb.http_logs;
```

FAQ

How can I verify that the full-text inverted index is being used?

After running a query, you can open its Query Profile. Under the OLAP_SCAN node, check the GinFilter value within NodeMetrics. This value shows the time the full-text inverted index spent on filtering.