Using index on `LIKE :varname || '%'` in firebird

Question

I have a query

SELECT DISTINCT FKDOCUMENT
FROM PNTM_DOCUMENTS_FT_INDEX
WHERE WORD LIKE 'sometext%'

PLAN SORT ((PNTM_DOCUMENTS_FT_INDEX INDEX (IX_PNTM_DOCUMENTS_FT_INDEX)))

And it works okay.

Howver, when I try to use concatenated string with LIKE, Firebird does not use indices:

SELECT DISTINCT FKDOCUMENT
FROM PNTM_DOCUMENTS_FT_INDEX
WHERE WORD LIKE 'sometext' || '%'

PLAN SORT ((PNTM_DOCUMENTS_FT_INDEX NATURAL))

How to force it to use indices?

Answer 1

The short answer, as ain already commented, is to use STARTING [WITH] instead of LIKE if you don't need a like pattern, but always want to do a prefix search. So:

WHERE WORD STARTING WITH 'sometext' -- No %!

or

WHERE WORD STARTING WITH :param

As far as I know this is exactly what Firebird does with LIKE 'sometext%'. This will use an index when available, and you don't need to escape it for presence of like pattern symbols. The downside is that you can't use like pattern symbols.

Now as to why Firebird doesn't use an index when you use

WHERE WORD LIKE :param || '%' -- (or LIKE :param) for that matter

or

WHERE WORD LIKE 'sometext' || '%'

The first case is easily explained: statement preparation is done separately from execution. Firebird needs to take into account the possibility that the parameter value starts with a _ or - worse - a %, and it can't use an index for that.

As to the second case, it should be possible to optimize it to the equivalent of LIKE 'sometext%', but Firebird probably considers anything that is not a plain literal as not optimizable. For this specific example it would be possible to decide it should be optimizable, but this a very specific exception (usually one doesn't concatenate literals like this, most of the time one or more 'black' boxes like columns, functions, case statements etc are involved).