NGram Bloom filter indexes

LIKE queries on large text columns often require full scans that read every data block. NGram Bloom filter indexes let SelectDB skip blocks that cannot match the search pattern, reducing I/O and accelerating LIKE query performance.

How it works

When you create an NGram Bloom filter index on a column, SelectDB tokenizes each stored value into overlapping character sequences of length gram_size and records them in a Bloom filter. For example, with gram_size=3, the string an ngram produces the tokens 'an ', 'n n', ' ng', 'ngr', 'gra', and 'ram'.

At query time, SelectDB applies the same tokenization to the pattern in the LIKE condition. If any token from the pattern is absent from the Bloom filter for a given data block, that block is skipped entirely.

Prerequisites

Before creating an NGram Bloom filter index, make sure the target column meets these requirements:

The column type is STRING or VARCHAR.
The column has no existing Bloom filter index. An NGram Bloom filter index and a Bloom filter index are mutually exclusive on the same column.

When the index takes effect

An NGram Bloom filter index is applied to a query only when both conditions are met:

The query uses a LIKE predicate.
The number of consecutive characters in the LIKE pattern is greater than or equal to gram_size.

For example, if gram_size=3, the pattern '%ab%' (2 characters) does not activate the index, but '%abc%' (3 characters) does.

Note

The default gram_size is 2 if not specified at index creation.

Create an NGram Bloom filter index

Important

If you omit the PROPERTIES clause, SelectDB uses gram_size=2 and bf_size=256. Set both parameters explicitly based on your query patterns for optimal performance.

When creating a table (synchronous)

Creating an index as part of CREATE TABLE is synchronous — the table and index are created together.

Syntax

CREATE TABLE [IF NOT EXISTS] [db_name.]<table_name>
(
  <column_definition_list>,
  INDEX <index_name>(<column_name>) USING NGRAM_BF
    [PROPERTIES("gram_size" = "<value>", "bf_size" = "<value>")]
    [COMMENT '<comment>']
)
table_properties;

Parameters

Parameter	Required	Description
`db_name`	No	The database where the table is created.
`table_name`	Yes	The name of the table.
`column_definition_list`	Yes	Column definitions.
`table_properties`	Yes	Table properties, including the data model and partitioning and bucketing settings. For more information, see Data models.
`index_name`	Yes	The index name, which must be unique within the table. Use the `idx_` prefix followed by the column name (for example, `idx_review_body`).
`column_name`	Yes	The column to index. A column can have only one NGram Bloom filter index or Bloom filter index.
`USING NGRAM_BF`	Yes	Specifies the NGram Bloom filter index type.

PROPERTIES

Key	Description
`gram_size`	The token length used for splitting column values. Use a value that matches the minimum pattern length in your LIKE queries, with a minimum of `2`. A smaller `gram_size` produces more tokens and increases the false positive rate — compensate by increasing `bf_size`. Setting `gram_size=3` and `bf_size=1024` works well for most use cases.
`bf_size`	The Bloom filter size in bytes per data block. A larger value lowers the false positive rate and reduces unnecessary I/O, but uses more storage and memory. Start with `256` for initial testing.

Example

CREATE TABLE `test_table` (
  `siteid`      int(11)      NULL DEFAULT "10" COMMENT "",
  `citycode`    smallint(6)  NULL COMMENT "",
  `username`    varchar(32)  NULL DEFAULT "" COMMENT "",
  `review_body` varchar(320) NULL,
  INDEX idx_ngrambf (`review_body`) USING NGRAM_BF
    PROPERTIES("gram_size"="3", "bf_size"="256")
    COMMENT 'review_body ngram_bf index'
) ENGINE=OLAP
AGGREGATE KEY(`siteid`, `citycode`, `username`, `review_body`) COMMENT "OLAP"
DISTRIBUTED BY HASH(`siteid`) BUCKETS 10;

On an existing table (asynchronous)

Adding an index to an existing table is asynchronous. Run SHOW ALTER TABLE COLUMN; to check progress.

Syntax

ALTER TABLE <table_name>
  ADD INDEX <index_name>(<column_name>) USING NGRAM_BF
  [PROPERTIES("gram_size" = "<value>", "bf_size" = "<value>")];

Example

ALTER TABLE test_table
  ADD INDEX idx_ngrambf2(username) USING NGRAM_BF
  PROPERTIES("gram_size"="2", "bf_size"="512")
  COMMENT 'username ngram_bf index';

View indexes on a table

Use the following statement to list all indexes and their properties on a table:

SHOW INDEXES FROM <table_name>;

Example

SHOW INDEX FROM test_table;

Delete an NGram Bloom filter index

Warning

Deleting an NGram Bloom filter index degrades LIKE query performance. Proceed with caution.

This operation is asynchronous. To check deletion progress, see Query the information about inverted indexes.

Syntax

ALTER TABLE <table_name> DROP INDEX <index_name>;

Example

ALTER TABLE test_table DROP INDEX idx_ngrambf;

Check index change progress

All ALTER TABLE index operations are asynchronous. Run the following statement to check progress:

SHOW ALTER TABLE COLUMN;

FAQ

How do I verify that an NGram Bloom filter index was used in a query?

Check the query profile for the query. The profile shows which indexes were applied during execution. For details, see Query profile.

I got the following error when adding an NGram Bloom filter index. What does it mean?

ERROR 1105 (HY000): errCode = 2, detailMessage = NGRAM_BF index for columns (review_body) already exist.

A table can have multiple NGram Bloom filter indexes, but each index must be on a different column. A column supports only one NGram Bloom filter index or Bloom filter index. Drop the existing index on the column first, or create the new index on a different column.