IDS Forum

Re: index compression

Posted By: John Miller iii
Date: Monday, 16 February 2015, at 10:58 a.m.

In Response To: index compression (FRANK)

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.
>

Messages In This Thread

• index compression
  FRANK -- Friday, 13 February 2015, at 11:52 a.m.
  • Re: index compression
    Art Kagel -- Friday, 13 February 2015, at 12:12 p.m.
  • Re: index compression
    John Miller iii -- Monday, 16 February 2015, at 10:58 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.