tested this briefly, with some random 255 characters strings, and the same full 255 characters can be seen in data and index pages.
Again, all this in rootdbs on Linux, so least possible page size.
Still couldn't think of a reason behind a 254 characters/bytes restriction, nor seeing the restriction being in effect.
Original Message:
Sent: Mon November 13, 2023 02:54 AM
From: Milan Rafaj
Subject: varchar index on varchar(255) column
Hello Andreas,
I think current limit is 764 bytes per index: (from 12.10 docs abou Informix limits)
Key parts per index | 16 |
Columns per functional index | 102 (for C UDRs) 341 (for SPL or Java™ UDRs) |
Maximum bytes per index key: | 2K page size = 387 4K page size = 796 8K page size = 1615 12K page size = 2435 16K page size = 3254 |
But max. length of varchar column to be indexed might ca be only 254 chars?
Milan Rafaj
Senior Lead, Infrastructure/Cloud Architecture
Kyndryl Consult
+420 737 264 248
www.kyndryl.cz
Planned absence/Plánovaná nepřítomnost:
Kyndryl Česká republika, spol. s r. o.
Sídlo: Praha 4, Chodov, V Parku 2308/8, PSČ: 148 00,
IČ: 14890992
Zapsaná v obchodním rejstříku, vedeném Městským soudem v Praze (oddíl C, vložka 339277)
Registered address: Prague 4, Chodov, V Parku, 2308/8, Zip code: 148 00
Company ID: 14890992
Entered in the Commercial Register maintained by the Municipal Court in Prague (Part C, Entry 339277)
--
Unless stated otherwise above:
Kyndryl Česká republika, spol. s r. o.
Sídlo: V Parku 2308/8, Chodov, 148 00 Praha 4,
IČ: 096 28 886
Zapsaná v obchodním rejstříku, vedeném Městským soudem v Praze (oddíl C, vložka 339277)
Registered address: V Parku 2308/8, Chodov, 148 00 Prague 4
Company ID: 096 28 886
Registered in the Commercial Register maintained by the Municipal Court in Prague (Part C, Entry 339277)
Original Message:
Sent: 11/10/2023 1:25:00 PM
From: Andreas Legner
Subject: RE: varchar index on varchar(255) column
Hmmm,
create table test_vc (i int, v varchar(255)) in rootdbs;
Table created.
create index test_vc_ix on test_vc (i, v) in rootdbs;
Index created.
As you can see I did this in rootdbs, so with minimum possible page size, and I even included the i column.
From finderr 517:
-517 The total size of the index is too large or too many parts in index.
Informix Dynamic Server has limits on the number of columns that can be
included in an index and on the total number of bytes in a key (the sum
of the widths of the columns). This CREATE INDEX statement would exceed
that limit for this database server. Informix Dynamic Server
allows 16 key parts (columnar or functional) and a width of 390 bytes on
a 2K page platform or in a 2K page dbspace. On 4K page platforms or using a
dbspace with a non-default page size allows greater than 390 bytes for the
width of indexed columns.
So I don't know when that phrase in the docs last was true, apparently it no longer is - or does anyone have a different view on this?
Now on the performance aspect: apparently what you'd like and possibly expect is a key-only scan (since all columns in your query are part of the index). With varchars (and also lvarchar?) in an index, this is not possible, so, even if the query goes via the desired index, it would do a cross-check with the row for every match it finds in the index: many index matches, many row retrievals == slow.
On the other hand, the query you're citing only produces one row, so should have done only one such cross check, and, quite obviously it is not slow.
-> what type of query would suffer bad performance with such index?
------------------------------
Andreas Legner
Original Message:
Sent: Fri November 10, 2023 11:22 AM
From: Pete Rushie
Subject: varchar index on varchar(255) column
From the documentation (VARCHAR(m,r) data type - IBM Documentation) there is this caveat:
If you are placing an index on a VARCHAR column, the maximum size is 254 bytes.
The effect on system performance seems to depend on the number of rows, in our test environment with 1 million rows there was no noticeable performance hit. In our production environment with 50 million rows the system was brought to a crawl until we removed the index.
------------------------------
Pete Rushie
Original Message:
Sent: Fri November 10, 2023 09:06 AM
From: Andreas Legner
Subject: varchar index on varchar(255) column
Not quite getting "not realizing that there is a max size of 254 when creating an index", and then index creation succeeded.
And why in how far then was this detrimental to system performance?
------------------------------
Andreas Legner
Original Message:
Sent: Mon March 27, 2023 04:00 PM
From: Pete Rushie
Subject: varchar index on varchar(255) column
We have a table with approximately 50 million rows that contains a VARCHAR(255), not realizing that there is a max size of 254 when creating an index, we attempted to create an index on the column. It succeeded but seemed detrimental to system performance. So we dropped the index and things returned to normal. But we are still needing an index on this column, short of reducing the column and fixing it everywhere in our code, is there a way around this? We were thinking about a fragmented index on the column?
Thanks,
Pete Rushie
Currency Technics & Metrics
------------------------------
Pete Rushie
------------------------------