CODEWITHSUNDEEP
indexingsql-likehsqldb
user149408
user149408

Reputation: 5881

Does a substring search with LIKE benefit from an index?

Assume the following table definition in HSQLDB:

create table message(id varchar(255) primary key not null, data clob not null);

Will the index which HSQLDB creates automatically on id (being the primary key) speed up a substring search like the following?

select * from message where id like 'foo:%'

Upvotes: 0

Views: 35

Answers (1)

user149408
user149408

Reputation: 5881

Apparently substring searches do benefit from an index on that column.

Running

explain plan for select * from message where id like 'foo:%'

gives me

…
access=INDEX PRED
…

just like for a simple equal sign comparison. This seems to work for any substring, e.g. '%foo%', not just the beginning of a string.

For comparison, if I try the same on the data column (which is not indexed and thus requires a full table scan), I get

…
access=FULL SCAN
…

Upvotes: 1

Related Questions