CODEWITHSUNDEEP
indexingh2prefixdatabase-indexes
auntyellow
auntyellow

Reputation: 2573

does h2 database support prefix index?

I have a table with a TEXT/CLOB like column that need to be indexed:

CREATE TABLE test (id INT PRIMARY KEY, val VARCHAR(512))

but this index takes too much spaces:

CREATE INDEX test_val ON test(val)

Can I create an index with only a prefix of this column? like MySQL's prefix index:

CREATE INDEX test_val ON test(val(20))

Upvotes: 1

Views: 1947

Answers (1)

Evgenij Ryazanov
Evgenij Ryazanov

Reputation: 8188

H2 does not support partial indexes in any way. Here is the related feature request: https://github.com/h2database/h2database/issues/2054

H2 supports indexes on generated columns, so you can create a generated column based on your original column (AS SUBSTRING(ORIGINAL_COLUMN FROM 1 FOR 20)) and index it, but you will be required to specify the value of this column in your SELECT queries; otherwise its index will not be used.

Upvotes: 4

Related Questions