Subject | RE: [firebird-support] Indexed used with search name='TEST' but not with name like 'TEST%' |
---|---|
Author | Leyne, Sean |
Post date | 2008-04-25T20:59:43Z |
Stefan,
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
> I have a very large SQL which does a search on a table while joiningArguably there is no problem with Firebird, but in your test.
> 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?
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