Em 15/1/2014 10:13, jamesjlovel...@gmail.com escreveu:
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 !
