Hi Everyone--

thanks for the responses. i really appreciate it!

i was unaware of "containing" and it sounds like that might work for me.

it makes sense "like" "starting" and "containing" do not use indexes.

and just to make sure I understand....

not all my queries are built with "like"

i do have many that use indexes and are done with "=" or ">" and what not. these use indexes if there is one on the field automatically (correct?).

and if I understand this thread properly...if I:

where
Manufacturer = 'Consolidated'

it will use the index build on the Manufacturer field automatically.

BUT if I use this to make it case insensitive then it does NOT use the index

where
upper(Manufacturer) = 'CONSOLIDATED'

and if i use this, it too is case insensitive and does not use the index (but the where clause is slightly different)

where
Manufacturer containing 'consolidated'

it seems like using the case insensitive character set is not a solution to me because the tables are all already built.

thanks again!
kelly

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:
>
> Hi, I'm mostly repeating what others have answered before, just trying to emphasize and put things together in one email:
>
> a) If you use an uppercase collation or an expression index, then an uppercased search can be indexed.
>
> b) LIKE does not use an index.
> The only case where LIKE can use an index, is where you use LIKE <constant> and <constant> starts with a fixed value. If you use a parameter rather than a constant, or the constant starts with a wildcard (like your examples do), then no index can be used.
>
> So your two examples will both be slow when you have lots (millions) of records in your table. An alternative to using "where upper(field) like '%UPPERCASE LOOK FOR%'" is "where field containing 'LOOK FOR'". Unlike LIKE, CONTAINING is case insensitive. However, it still cannot use any index.
>
> Set
>
>