I know there are some nuances to ensuring an index is used with 'Like' searches. 

I would expect this search to use an appropriate index if one is available (hvalue_search has an ascending_index with selectivity recalculated).

SELECT id, hname, hvalue, hvalue_search

from entity_header

where hvalue_search like 'GLX%'

 If I do a search in straight sql, the plan returns this:

'PLAN (ENTITY_HEADER INDEX (I_HEADER_HVALUESEARCH))'

This is the plan used either using a tool like DBWorkbench, or using the python DB-API with the FDB driver.

However, if I try to use a parameterised version of this query (using either the FDB driver or DB Workbench), then the index is ignored and the entire table is scanned.

'PLAN (ENTITY_HEADER NATURAL)'

I assumed at first that this was a problem with the python driver or with the python db-api. So when I tested with DB Workbench I was surprised to see that it seems that firebird will do a full table scan if there is a parameterised query with like.  Is this the normal behaviour?

This is what I am using in DB Workbench to show that the introduction of a parameter causes the full table scan:

SELECT id, hname, hvalue, hvalue_search

from entity_header

where hvalue_search like :1

I can't seem to run a paramterised query within the command line ISQL, so I can't determine if this behaviour is really a limitation of the tools I've been trying or of firebird itself.

Yes, it's the normal behaviour...


A parameterised query will be re-used with any value for the parameter, so the plan is choosen before the value of a given parameter will be set, since there is no way to predict if the parameter would be 'ABC%' or '%ABC%' or '_ABC' the optimizer chooses a plan that will solve all the possibilities, thereafter it can't use a plan that uses an index.

You could use STARTING WITH, this way you will always use an index.

see you !