InnoDB index size with NULLs allowed (MySQL)
I wonder if anyone ever found confirmation in MySQL docs that for InnoDB, a column that allows NULL in the index takes 1 extra byte?
Example: create a column SMALLINT UNSIGNED DEFAULT NULL; (2 bytes). The index uses 3 bytes (without taking into account PK links).
The same column that does not allow NULL: SMALLINT UNSIGNED NOT NULL; The index will be as it should - 2 bytes.
UPD: I found this in docs:
"Due to the key storage format, the key length is one greater for a column that can be NULL than for a NOT NULL column."
But still, I dont understand, whether index size is 1 byte greater with NULLable column or not.
P.S. Sorry for my bad English :)
mysql innodb
asked Nov 24 '18 at 23:08
Max32NovMax32Nov
83
add a comment |
I wonder if anyone ever found confirmation in MySQL docs that for InnoDB, a column that allows NULL in the index takes 1 extra byte?
Example: create a column SMALLINT UNSIGNED DEFAULT NULL; (2 bytes). The index uses 3 bytes (without taking into account PK links).
The same column that does not allow NULL: SMALLINT UNSIGNED NOT NULL; The index will be as it should - 2 bytes.
UPD: I found this in docs:
"Due to the key storage format, the key length is one greater for a column that can be NULL than for a NOT NULL column."
But still, I dont understand, whether index size is 1 byte greater with NULLable column or not.
P.S. Sorry for my bad English :)
mysql innodb
asked Nov 24 '18 at 23:08
Max32NovMax32Nov
83
add a comment |
I wonder if anyone ever found confirmation in MySQL docs that for InnoDB, a column that allows NULL in the index takes 1 extra byte?
Example: create a column SMALLINT UNSIGNED DEFAULT NULL; (2 bytes). The index uses 3 bytes (without taking into account PK links).
The same column that does not allow NULL: SMALLINT UNSIGNED NOT NULL; The index will be as it should - 2 bytes.
UPD: I found this in docs:
"Due to the key storage format, the key length is one greater for a column that can be NULL than for a NOT NULL column."
But still, I dont understand, whether index size is 1 byte greater with NULLable column or not.
P.S. Sorry for my bad English :)
mysql innodb
asked Nov 24 '18 at 23:08
Max32NovMax32Nov
83
I wonder if anyone ever found confirmation in MySQL docs that for InnoDB, a column that allows NULL in the index takes 1 extra byte?
Example: create a column SMALLINT UNSIGNED DEFAULT NULL; (2 bytes). The index uses 3 bytes (without taking into account PK links).
The same column that does not allow NULL: SMALLINT UNSIGNED NOT NULL; The index will be as it should - 2 bytes.
UPD: I found this in docs:
"Due to the key storage format, the key length is one greater for a column that can be NULL than for a NOT NULL column."
But still, I dont understand, whether index size is 1 byte greater with NULLable column or not.
P.S. Sorry for my bad English :)
mysql innodb
mysql innodb
asked Nov 24 '18 at 23:08
Max32NovMax32Nov
83
asked Nov 24 '18 at 23:08
Max32NovMax32Nov
83
edited Nov 24 '18 at 23:25
Max32Nov
asked Nov 24 '18 at 23:08
Max32NovMax32Nov
83
asked Nov 24 '18 at 23:08
Max32NovMax32Nov
83
asked Nov 24 '18 at 23:08
Max32NovMax32Nov
83
83
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
There are several flaws in the key_len of EXPLAIN.
- There are differences between Engines, but Explain does not take account of such.
- The null bit may or may not take a full byte. Still, 3 vs 2 is a handy clue that the
SMALLINTisNULLorNOT NULL.
VAR...actually takes a variable amount of space.- InnoDB` has a 1- or 2-byte prefix to each column; that is not mentioned.
- The
key_lenusually accounts for any column(s) that are tested with=. If there also a "range" test (BETWEEN,>,LIKE 'foo%', etc)that can use part of the index, key_len does not indicate such. - Ditto for using part of the index for
GROUP BYandORDER BY.
You can get more information (but still not 'everyting') by using EXPLAIN FORMAT=JSON SELECT ....
Logically, if not in reality, there is no room for NULL in a 2-byte SMALLINT. So, more space is needed -- at least one bit.
There are two separate issues -- The size of the index BTree, and the data structure(s) used during the query.
I would argue that the extra byte or bit for NULL is not worth worrying about. Instead, it is better to say NOT NULL except when you have a "business logic" requirement for NULL (no value, N/A, not yet specified, etc, etc). Then let the table, index, etc, consume an extra bit or byte as needed.
I think (without sufficient confirmation) that InnoDB takes no extra space for the null bit -- it is one of the 8 or 16 bits that prefixes each column.
Note that in InnoDB, an index BTree is essentially identical to the data BTree. (And the PRIMARY KEY is the ordering of the data BTree.)
answered Nov 24 '18 at 23:28
Rick JamesRick James
69.4k561102
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53463155%2finnodb-index-size-with-nulls-allowed-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
There are several flaws in the key_len of EXPLAIN.
- There are differences between Engines, but Explain does not take account of such.
- The null bit may or may not take a full byte. Still, 3 vs 2 is a handy clue that the
SMALLINTisNULLorNOT NULL.
VAR...actually takes a variable amount of space.- InnoDB` has a 1- or 2-byte prefix to each column; that is not mentioned.
- The
key_lenusually accounts for any column(s) that are tested with=. If there also a "range" test (BETWEEN,>,LIKE 'foo%', etc)that can use part of the index, key_len does not indicate such. - Ditto for using part of the index for
GROUP BYandORDER BY.
You can get more information (but still not 'everyting') by using EXPLAIN FORMAT=JSON SELECT ....
Logically, if not in reality, there is no room for NULL in a 2-byte SMALLINT. So, more space is needed -- at least one bit.
There are two separate issues -- The size of the index BTree, and the data structure(s) used during the query.
I would argue that the extra byte or bit for NULL is not worth worrying about. Instead, it is better to say NOT NULL except when you have a "business logic" requirement for NULL (no value, N/A, not yet specified, etc, etc). Then let the table, index, etc, consume an extra bit or byte as needed.
I think (without sufficient confirmation) that InnoDB takes no extra space for the null bit -- it is one of the 8 or 16 bits that prefixes each column.
Note that in InnoDB, an index BTree is essentially identical to the data BTree. (And the PRIMARY KEY is the ordering of the data BTree.)
answered Nov 24 '18 at 23:28
Rick JamesRick James
69.4k561102
add a comment |
There are several flaws in the key_len of EXPLAIN.
- There are differences between Engines, but Explain does not take account of such.
- The null bit may or may not take a full byte. Still, 3 vs 2 is a handy clue that the
SMALLINTisNULLorNOT NULL.
VAR...actually takes a variable amount of space.- InnoDB` has a 1- or 2-byte prefix to each column; that is not mentioned.
- The
key_lenusually accounts for any column(s) that are tested with=. If there also a "range" test (BETWEEN,>,LIKE 'foo%', etc)that can use part of the index, key_len does not indicate such. - Ditto for using part of the index for
GROUP BYandORDER BY.
You can get more information (but still not 'everyting') by using EXPLAIN FORMAT=JSON SELECT ....
Logically, if not in reality, there is no room for NULL in a 2-byte SMALLINT. So, more space is needed -- at least one bit.
There are two separate issues -- The size of the index BTree, and the data structure(s) used during the query.
I would argue that the extra byte or bit for NULL is not worth worrying about. Instead, it is better to say NOT NULL except when you have a "business logic" requirement for NULL (no value, N/A, not yet specified, etc, etc). Then let the table, index, etc, consume an extra bit or byte as needed.
I think (without sufficient confirmation) that InnoDB takes no extra space for the null bit -- it is one of the 8 or 16 bits that prefixes each column.
Note that in InnoDB, an index BTree is essentially identical to the data BTree. (And the PRIMARY KEY is the ordering of the data BTree.)
answered Nov 24 '18 at 23:28
Rick JamesRick James
69.4k561102
add a comment |
There are several flaws in the key_len of EXPLAIN.
- There are differences between Engines, but Explain does not take account of such.
- The null bit may or may not take a full byte. Still, 3 vs 2 is a handy clue that the
SMALLINTisNULLorNOT NULL.
VAR...actually takes a variable amount of space.- InnoDB` has a 1- or 2-byte prefix to each column; that is not mentioned.
- The
key_lenusually accounts for any column(s) that are tested with=. If there also a "range" test (BETWEEN,>,LIKE 'foo%', etc)that can use part of the index, key_len does not indicate such. - Ditto for using part of the index for
GROUP BYandORDER BY.
You can get more information (but still not 'everyting') by using EXPLAIN FORMAT=JSON SELECT ....
Logically, if not in reality, there is no room for NULL in a 2-byte SMALLINT. So, more space is needed -- at least one bit.
There are two separate issues -- The size of the index BTree, and the data structure(s) used during the query.
I would argue that the extra byte or bit for NULL is not worth worrying about. Instead, it is better to say NOT NULL except when you have a "business logic" requirement for NULL (no value, N/A, not yet specified, etc, etc). Then let the table, index, etc, consume an extra bit or byte as needed.
I think (without sufficient confirmation) that InnoDB takes no extra space for the null bit -- it is one of the 8 or 16 bits that prefixes each column.
Note that in InnoDB, an index BTree is essentially identical to the data BTree. (And the PRIMARY KEY is the ordering of the data BTree.)
answered Nov 24 '18 at 23:28
Rick JamesRick James
69.4k561102
There are several flaws in the key_len of EXPLAIN.
- There are differences between Engines, but Explain does not take account of such.
- The null bit may or may not take a full byte. Still, 3 vs 2 is a handy clue that the
SMALLINTisNULLorNOT NULL.
VAR...actually takes a variable amount of space.- InnoDB` has a 1- or 2-byte prefix to each column; that is not mentioned.
- The
key_lenusually accounts for any column(s) that are tested with=. If there also a "range" test (BETWEEN,>,LIKE 'foo%', etc)that can use part of the index, key_len does not indicate such. - Ditto for using part of the index for
GROUP BYandORDER BY.
You can get more information (but still not 'everyting') by using EXPLAIN FORMAT=JSON SELECT ....
Logically, if not in reality, there is no room for NULL in a 2-byte SMALLINT. So, more space is needed -- at least one bit.
There are two separate issues -- The size of the index BTree, and the data structure(s) used during the query.
I would argue that the extra byte or bit for NULL is not worth worrying about. Instead, it is better to say NOT NULL except when you have a "business logic" requirement for NULL (no value, N/A, not yet specified, etc, etc). Then let the table, index, etc, consume an extra bit or byte as needed.
I think (without sufficient confirmation) that InnoDB takes no extra space for the null bit -- it is one of the 8 or 16 bits that prefixes each column.
Note that in InnoDB, an index BTree is essentially identical to the data BTree. (And the PRIMARY KEY is the ordering of the data BTree.)
answered Nov 24 '18 at 23:28
Rick JamesRick James
69.4k561102
edited Nov 24 '18 at 23:36
answered Nov 24 '18 at 23:28
Rick JamesRick James
69.4k561102
answered Nov 24 '18 at 23:28
Rick JamesRick James
69.4k561102
answered Nov 24 '18 at 23:28
Rick JamesRick James
69.4k561102
69.4k561102
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53463155%2finnodb-index-size-with-nulls-allowed-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
