Index types

Every column in CrateDB is indexed by default using the plain index method. You can choose from three index configurations when defining a table:

1. Disable Indexing

2. Plain Index (Default)

3. *Fulltext Index with Analyzer

Creating an index after a table is created is not supported. Plan your indexing strategy before defining your schema.

---

Disable Indexing

Use INDEX OFF to turn off indexing for a column:

CREATE TABLE table_a (
  first_column TEXT INDEX OFF
);

Effects:

• Reduces disk usage.

• May significantly slow down queries on that column.

• Not allowed on partition columns, as they are stored differently.

---

Plain Index (Default)

A plain index indexes the raw value as-is, without tokenization or analysis.

sqlCopierModifier-- Explicit declaration
CREATE TABLE table_b1 (
  first_column TEXT INDEX USING plain
);

-- Equivalent default behavior
CREATE TABLE table_b2 (
  first_column TEXT
);

Plain indexing is best for exact-match queries.

---

Fulltext Index with Analyzer

A fulltext index stores analyzed tokens instead of raw text, enabling advanced text search capabilities. The analyzer defines how text is broken into tokens, lowercased, stemmed, etc.

If no analyzer is specified, CrateDB uses the standard analyzer.

-- Using default analyzer
CREATE TABLE table_c (
  first_column TEXT INDEX USING fulltext
);

-- Using a specific analyzer
CREATE TABLE table_d (
  first_column TEXT INDEX USING fulltext WITH (analyzer = 'english')
);

Note: With fulltext indices, exact-match queries on the original text may not work as expected. See Built-in analyzers for available options.