At 06:01 PM 11/30/2004, andrew_s_vaz wrote:

> > where name like '%a'
> >
> > can not use the index, and so doesn't.
>
>Any known way so the <like '%a'> can use an index? Or simply it just
>can't use it?

One of the questions the optimizer must answer when choosing to
use an index is whether (or not) the index is likely to make
retrievals faster. Remember that Firebird does not store records
in the index, but on separate data pages. The fastest way to
read all the records in a table (by far) is to read the data
pages. The restriction "like '%a%'" will probably return most
of the rows in a table, so the fastest way to get the results
is to skip the index and just read the data pages.

The restriction "like '%abcdef%'" is a better candidate for an
index lookup, but it runs into a different problem. The index
lookup code is designed to identify a contiguous set of matches,
not to scan the whole index, expanding the each key, checking
for a match, and continuing. Thus it can find 'abc%' because
that includes everything from 'abc' up to but not including
'abd'.

The answer, for your application, may be to find a text indexing
package for Firebird - there are some, though I don't have a
pointer at the moment.


Regards,


Ann