Does a substring search with LIKE benefit from an index?

Question

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:%'

score 1 · Accepted Answer · answered Apr 10 '20 at 20:19

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
…

1 Answers1