Discussion:
index on varchar
(too old to reply)
t***@gmail.com
2014-02-14 15:59:08 UTC
Permalink
Hello

Are indexes on varchars in Informix "inefficient"?

thanks
Jack Parker
2014-02-14 16:07:59 UTC
Permalink
Yes.

The index is built using the full potential length of the varchar. Which means that a varchar(255) will yield 7 indexes per index page using a 2K page

j.
Post by t***@gmail.com
Hello
Are indexes on varchars in Informix "inefficient"?
thanks
_______________________________________________
Informix-list mailing list
http://www.iiug.org/mailman/listinfo/informix-list
jrenaut
2014-02-24 18:00:11 UTC
Permalink
Post by Jack Parker
Yes.
The index is built using the full potential length of the varchar. Which means that a varchar(255) will yield 7 indexes per index page using a 2K page
j.
Post by t***@gmail.com
Hello
Are indexes on varchars in Informix "inefficient"?
thanks
_______________________________________________
Informix-list mailing list
http://www.iiug.org/mailman/listinfo/informix-list
I don't know if we used to do that, but I just tested 12.10 and 11.50 and that is not true.

my test:

create table v1 (c1 varchar(255)) lock mode row;
create index v1_idx on v1(c1);

When you look at the index page...1st off, I have more then 7 key values, and 2nd off, it's easy to see the length of the slots is not based on the full length of 255.

oncheck -pp 1049353 1
addr stamp chksum nslots flag type frptr frcnt next prev
1:74330 10202079 891e 11 8b0 BTREE 159 1841 0 0
slot ptr len flg
1 37 9 0
2 103 13 0
3 46 9 0
4 75 12 0
5 55 10 0
6 24 13 0
7 127 16 0
8 143 16 0
9 65 10 0
10 87 16 0
11 116 11 0

Jacques Renaut
IBM Informix Advanced Support
APD Team
Jack Parker
2014-02-24 18:42:24 UTC
Permalink
It’s been a while since I tested it. However, the total length of yours keys is 135, + overhead of 9 bytes per index (99 (which includes slots which are really at the end so that should be 5/index)) = 190, Page overhead 24 (or so), I would expect your “next” to therefore be ~214 into the page, not 1841. It’s been a long time since I looked into these pages and my numbers may be off here or there, but you appear to be using about 164 bytes per index.

j.
Post by jrenaut
create table v1 (c1 varchar(255)) lock mode row;
create index v1_idx on v1(c1);
When you look at the index page...1st off, I have more then 7 key values, and 2nd off, it's easy to see the length of the slots is not based on the full length of 255.
oncheck -pp 1049353 1
addr stamp chksum nslots flag type frptr frcnt next prev
1:74330 10202079 891e 11 8b0 BTREE 159 1841 0 0
slot ptr len flg
1 37 9 0
2 103 13 0
3 46 9 0
4 75 12 0
5 55 10 0
6 24 13 0
7 127 16 0
8 143 16 0
9 65 10 0
10 87 16 0
11 116 11 0
Jacques Renaut
IBM Informix Advanced Support
APD Team
_______________________________________________
Informix-list mailing list
http://www.iiug.org/mailman/listinfo/informix-list
Jack Parker
2014-02-24 19:11:44 UTC
Permalink
John points out that I’ve mixed up frptr and freecnt - which is correct, I always think I’m reading courier instead of a kerned font. Ah well, It has been too long.

j.
To add to Jacques answer a varchar index has never
been built on the full length of the varchar in any version. This
adds some complexity to varchar indexes. While
varchar significantly reduce the space a comparable fix
length character index would take, there are complexities
in this. If you really are looking for the BEST space
performance index on character data you need to
utilized fixed length character columns with both
index compression. Index compression keeps
the top level nodes as full length items for fast
comparison, and the leaf level (which contain most
of the index pages) items are compress saving lots
of space.
There was a great talk provided at last years IIUG in
San Deigo on this specific topic, It provided specific
workloads and characteristics of data types. If you
want lots of detail check the talk called "Anything you
wanted to know about Indexes" by Scott Lashley.
A great reason to attend this years IIUG conference in
Sunny Miami is this type of detail and direct access to
the architects.
John F. Miller III
STSM, Lead Architect
503-747-1366
IBM Informix Dynamic Server (IDS)
Date: 02/24/2014 10:05 AM
Subject: Re: index on varchar
Post by Jack Parker
Yes.
The index is built using the full potential length of the varchar.
Which means that a varchar(255) will yield 7 indexes per index page
using a 2K page
Post by Jack Parker
j.
Post by t***@gmail.com
Hello
Are indexes on varchars in Informix "inefficient"?
thanks
_______________________________________________
Informix-list mailing list
http://www.iiug.org/mailman/listinfo/informix-list
I don't know if we used to do that, but I just tested 12.10 and 11.
50 and that is not true.
create table v1 (c1 varchar(255)) lock mode row;
create index v1_idx on v1(c1);
When you look at the index page...1st off, I have more then 7 key
values, and 2nd off, it's easy to see the length of the slots is not
based on the full length of 255.
oncheck -pp 1049353 1
addr stamp chksum nslots flag type frptr frcnt next prev
1:74330 10202079 891e 11 8b0 BTREE 159 1841
0 0
slot ptr len flg
1 37 9 0
2 103 13 0
3 46 9 0
4 75 12 0
5 55 10 0
6 24 13 0
7 127 16 0
8 143 16 0
9 65 10 0
10 87 16 0
11 116 11 0
Jacques Renaut
IBM Informix Advanced Support
APD Team
_______________________________________________
Informix-list mailing list
http://www.iiug.org/mailman/listinfo/informix-list
_______________________________________________
Informix-list mailing list
http://www.iiug.org/mailman/listinfo/informix-list
Loading...