Geoff Worboys wrote:
Firebird v2.5.3 - Windows 32bit server Database page size is 8192, ODS v11.2.
I'm getting: "key size exceeds implementation restriction for index"
when I try to create the following index:
CREATE INDEX MyIndex1 ON MyTable1 (Field1, Field2);
where:
Field1 is VARCHAR(80) Field2 is VARCHAR(255)
both use the database default CHARACTER SET WIN1252 with collation WIN1252_NOCASE as defined like this:
CREATE COLLATION WIN1252_UNICODE
FOR WIN1252^
CREATE COLLATION WIN1252_NOCASE
FOR WIN1252
FROM WIN1252_UNICODE
CASE INSENSITIVE^
As it happens, I'm happy not to have this particular index, but I'd like to understand what's going on.
I've looked at this: http://www.firebirdfaq.org/ip_ib_indexcalculator.htm and I cannot find a combination for the above specification that exceeds 2048 bytes (1/4 page size) - 1260 is the figure it would lead me to expect, I think, though I'm not all that clear about the distinctions between the levels of collation. But even UTF8 at those sizes is reported at only 1675 bytes.
Is the restriction in this case expected, or should I be looking for some other problem? Maybe I have my collation set up incorrectly?
Dmitry Yemanov answers:
IIRC, unicode derived case/accent insensitive collations use six bytes per character encoding. This gives us 2010 bytes which is pretty near the 1/4 page size. Given that some overhead should be taken into account, the key length may in fact overflow the limit.
Geoff Worboys confirms:
That fits. For example I can create an expression index of the same (335 character) size, and that works, so it must be the multi-segment overhead that is killing it.
On a test database with page size 4096 I just tried these:
CREATE INDEX myindex
ON testtable COMPUTED BY
(CAST(afield || 'a' AS VARCHAR(169)) COLLATE WIN1252_NOCASE);
that one ((169 * 6) + 9 = 1023) works.
CREATE INDEX myindex
ON testtable COMPUTED BY
(CAST(afield || 'a' AS VARCHAR(170)) COLLATE WIN1252_NOCASE);
that one ((170 * 6) + 9 = 1029) fails with: "key size exceeds implementation restriction for index".
Which seems to confirm what you've said.