How Mysql's Adaptive-Hash Index works
up vote
0
down vote
favorite
Assume there has a hash index on column name, the hash table should looks like prefix of the index key -> pages of the index, according to this https://dev.mysql.com/doc/refman/8.0/en/innodb-adaptive-hash.html
Based on the observed pattern of searches, a hash index is built using a prefix of the index key. The prefix can be any length, and it may be that only some values in the B-tree appear in the hash index. Hash indexes are built on demand for the pages of the index that are accessed often.
but i want to know why the hash table is not like prefix of the index key -> result rows?
mysql indexing
asked Nov 11 at 14:17
user10570334
61
add a comment |
up vote
0
down vote
favorite
Assume there has a hash index on column name, the hash table should looks like prefix of the index key -> pages of the index, according to this https://dev.mysql.com/doc/refman/8.0/en/innodb-adaptive-hash.html
Based on the observed pattern of searches, a hash index is built using a prefix of the index key. The prefix can be any length, and it may be that only some values in the B-tree appear in the hash index. Hash indexes are built on demand for the pages of the index that are accessed often.
but i want to know why the hash table is not like prefix of the index key -> result rows?
mysql indexing
asked Nov 11 at 14:17
user10570334
61
The hashtable in fact maps a prefix of the index column to logical rows. I don't understand your question exactly. Can you clarify what your doubts are?
– Tim Biegeleisen
Nov 11 at 14:22
The "adaptive hash" is an internal optimization. You can ignore it. InnoDB has no "Hash" indexing, only BTree, Fulltext, and Spatial. Stackoverflow focuses on usage of the product, not the internals.
– Rick James
Nov 11 at 20:44
You can ask this question at dba.stackexchange.com
– Madhur Bhaiya
Nov 12 at 6:14
@Tim Biegeleisen i'm just doubt what the hashtable really be? "Hash indexes are built on demand for the pages of the index that are accessed often." from official document make me puzzled, Are there has anything to do with "pages of the index"?
– user10570334
Nov 12 at 8:44
You should start by reviewing a good tutorial on how indices work in databases.
– Tim Biegeleisen
Nov 12 at 8:46
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
Assume there has a hash index on column name, the hash table should looks like prefix of the index key -> pages of the index, according to this https://dev.mysql.com/doc/refman/8.0/en/innodb-adaptive-hash.html
Based on the observed pattern of searches, a hash index is built using a prefix of the index key. The prefix can be any length, and it may be that only some values in the B-tree appear in the hash index. Hash indexes are built on demand for the pages of the index that are accessed often.
but i want to know why the hash table is not like prefix of the index key -> result rows?
mysql indexing
asked Nov 11 at 14:17
user10570334
61
Assume there has a hash index on column name, the hash table should looks like prefix of the index key -> pages of the index, according to this https://dev.mysql.com/doc/refman/8.0/en/innodb-adaptive-hash.html
Based on the observed pattern of searches, a hash index is built using a prefix of the index key. The prefix can be any length, and it may be that only some values in the B-tree appear in the hash index. Hash indexes are built on demand for the pages of the index that are accessed often.
but i want to know why the hash table is not like prefix of the index key -> result rows?
mysql indexing
mysql indexing
asked Nov 11 at 14:17
user10570334
61
asked Nov 11 at 14:17
user10570334
61
asked Nov 11 at 14:17
user10570334
61
asked Nov 11 at 14:17
user10570334
61
asked Nov 11 at 14:17
user10570334
61
61
The hashtable in fact maps a prefix of the index column to logical rows. I don't understand your question exactly. Can you clarify what your doubts are?
– Tim Biegeleisen
Nov 11 at 14:22
The "adaptive hash" is an internal optimization. You can ignore it. InnoDB has no "Hash" indexing, only BTree, Fulltext, and Spatial. Stackoverflow focuses on usage of the product, not the internals.
– Rick James
Nov 11 at 20:44
You can ask this question at dba.stackexchange.com
– Madhur Bhaiya
Nov 12 at 6:14
@Tim Biegeleisen i'm just doubt what the hashtable really be? "Hash indexes are built on demand for the pages of the index that are accessed often." from official document make me puzzled, Are there has anything to do with "pages of the index"?
– user10570334
Nov 12 at 8:44
You should start by reviewing a good tutorial on how indices work in databases.
– Tim Biegeleisen
Nov 12 at 8:46
add a comment |
The hashtable in fact maps a prefix of the index column to logical rows. I don't understand your question exactly. Can you clarify what your doubts are?
– Tim Biegeleisen
Nov 11 at 14:22
The "adaptive hash" is an internal optimization. You can ignore it. InnoDB has no "Hash" indexing, only BTree, Fulltext, and Spatial. Stackoverflow focuses on usage of the product, not the internals.
– Rick James
Nov 11 at 20:44
You can ask this question at dba.stackexchange.com
– Madhur Bhaiya
Nov 12 at 6:14
@Tim Biegeleisen i'm just doubt what the hashtable really be? "Hash indexes are built on demand for the pages of the index that are accessed often." from official document make me puzzled, Are there has anything to do with "pages of the index"?
– user10570334
Nov 12 at 8:44
You should start by reviewing a good tutorial on how indices work in databases.
– Tim Biegeleisen
Nov 12 at 8:46
The hashtable in fact maps a prefix of the index column to logical rows. I don't understand your question exactly. Can you clarify what your doubts are?
– Tim Biegeleisen
Nov 11 at 14:22
The hashtable in fact maps a prefix of the index column to logical rows. I don't understand your question exactly. Can you clarify what your doubts are?
– Tim Biegeleisen
Nov 11 at 14:22
The "adaptive hash" is an internal optimization. You can ignore it. InnoDB has no "Hash" indexing, only BTree, Fulltext, and Spatial. Stackoverflow focuses on usage of the product, not the internals.
– Rick James
Nov 11 at 20:44
The "adaptive hash" is an internal optimization. You can ignore it. InnoDB has no "Hash" indexing, only BTree, Fulltext, and Spatial. Stackoverflow focuses on usage of the product, not the internals.
– Rick James
Nov 11 at 20:44
You can ask this question at dba.stackexchange.com
– Madhur Bhaiya
Nov 12 at 6:14
You can ask this question at dba.stackexchange.com
– Madhur Bhaiya
Nov 12 at 6:14
@Tim Biegeleisen i'm just doubt what the hashtable really be? "Hash indexes are built on demand for the pages of the index that are accessed often." from official document make me puzzled, Are there has anything to do with "pages of the index"?
– user10570334
Nov 12 at 8:44
@Tim Biegeleisen i'm just doubt what the hashtable really be? "Hash indexes are built on demand for the pages of the index that are accessed often." from official document make me puzzled, Are there has anything to do with "pages of the index"?
– user10570334
Nov 12 at 8:44
You should start by reviewing a good tutorial on how indices work in databases.
– Tim Biegeleisen
Nov 12 at 8:46
You should start by reviewing a good tutorial on how indices work in databases.
– Tim Biegeleisen
Nov 12 at 8:46
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53249610%2fhow-mysqls-adaptive-hash-index-works%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
The hashtable in fact maps a prefix of the index column to logical rows. I don't understand your question exactly. Can you clarify what your doubts are?
– Tim Biegeleisen
Nov 11 at 14:22
The "adaptive hash" is an internal optimization. You can ignore it. InnoDB has no "Hash" indexing, only BTree, Fulltext, and Spatial. Stackoverflow focuses on usage of the product, not the internals.
– Rick James
Nov 11 at 20:44
You can ask this question at dba.stackexchange.com
– Madhur Bhaiya
Nov 12 at 6:14
@Tim Biegeleisen i'm just doubt what the hashtable really be? "Hash indexes are built on demand for the pages of the index that are accessed often." from official document make me puzzled, Are there has anything to do with "pages of the index"?
– user10570334
Nov 12 at 8:44
You should start by reviewing a good tutorial on how indices work in databases.
– Tim Biegeleisen
Nov 12 at 8:46