index compression

IDS12.10 FC4. I am going to try to compress some big indexes with long keys , e.g., VARCHAR(250), with considerable length of duplicate sub-strings among millions of index keys. Want to get some existing experiences if possible. --- after an index got compressed, its performance will be the same, slightly slower or better ? --- How to decide if an index is worth to compress or not? --- Other I should be aware of ? Thanks Frank --001a11c16a908fc02a050efb0f80

Indexes and data both tend to be faster on average compressed than uncompressed. Some operations are a bit slower and require more CPU power. So, if your system is already CPU bound, compression is a bad idea until you move to faster hardware. However, the savings in IO reduction and having each IO move more data into cache usually more than makes up for the time to expand the data. Index keys are not expanded when searching compressed indexes, the filter value is compressed and the compressed keys are searched. A large join hitting the full width of a compressed index can be a bit slower, but in general even indexes are faster compressed. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. 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 Fri, Feb 13, 2015 at 11:52 AM, FRANK <yunyaoqu@gmail.com> wrote: > IDS12.10 FC4. > > I am going to try to compress some big indexes with long keys , e.g., > VARCHAR(250), with considerable length of duplicate sub-strings among > millions of index keys. > > Want to get some existing experiences if possible. > > --- after an index got compressed, its performance will be the same, > slightly slower or better ? > > --- How to decide if an index is worth to compress or not? > > --- Other I should be aware of ? > > Thanks > Frank > > --001a11c16a908fc02a050efb0f80 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0158c51a6988d7050efb5542

Frank: varchars with indexes are the prime candidate for compression. To get the biggest performance boost you would want to convert all indexed varchars columns to chars. You will have better space savings on the data pages and the index page size only the leaf pages get compressed so the index will be slightly larger, but will perform much better for un-cached data. This has to do with the re-evaluation specific to varchar columns. Our tests show that the savings is about 183% faster on key only scans when using compressed char columns versus varchar columns. As the varchar index requires the data page to always be processed, even for key only scans. Just to put a plug in for the IIUG conference that takes place in April, all of this information was present at last years performance tutorial along with the technical reason behind why this happens. It is a great event with many highly technical session and information exchange between fellow informix users is incredible. . John F. Miller III STSM, Lead Architect miller3@us.ibm.com 503-747-1366 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 02/13/2015 08:52:36 AM: > From: "FRANK" <yunyaoqu@gmail.com> > To: ids@iiug.org > Date: 02/13/2015 08:53 AM > Subject: index compression [34663] > Sent by: ids-bounces@iiug.org > > IDS12.10 FC4. > > I am going to try to compress some big indexes with long keys , e.g., > VARCHAR(250), with considerable length of duplicate sub-strings among > millions of index keys. > > Want to get some existing experiences if possible. > > --- after an index got compressed, its performance will be the same, > slightly slower or better ? > > --- How to decide if an index is worth to compress or not? > > --- Other I should be aware of ? > > Thanks > Frank > > --001a11c16a908fc02a050efb0f80 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >