Building an Index on a Substring

Environment: IDS 7.30UC5, Solaris 2.6 I'd like to build a very compact and efficient index on the first "n" characters of a varchar column. The table has 12 million records in it, and I don't need very precise or exact searching - although I do need high performance. Is there a way to accomplish this? I know I could add a new column to this table that just contains those "n" characters, but that seems like a hack. The informix "create index" command, of course, won't permit me to use substring syntax, i.e., "create index foo on bar(name[1,5]);". Does anyone know of other good-quality options? Many thanks ... Rich -- Richard C. Auslander Database Manager AirFlash, Inc. 1733 Woodside Rd., Suite #110 Redwood City, CA 94061 (650) 556-7928 www.airflash.com

Given that you are interested in a leading portion of the column, why not just create an index on the whole column? SQL such as the following should be quite quick: select * from bar where name like ("abcde%"); This would only be a problem if name is a very wide column and you do not want to waste the extra space. Jay Buckler Richard Auslander <rich@airflash.com> wrote in message news:3887E491.E3D982C6@airflash.com... > Environment: IDS 7.30UC5, Solaris 2.6 > > I'd like to build a very compact and efficient index on the first "n" > characters of a varchar column. The table has 12 million records in it, > and I don't need very precise or exact searching - although I do need > high performance. Is there a way to accomplish this? I know I could > add a new column to this table that just contains those "n" characters, > but that seems like a hack. The informix "create index" command, of > course, won't permit me to use substring syntax, i.e., "create index foo > on bar(name[1,5]);". Does anyone know of other good-quality options? > Many thanks ... > > Rich > -- > Richard C. Auslander > Database Manager > > AirFlash, Inc. > 1733 Woodside Rd., Suite #110 > Redwood City, CA 94061 > (650) 556-7928 > > www.airflash.com > >