At 11:07 AM 18/03/2004 +0200, you wrote:

>Thanks Helen.
>
>Would it help if I :
>No.1 Use the CONTAINING clause instead of LIKE ('% %') ?

Well, CONTAINING can't use indexes either. But it is case insensitive.

>No.2 In my program convert my search terms to uppercase and leave out one
>UPPER
>eg. UPPER(W.WPName) CONTAINING ('OEDER')

That still won't use an index - it's an expression, not a column.
But...
What we users of IBObjects do is we define "proxy search columns" for high
selectivity string-things like names and places. They are the same
specification as the column they "proxy" but we add a Before Insert and
Before Update trigger like this:

if (new.ColumnA is not null) then
new.ProxyA = upper(new.ColumnA);

Then, we index the *proxy* column instead of the actual data
column. Native IBO supports this technique by providing a property to
appoint a proxy search column for a column. So our case-insensitive
searches are a cinch.

>Can I generate a PLAN to actually speed up this query.

See below.

>You said : " W.CityVBSQ = 200 /* will use an index but no good if
>selectivity is bad */"
>This is the index on the foreign key how do I see if it is good or bad ?

It's pretty bad. As Arno commented, it will probably be slower with this
index than without it.

> Index RDB$FOREIGN7 (5)
> Depth: 2, leaf buckets: 109, nodes: 73415
> Average data length: 0.00, total dup: 73205, max dup: 26867

This is the "demographics" of the index. You have approx. 73215 indexed
rows (nodes), of which all but 10 are duplicated (total dup.). Of those
duplicated groups, the biggest group has nearly 27,000 identical
nodes. That is called a "long duplicate chain" and that's the
characteristic that makes low selectivity a bad thing in indexes. There
may be at least one or two more long chains in there, as well. The effect
will be that searching the index will be slower than the alternative, a
natural sort, since the engine is good at these.

> Fill distribution:
> 0 - 19% = 0
> 20 - 39% = 0
> 40 - 59% = 0
> 60 - 79% = 0
> 80 - 99% = 109
>
>You said for WPName and WPDescription indexes will not help. Would indexes
>actually help if I changed the search criteria to:
>
>UPPER(W.WPName) LIKE ('OEDER%')
>or
>UPPER(W.WPName) LIKE ('% OEDER%')
>
>so that the database engine can determine that a word starts with
>, looking at example above , 'OEDER' ?

Like 'OEDER%' will be resolved to STARTING WITH 'OEDER' and the engine WILL
use an index on this. But UPPER(W.WPName) is an expression, so there's
nothing about that which can be used.

/heLen