Hi,


I’m using a table similar to this:


CREATE TABLE ADDRESS (
ID BIGINT NOT NULL,
FIRST_NAME VARCHAR(64) CHARACTER SET UTF8 COLLATE UNICODE_CI_AI,
LAST_NAME VARCHAR(64) CHARACTER SET UTF8 COLLATE UNICODE_CI_AI;
ALTER TABLE ADDRESS ADD PRIMARY KEY (ID);
CREATE INDEX IDX_ADDRESS ON ADDRESS(LAST_NAME);


There are about 200.000 rows in this table and the following statement
runs, as expected, very fast:


SELECT ID, FIRST_NAME, LAST_NAME
FROM ADDRESS
WHERE ADDRESS.LAST_NAME LIKE 'Smith%';


PLAN (ADDRESS INDEX (IDX_ADDRESS))


Unfortunatly, after implementing the same query as prepared statement in
my application, everything got very, very slow.


SELECT ID, FIRST_NAME, LAST_NAME
FROM ADDRESS
WHERE ADDRESS.LAST_NAME LIKE ?;


Parameter 1 = "Smith%"


PLAN (ADDRESS NATURAL)


I tried to force Firebird to use the correct PLAN, but that didn’t work
either:
“Index IDX_ADDRESS cannot be used in the specified plan.“


Thinking about it, i understand why the optimizer can’t really know what
parameters will be used later on und obviously takes the save route by
omitting the index altogether. But why isn’t it even possible to
manually suggest a PLAN?


Is there really no possible way to somehow convince Firebird to use the
index in this case?
If possible, I really wouldn't want to loose parameters and fiddleing
with user input by hardcoding the queries.




My setup:
JDK 1.6
Firebird-2.5.0.26074-0_Win32_embed
Jaybird-2.2.5-JDK_1.6


Jan