Subject RE: [firebird-support] Indexed used with search name='TEST' but not with name like 'TEST%'
Author Leyne, Sean
Stefan,

> I have a very large SQL which does a search on a table while joining
> over several other tables. All tables contains millions of rows. The
> SQL does a search on a table WORD with column name='TEST'. All
> entries in the column are in uppercase and indexed. The SQL runs in 3
> seconds when I search name='TEST' but if I search for name
> like 'TEST%' it runs 15 minutes. The plan analyszer shows that in the
> second case no index is used. I teste it fpr 2.0.3 and 2.1. Uner
> Oracle 9i or SQL-Server 2005 both selects runs in about some seconds.
>
> What is the problem in Firebird?

Arguably there is no problem with Firebird, but in your test.

Firebird will use indexes for these SQL statements:

SELECT ... WHERE NAME = 'TEST'

SELECT ... WHERE NAME LIKE 'TEST%'


Firebird will also use indexes for a prepared query like:

SELECT ... WHERE NAME = :Parm


But Firebird will *not* use an index for a prepared query like:

SELECT ... WHERE NAME LIKE :Parm


This is a known/documented limitation.


Sean