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@rice.edu
Executive Director phone: 713-348-6166
Gulf Coast Consortium for Bioinformatics fax: 713-348-6182
Rice University MS-39
Houston, TX 77005
