Re: indexing and LIKE

On Thu, Oct 11, 2001 at 02:28:34PM +0200, Patrik Kudo wrote:
> Hi!
>
> If I want to be able to search for stringmatches using LIKE, doing
> something like the following:
>
> select id, name from table1 where lower(name) like 'somestring%';
>
> Actually I will be joining with some other table on id too, but the join
> will produce a substancial amount of data to be filtered with the LIKE
> clause so I figure if it'd be possible to index on lower(name) somehow,
> it would result in an appreciated speed gain.
>
> Is it at all possible to create an index on lower(name), and in that case,
> what type of index and using what syntax? Is it possible to create a
> multicolumn index on both id and name? Both id and name are of type
> "text".

Checking the short help from CREATE INDEX:

template1=# \h create index
Command: CREATE INDEX
Description: Constructs a secondary index
Syntax:
CREATE [ UNIQUE ] INDEX index_name ON table
[ USING acc_name ] ( column [ ops_name ] [, ...] )
CREATE [ UNIQUE ] INDEX index_name ON table
[ USING acc_name ] ( func_name( column [, ... ]) [ ops_name ] )

template1=#

So, you want something like:

CREATE INDEX table1_l_name_idx ON table1 (lower(name));

Multicolumn indices are seldom as useful as you may think at first.
And I don't think you can combine them with functional indices.

Ross
--
Ross Reedstrom, Ph.D. reedstrm(at)rice(dot)edu
Executive Director phone: 713-348-6166
Gulf Coast Consortium for Bioinformatics fax: 713-348-6182
Rice University MS-39
Houston, TX 77005