bwc3068 wrote:

> 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.
>

LIKE would use an index if the first character is not a wildcard, and
that if you use constants (not parameters with prepared queries)

> 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.
>

There is a lot more inside FB optimizer to decide if an index is good or
not to be used, but as a general rule you are correct, the index will be
used

> BUT if I use this to make it case insensitive then it does NOT use the index
>
> where
> upper(Manufacturer) = 'CONSOLIDATED'
>

Unless you have an expression index on upper(Manufacturer)

> 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'
>

yes

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

It's not a big deal, it's very easy to extract the metadata, change the
domains to use a case/accent insensitive collation, and pump the data over.

If you don't care about speed go if CONTAINING

note that

where
upper(Manufacturer) = 'DATE'

will return the records where Manufacturer is 'CONSOLIDATED'

> thanks again!
> kelly
>
>

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br