indexing varchars

Posted in 2009

alex.e.c — Tue, 24 Mar 2009 09:25:39 -0700 (PDT) — source: Usenet: comp.databases.informix

I have a table that has a varchar(40) column on it. This is likely to contains sequential integers. I want to index this column, but given that the beginning of the index will have little variance, will my index be any good? Will Informix hash the values in the index? Am I worrying about nothing?

↪ replying to alex.e.c

Art Kagel — Tue, 24 Mar 2009 14:37:00 -0400 — source: Usenet: comp.databases.informix

Sequential integers as in a character string of "1234567890...." etc or binary integers packed into the varchar column? If the filter value of the strings are good, it high uniqueness/low duplication count then an index will work fine. If reversing the strings would improve the seach speed by allowing for short circuiting the string comparisons, you can make it an index on a function returning the string reversed. Of course that assumes that your IDS version supports functional indexes (9.30 or later)! It's always a good idea to post your version and platform information when you post a question! Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Mar 24, 2009 at 12:25 PM, alex.e.c <alex.e.c@gmail.com> wrote: > I have a table that has a varchar(40) column on it. This is likely to > contains sequential integers. I want to index this column, but given > that the beginning of the index will have little variance, will my > index be any good? Will Informix hash the values in the index? Am I > worrying about nothing? > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

↪ replying to alex.e.c

Obnoxio The Clown — Thu, 26 Mar 2009 14:15:17 +0000 — source: Usenet: comp.databases.informix

alex.e.c wrote: > I have a table that has a varchar(40) column on it. This is likely to > contains sequential integers. I want to index this column, Don't. And don't use varchars unless your data length really does vary wildly. It's just a pointless overhead. -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.