thank you all for the help and clarification on my mistake with "like" when it starts with a letter and it's not a parameter.

regards
kelly

--- In firebird-support@yahoogroups.com, Svein Erling Tysvaer <svein.erling.tysvaer@...> wrote:
>
> STARTING can use an index, CONTAINING cannot, and LIKE cannot unless it
> is a constant value not beginning with a wildcard.
>
> "=" and BETWEEN can use an index, ">" can use an ASCENDING index and "<"
> can use a DESCENDING index (I think, I'm not 100% certain about
> descending indexes - they weren't very good when Firebird was at version
> 1.0).
>
> The reason why CONTAINING and LIKE often cannot use an index, is because
> values of fields in the index is stored from the first character
> onwards (I've only used CONTAINING with CHAR, VARCHAR and BLOB SUBTYPE
> TEXT, to me it doesn't sense to use it with numbers, dates or similar),
> and if you do not know the first character(s), you cannot know where to
> look in the index.
>
> I think you're right in your assumptions regarding
>
> where upper(Manufacturer) = 'CONSOLIDATED'
>
> not using an index. Though, theoretically, if the field is defined with
> a case insensitive collation, then the optimizer could know that
> upper(Manufacturer) would be identical to Manufacturer and use the
> defined index. Likewise, if you defined an expression index for
> upper(Manufacturer), then the above WHERE clause could use an index.
>
> Likewise, if
>
> where Manufacturer containing 'consolidated'
>
> are used and the field is defined as (VAR)CHAR(12), then the optimizer
> could theoretically know that the above clause are identical to using
> '='. If the field is defined as anything else, no index can be used
> because the optimizer cannot know what the first letter(s) of the field are.
>
> I seriously doubt the two theoretical possibilities are or will be
> implemented - mainly because using UPPER or CONTAINING when they are not
> the best choice is an active choice of the developer. That choice may
> either be due to simply not being careful enough when writing SQL or an
> active choice to prevent certain indexes from being used (similar to
> using ||'' or +0). In my opinion, it is not Firebirds responsibility to
> hide developer carelessness.
>
> By the way, no-one has mentioned any 'case insensitive character set',
> we have talked about case insensitive collations. A character set can
> have many possible collations, some case insensitive, some case
> sensitive. Not using any character set at all can have undesired effects
> - I've always used ISO8859_1 and have no knowledge of what undesired
> effects I could have experienced if I didn't specify the character set.
> Maybe others will jump in and explain.
>
> If you use CHARACTER SET ISO8859_1, then the field may either be defined
> with a case insensitive collation or you can specify it in your select, e.g.
>
> where Manufacturer collate PT_BR = 'CONSOLIDATED'
>
> However, I don't think this will be able to use any index unless you
> define an expression index (though I haven't checked), and the only case
> I've experienced where I've tried to do something similar, was when I
> had a field that contained Norwegian characters but that accidentally
> had been defined without COLLATE NO_NO. Only when using WHERE
> UPPER(MANUFACTURER COLLATE NO_NO) were the 3 special Norwegian
> characters uppercased the same way as the 26 English (translating æøå to
> ÆØÅ).
>
>