Pattern Search in ScyllaDB¶

What Is Pattern Search¶

Pattern Search finds the rows whose text column matches a LIKE pattern, such as '%abc%', 'abc%' or 'a%b', but served by an index rather than by a scan of the table. The index node does not match approximately: a value matches when the whole of it matches the pattern. The pattern has the same syntax as in a filtered LIKE, with the same %, _ and \ rules, but the query does not behave like a filtered one in other respects; see Indexed vs. Filtered LIKE.

It suits values that are searched by any part of them rather than by whole words, such as product codes, identifiers and tags. For example, with the table below:

code
------------
AB-1024-XL
AB-2048-S
ab-4096-M
XAB-1024
CD-1024-AB

• code LIKE 'AB-%' returns AB-1024-XL and AB-2048-S, and also ab-4096-M on an index created with 'case_sensitive': 'false'. It does not return XAB-1024 or CD-1024-AB, which contain AB but do not start with AB-.

• code LIKE '%024%' returns AB-1024-XL, XAB-1024 and CD-1024-AB. It matches inside 1024, which a word-based search would not find.

• code LIKE '%-_' returns AB-2048-S and ab-4096-M, the codes that end with a one-character suffix.

A row either matches the pattern or it does not, and no row is ranked above another: CD-1024-AB, with AB at its end, is as much a result of code LIKE '%AB%' as AB-1024-XL. When more rows match than the LIMIT, which of them are returned is not specified. A query can also return fewer rows than its LIMIT, even when more rows match, if a row was deleted after the index found it.

Pattern Search does not tokenize, stem or rank. For word-based search ranked by relevance, use Full-Text Search.

Creating a Pattern Index¶

A pattern_index on a column serves the LIKE restrictions on that column. Such a query does not need ALLOW FILTERING; if it is given, it is ignored and the index is still used. To create the index:

CREATE CUSTOM INDEX ON ks.parts (code) USING 'pattern_index';

or, to match regardless of letter case:

CREATE CUSTOM INDEX ON ks.parts (code) USING 'pattern_index'
    WITH OPTIONS = {'case_sensitive': 'false'};

For the option and its default, see Pattern Index.

Requirements:

• The indexed column must be of type text, varchar, or ascii.

• The indexed column must be a regular column. Primary-key and static columns cannot be indexed.

• The table must use tablets (not vnodes).

• CDC must be enabled on the table with a TTL of at least 86400 seconds (24 hours) and either delta = 'full' or postimage enabled, because the index is updated from the CDC log. CDC is enabled automatically when creating a pattern index, so you do not need to configure it manually.

Cell-level TTL, set with the standard USING TTL syntax on INSERT or UPDATE, makes the value unreadable at its expiration deadline but does not generate a CDC event, so the pattern index is not updated and keeps a stale entry for that value. A LIKE query can then still return the row, with the expired value read as null. If the index must reflect expiration, use the Per-row TTL feature instead.

Querying with LIKE¶

On a column with a pattern index, LIKE queries look like this:

SELECT id, code FROM ks.parts
    WHERE code LIKE '%024%'
    LIMIT 10;

SELECT id, code FROM ks.parts
    WHERE code LIKE 'AB-%'
    LIMIT 10;

SELECT id, code FROM ks.parts
    WHERE code LIKE ?
    LIMIT 10;

Creating the index is a deliberate choice to serve every LIKE on the column from the index, even with ALLOW FILTERING. The full rules are in Indexed LIKE queries.

Dropping a Pattern Index¶

Dropping the index restores filtered LIKE on its column:

DROP INDEX ks.parts_code_idx;

From then on, a LIKE on the column is a filter again: it requires ALLOW FILTERING, matches case-sensitively, and follows the rules of LIKE Operator. Dropping the index is the way back when the indexed LIKE does not suit an application that queries the column.