The other guys have answered it pretty well, I just thought I would
give an explanation as to why it cant use the index. An index is sort
of like storing a list of page numbers in alphabetical order. When
you know what you are looking for, you can jump to it in the index,
and find out really quickly where the information is located.

Think of the index in a phone directory.

If I said

select *
from phonebook
where name = 'Abi'

I could efficiently use the name index.

Similarlily

select *
from phonebook
where name = 'Abi%'

(all names starting with Abi) could efficiently use the name index as
well, I could just stop when I got to Abj.

But

select *
from phonebook
where name = '%Abi'

might have

'Abi' or 'DAbi', or 'Pabi' or 'Xabi'

We can not look at that index and say to ourselves "well it isn't
possible to get another record with that pattern". With the first two
queries, you could know once you got past Abi that no further
occurance was possible. You would need to check every record to see
if the pattern occurred. So in this third case, the index is of no
help, so Firebird just does the table scan.








--- In firebird-support@yahoogroups.com, "adiw_db" <adiw_db@y...>
wrote:

>
>
> Hi, I have one table that contains 110.000 records.
>
> I ran a query to this table :
> select knokat from lib_judul where k245a like '%MATEMATIKA%'
> But it doesn't use available indexes (both fields are indexed).
> PLAN (LIB_JUDUL NATURAL)
> It took almost 14 seconds to finish.
>
> If I changed the query :
> select knokat from lib_judul where k245a like 'MATEMATIKA'
> or
> select knokat from lib_judul where k245a = 'MATEMATIKA'
> It used index, and finished less than a second.
> PLAN (LIB_JUDUL INDEX (IDX_LIB_JUDUL_K245A))
>
> I tried at FB1.0 on linux and FB1.5 on WinXP Pro.
>
> Can someone explain to me how to use index?
>
> Thank you.
> Adi.