Re: Are indexes used with LIKE?

Thank you for the answer!

Sure, the possiblity of having a separate column for each flag was
considered, but a common columnn is preferred -- I do not remember
exactly why. (I do not directly make that decision.) I guess the main
reason is that adding new columns to the table complicates the upgrade
procedure with our existing customer base.

Thank you again!

Peter

Michael Fuhr wrote:
> On Mon, Jan 23, 2006 at 08:00:01PM +0100, Kovcs Pter wrote:
>
>>Are indexes on VARCHAR columns used with the LIKE operator, and if so,
>>how efficiently are they used?
>>
>>I can imagine that using indexes can be easy with the starting literal
>>characters up to the first percent sign such as in:
>>
>>LIKE 'ZOE%QQWE%'
>>
>>But, after the first % sign, things can get more difficult.
>
>
> The planner can use an index on the starting literal characters;
> how "difficult" the query becomes after that depends on how
> discriminating those initial characters are. If values matching
> the initial characters comprise a small fraction of the table then
> the query will probably use an index and be fast, but if they
> comprise a large fraction of the table, or if the search string
> starts with a wildcard, then you'll get a sequential scan, which
> might be slow.
>
>
>>The reason I am asking is that we are thinking about discriminating
>>between rows of a table based on a VARCHAR column containing various
>>one-character "flags". We could then use the LIKE operator for
>>formulating filter conditions.
>
>
> Have you considered putting each flag in a separate column and
> indexing those columns? If you're using 8.1 the planner would
> probably use bitmap index scans and come up with a fast plan
> regardless of which columns you restrict on. And performance issues
> aside, some people would consider that a better design. However,
> a disadvantage might be that your queries would be more complex.
>