Another thing I do when I need large searchable varchars it to have
second OCTETS field. Each time I insert or update a string in the field,
I create an MD5 and store it in the OCTETS field. Then I just use this
for searching. Seems to work well. Usually, inserting or updating is the
result of a user action, so I don't mind the hit required to generate
the MD5. Anyway, MD5 creation is pretty fast. This is usually better
(and more unique) than using a substring.

Just a thought,

R.

-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: Wednesday, December 31, 2003 12:44 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Quick search on large Varchar?


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




Yahoo! Groups Links

To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.