Subject Re: [firebird-support] Quick search on large Varchar?
Author Helen Borrie
At 03:41 PM 2/01/2004 +0100, you wrote:
>HB> The reason this query won't use the index is that the second search
>HB> condition requires an unindexed read. By including it, you are obviating
>HB> the usefulness of the proxy search column you have set up. It is
>HB> redundantly reading both columns when USERS.DN alone would find it in an
>HB> unindexed read.
>Yes, I thought that. But could a subselect work?

What did you have in mind?

>HB> Could you take the company portion of USERS.DN and make a second search
>HB> column? Then a compound index on the two columns in the correct
>HB> left-to-right order would be used.
>No, not realy. The company part includes office etc. and ist up to
>2000 chars long.
>So I'd to build up to 24 index-columns?

Hmm, this sounds a rather suspect thing to be searching on. I pass.

>HB> My other comment is that you are forcing extra cycles on this query by
>HB> calling a server-side function to pre-process the search
>parameter. Have
>HB> your application process the input string to extract the (two) search
>HB> parameters and pass them "ready-to-roll".
>I fear I don't understand exactly what you mean with this.

You are passing the whole search argument as a parameter and then applying
the SUBSTRING() function to it. If you want to search only on a part of
the argument, use your client to calculate the substring for the search