At 02:48 PM 31/12/2003 +0100, you wrote:

>Hi,
>
>
> I've to synchronies an X.500 (LDAP) Directory with some DB tables.
> Keys in X.500 are DNs with more than 100 chars but with a very hi
> difference in the first chars (there are the names, followed by
> organisations).
>
> I can't change the synchronization order from db to LDAP as there
> are more targets for data than the db.
>
> So I've build a trigger to copy 84 chars (longest varchar in
> ISO8859_1) into another field on which I've put an index.
>
> Problem: In Firebird until 1.5 I've found nothing to use this
> index on my DN-Field in Database. Therefore every SELECT on an DN
> causes about 40.000 unindexed reads and also the method
>
>
> SELECT "Id" from USERS WHERE SUBSTRING(:LDAPDN from 1 for 84) =
> USERS.SHORTDN AND :LDAPDN = USERS.DN
>
> has the same result.
>
> How can I get firebird to use my index? Subselect? Or do I 've to
> cache al the data in perl hashes? :-(

The reason this query won't use the index is that the second search
condition requires an unindexed read. By including it, you are obviating
the usefulness of the proxy search column you have set up. It is
redundantly reading both columns when USERS.DN alone would find it in an
unindexed read.

Could you take the company portion of USERS.DN and make a second search
column? Then a compound index on the two columns in the correct
left-to-right order would be used.

My other comment is that you are forcing extra cycles on this query by
calling a server-side function to pre-process the search parameter. Have
your application process the input string to extract the (two) search
parameters and pass them "ready-to-roll".

/heLen