You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
When I want to create a index with two varchar(205) in utf8, firebird give me a error (key size exceeds implementation restriction for index).
But according to the documentation, normally the limit is 509 from this compute : (PAGE_SIZE / 4 - 9) / BYTES_PER_CHARSET.
And for my is (8192 / 4 - 9) / 4 = 509 (Same the doc)
Exemple script for test this issue
-- Basic table
CREATE TABLE "table" (
"id" INTEGER NOT NULL,
"fname" VARCHAR(205) NOT NULL,
"lname" VARCHAR(205) NOT NULL,
"fname2" VARCHAR(204) NOT NULL,
"lname2" VARCHAR(204) NOT NULL
);
CREATE INDEX "idx_table_test1" ON "table" ("fname", "lname"); -- 410 : Fail
CREATE INDEX "idx_table_test2" ON "table" ("fname", "lname2"); -- 409 : Fail
CREATE INDEX "idx_table_test3" ON "table" ("fname2", "lname2"); -- 408 : Work
Why ?
The text was updated successfully, but these errors were encountered:
Because two fields of 205 characters does not produce the key of 410*4 bytes. For compound indices, special bytes are added per every segment, so the actual compound key is slightly longer than you expect.
Submitted by: Reydy Valentin (lifeoryou)
When I want to create a index with two varchar(205) in utf8, firebird give me a error (key size exceeds implementation restriction for index).
But according to the documentation, normally the limit is 509 from this compute : (PAGE_SIZE / 4 - 9) / BYTES_PER_CHARSET.
And for my is (8192 / 4 - 9) / 4 = 509 (Same the doc)
Exemple script for test this issue
-- Basic table
CREATE TABLE "table" (
"id" INTEGER NOT NULL,
"fname" VARCHAR(205) NOT NULL,
"lname" VARCHAR(205) NOT NULL,
"fname2" VARCHAR(204) NOT NULL,
"lname2" VARCHAR(204) NOT NULL
);
CREATE INDEX "idx_table_test1" ON "table" ("fname", "lname"); -- 410 : Fail
CREATE INDEX "idx_table_test2" ON "table" ("fname", "lname2"); -- 409 : Fail
CREATE INDEX "idx_table_test3" ON "table" ("fname2", "lname2"); -- 408 : Work
Why ?
The text was updated successfully, but these errors were encountered: