How to get index value for specific table from mysql?
How to get mysql index value for specific table from MySQL?
"Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are
stored in B-trees."
As you can check out in documentation it is B-trees?
mysql indexing pagination cursor
asked Nov 20 '18 at 19:41
exp2Tapavickiexp2Tapavicki
12719
add a comment |
How to get mysql index value for specific table from MySQL?
"Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are
stored in B-trees."
As you can check out in documentation it is B-trees?
mysql indexing pagination cursor
asked Nov 20 '18 at 19:41
exp2Tapavickiexp2Tapavicki
12719
dev.mysql.com/doc/refman/8.0/en/show-index.html
– P.Salmon
Nov 20 '18 at 19:54
This is how to find index information. Now question is how to get specific index BTREE object(value). After I get this BTREE I will traverse through it. At the moment I don't know how to get BTREE.
– exp2Tapavicki
Nov 21 '18 at 17:24
How do mean traverse through it.?
– P.Salmon
Nov 21 '18 at 17:34
It's not important because I need to get BTREE somehow first.
– exp2Tapavicki
Nov 22 '18 at 7:17
add a comment |
How to get mysql index value for specific table from MySQL?
"Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are
stored in B-trees."
As you can check out in documentation it is B-trees?
mysql indexing pagination cursor
asked Nov 20 '18 at 19:41
exp2Tapavickiexp2Tapavicki
12719
How to get mysql index value for specific table from MySQL?
"Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are
stored in B-trees."
As you can check out in documentation it is B-trees?
mysql indexing pagination cursor
mysql indexing pagination cursor
asked Nov 20 '18 at 19:41
exp2Tapavickiexp2Tapavicki
12719
asked Nov 20 '18 at 19:41
exp2Tapavickiexp2Tapavicki
12719
asked Nov 20 '18 at 19:41
exp2Tapavickiexp2Tapavicki
12719
asked Nov 20 '18 at 19:41
exp2Tapavickiexp2Tapavicki
12719
asked Nov 20 '18 at 19:41
exp2Tapavickiexp2Tapavicki
12719
12719
dev.mysql.com/doc/refman/8.0/en/show-index.html
– P.Salmon
Nov 20 '18 at 19:54
This is how to find index information. Now question is how to get specific index BTREE object(value). After I get this BTREE I will traverse through it. At the moment I don't know how to get BTREE.
– exp2Tapavicki
Nov 21 '18 at 17:24
How do mean traverse through it.?
– P.Salmon
Nov 21 '18 at 17:34
It's not important because I need to get BTREE somehow first.
– exp2Tapavicki
Nov 22 '18 at 7:17
add a comment |
dev.mysql.com/doc/refman/8.0/en/show-index.html
– P.Salmon
Nov 20 '18 at 19:54
This is how to find index information. Now question is how to get specific index BTREE object(value). After I get this BTREE I will traverse through it. At the moment I don't know how to get BTREE.
– exp2Tapavicki
Nov 21 '18 at 17:24
How do mean traverse through it.?
– P.Salmon
Nov 21 '18 at 17:34
It's not important because I need to get BTREE somehow first.
– exp2Tapavicki
Nov 22 '18 at 7:17
dev.mysql.com/doc/refman/8.0/en/show-index.html
– P.Salmon
Nov 20 '18 at 19:54
dev.mysql.com/doc/refman/8.0/en/show-index.html
– P.Salmon
Nov 20 '18 at 19:54
This is how to find index information. Now question is how to get specific index BTREE object(value). After I get this BTREE I will traverse through it. At the moment I don't know how to get BTREE.
– exp2Tapavicki
Nov 21 '18 at 17:24
This is how to find index information. Now question is how to get specific index BTREE object(value). After I get this BTREE I will traverse through it. At the moment I don't know how to get BTREE.
– exp2Tapavicki
Nov 21 '18 at 17:24
How do mean traverse through it.?
– P.Salmon
Nov 21 '18 at 17:34
How do mean traverse through it.?
– P.Salmon
Nov 21 '18 at 17:34
It's not important because I need to get BTREE somehow first.
– exp2Tapavicki
Nov 22 '18 at 7:17
It's not important because I need to get BTREE somehow first.
– exp2Tapavicki
Nov 22 '18 at 7:17
add a comment |
1 Answer
1
active
oldest
votes
Study BTrees. More specifically, B+Trees.
Let's search for a single item by the BTree index. A BTree has a root node; start there. It has perhaps 100 links to subnodes and information on range of value to be found in each. You go into the appropriate subnode. For a small table, you are finished. But for a huge table, you repeat the process a few times. (Perhaps 6 iterations for a table with a trillion rows.) Done. You have found the one row you wanted.
Now, let's fetch a "range" of rows. (Example: WHERE id BETWEEN 1234 and 9876 or WHERE name LIKE 'T%' Do the above to find the first of the range, then go to the 'next' record in the bottom ("leaf") node you are in. This is fine until you run out of records in the leaf node.
How the + in B+Tree kicks in. It means that there is a link from this node to the 'next' node at the bottom level. So it is quite quick to continue the range query.
Statistics for a million-row table with a "fan-out" of 100:
To find a particular item: 3 nodes (16KB blocks in the case of InnoDB) need to be touched.
To fetch 500 consecutive rows: 3 nodes to get to the start; then about 5 more to scan forward.
Compare... If there were no index, you would need to scan the entire table of about 10,000 blocks (1M/100). This would be required for either of the hypothetical queries above.
In InnoDB (in MySQL), nearly all of the keys you ever make will be PRIMARY KEY, UNIQUE, and INDEX. Those are B+Trees; there is no option (unlike other vendors). Other than that, there are SPATIAL in FULLTEXT; they are more complex.
If you do SHOW CREATE TABLE foo, you get some info about the indexes. `information_schema has further info.
answered Nov 21 '18 at 18:04
Rick JamesRick James
69.5k561102
I have my doubts about the everyday value of knowing that an index is btree or otherwise or how sql traverses the nodes. At the end of the day you tell sql what you want and the optimiser decides how to do what you want,including whether to use an index or not.
– P.Salmon
Nov 21 '18 at 19:38
B+Trees are excellent for "point queries" and "range queries". Still, the Optimizer may validly choose to ignore your index. Example: Most of the rows are needed anyway; bouncing between the index BTree and the data would be slower than simply scanning all the data.
– Rick James
Nov 21 '18 at 20:11
This is great information. This is the reason why I somehow need to get B+Trees object. So I can manipulate with it later. Many people probably didn't understand what I need. Thanks @RickJames you got vote up from me.
– exp2Tapavicki
Nov 22 '18 at 7:33
@P.Salmon - Yeah, TMI. Sometimes I feel like giving too much info. My Comment is the "Executive Summary" -- sufficient for most people.
– Rick James
Nov 22 '18 at 19:12
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%2f53400402%2fhow-to-get-index-value-for-specific-table-from-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
Study BTrees. More specifically, B+Trees.
Let's search for a single item by the BTree index. A BTree has a root node; start there. It has perhaps 100 links to subnodes and information on range of value to be found in each. You go into the appropriate subnode. For a small table, you are finished. But for a huge table, you repeat the process a few times. (Perhaps 6 iterations for a table with a trillion rows.) Done. You have found the one row you wanted.
Now, let's fetch a "range" of rows. (Example: WHERE id BETWEEN 1234 and 9876 or WHERE name LIKE 'T%' Do the above to find the first of the range, then go to the 'next' record in the bottom ("leaf") node you are in. This is fine until you run out of records in the leaf node.
How the + in B+Tree kicks in. It means that there is a link from this node to the 'next' node at the bottom level. So it is quite quick to continue the range query.
Statistics for a million-row table with a "fan-out" of 100:
To find a particular item: 3 nodes (16KB blocks in the case of InnoDB) need to be touched.
To fetch 500 consecutive rows: 3 nodes to get to the start; then about 5 more to scan forward.
Compare... If there were no index, you would need to scan the entire table of about 10,000 blocks (1M/100). This would be required for either of the hypothetical queries above.
In InnoDB (in MySQL), nearly all of the keys you ever make will be PRIMARY KEY, UNIQUE, and INDEX. Those are B+Trees; there is no option (unlike other vendors). Other than that, there are SPATIAL in FULLTEXT; they are more complex.
If you do SHOW CREATE TABLE foo, you get some info about the indexes. `information_schema has further info.
answered Nov 21 '18 at 18:04
Rick JamesRick James
69.5k561102
I have my doubts about the everyday value of knowing that an index is btree or otherwise or how sql traverses the nodes. At the end of the day you tell sql what you want and the optimiser decides how to do what you want,including whether to use an index or not.
– P.Salmon
Nov 21 '18 at 19:38
B+Trees are excellent for "point queries" and "range queries". Still, the Optimizer may validly choose to ignore your index. Example: Most of the rows are needed anyway; bouncing between the index BTree and the data would be slower than simply scanning all the data.
– Rick James
Nov 21 '18 at 20:11
This is great information. This is the reason why I somehow need to get B+Trees object. So I can manipulate with it later. Many people probably didn't understand what I need. Thanks @RickJames you got vote up from me.
– exp2Tapavicki
Nov 22 '18 at 7:33
@P.Salmon - Yeah, TMI. Sometimes I feel like giving too much info. My Comment is the "Executive Summary" -- sufficient for most people.
– Rick James
Nov 22 '18 at 19:12
add a comment |
Study BTrees. More specifically, B+Trees.
Let's search for a single item by the BTree index. A BTree has a root node; start there. It has perhaps 100 links to subnodes and information on range of value to be found in each. You go into the appropriate subnode. For a small table, you are finished. But for a huge table, you repeat the process a few times. (Perhaps 6 iterations for a table with a trillion rows.) Done. You have found the one row you wanted.
Now, let's fetch a "range" of rows. (Example: WHERE id BETWEEN 1234 and 9876 or WHERE name LIKE 'T%' Do the above to find the first of the range, then go to the 'next' record in the bottom ("leaf") node you are in. This is fine until you run out of records in the leaf node.
How the + in B+Tree kicks in. It means that there is a link from this node to the 'next' node at the bottom level. So it is quite quick to continue the range query.
Statistics for a million-row table with a "fan-out" of 100:
To find a particular item: 3 nodes (16KB blocks in the case of InnoDB) need to be touched.
To fetch 500 consecutive rows: 3 nodes to get to the start; then about 5 more to scan forward.
Compare... If there were no index, you would need to scan the entire table of about 10,000 blocks (1M/100). This would be required for either of the hypothetical queries above.
In InnoDB (in MySQL), nearly all of the keys you ever make will be PRIMARY KEY, UNIQUE, and INDEX. Those are B+Trees; there is no option (unlike other vendors). Other than that, there are SPATIAL in FULLTEXT; they are more complex.
If you do SHOW CREATE TABLE foo, you get some info about the indexes. `information_schema has further info.
answered Nov 21 '18 at 18:04
Rick JamesRick James
69.5k561102
I have my doubts about the everyday value of knowing that an index is btree or otherwise or how sql traverses the nodes. At the end of the day you tell sql what you want and the optimiser decides how to do what you want,including whether to use an index or not.
– P.Salmon
Nov 21 '18 at 19:38
B+Trees are excellent for "point queries" and "range queries". Still, the Optimizer may validly choose to ignore your index. Example: Most of the rows are needed anyway; bouncing between the index BTree and the data would be slower than simply scanning all the data.
– Rick James
Nov 21 '18 at 20:11
This is great information. This is the reason why I somehow need to get B+Trees object. So I can manipulate with it later. Many people probably didn't understand what I need. Thanks @RickJames you got vote up from me.
– exp2Tapavicki
Nov 22 '18 at 7:33
@P.Salmon - Yeah, TMI. Sometimes I feel like giving too much info. My Comment is the "Executive Summary" -- sufficient for most people.
– Rick James
Nov 22 '18 at 19:12
add a comment |
Study BTrees. More specifically, B+Trees.
Let's search for a single item by the BTree index. A BTree has a root node; start there. It has perhaps 100 links to subnodes and information on range of value to be found in each. You go into the appropriate subnode. For a small table, you are finished. But for a huge table, you repeat the process a few times. (Perhaps 6 iterations for a table with a trillion rows.) Done. You have found the one row you wanted.
Now, let's fetch a "range" of rows. (Example: WHERE id BETWEEN 1234 and 9876 or WHERE name LIKE 'T%' Do the above to find the first of the range, then go to the 'next' record in the bottom ("leaf") node you are in. This is fine until you run out of records in the leaf node.
How the + in B+Tree kicks in. It means that there is a link from this node to the 'next' node at the bottom level. So it is quite quick to continue the range query.
Statistics for a million-row table with a "fan-out" of 100:
To find a particular item: 3 nodes (16KB blocks in the case of InnoDB) need to be touched.
To fetch 500 consecutive rows: 3 nodes to get to the start; then about 5 more to scan forward.
Compare... If there were no index, you would need to scan the entire table of about 10,000 blocks (1M/100). This would be required for either of the hypothetical queries above.
In InnoDB (in MySQL), nearly all of the keys you ever make will be PRIMARY KEY, UNIQUE, and INDEX. Those are B+Trees; there is no option (unlike other vendors). Other than that, there are SPATIAL in FULLTEXT; they are more complex.
If you do SHOW CREATE TABLE foo, you get some info about the indexes. `information_schema has further info.
answered Nov 21 '18 at 18:04
Rick JamesRick James
69.5k561102
Study BTrees. More specifically, B+Trees.
Let's search for a single item by the BTree index. A BTree has a root node; start there. It has perhaps 100 links to subnodes and information on range of value to be found in each. You go into the appropriate subnode. For a small table, you are finished. But for a huge table, you repeat the process a few times. (Perhaps 6 iterations for a table with a trillion rows.) Done. You have found the one row you wanted.
Now, let's fetch a "range" of rows. (Example: WHERE id BETWEEN 1234 and 9876 or WHERE name LIKE 'T%' Do the above to find the first of the range, then go to the 'next' record in the bottom ("leaf") node you are in. This is fine until you run out of records in the leaf node.
How the + in B+Tree kicks in. It means that there is a link from this node to the 'next' node at the bottom level. So it is quite quick to continue the range query.
Statistics for a million-row table with a "fan-out" of 100:
To find a particular item: 3 nodes (16KB blocks in the case of InnoDB) need to be touched.
To fetch 500 consecutive rows: 3 nodes to get to the start; then about 5 more to scan forward.
Compare... If there were no index, you would need to scan the entire table of about 10,000 blocks (1M/100). This would be required for either of the hypothetical queries above.
In InnoDB (in MySQL), nearly all of the keys you ever make will be PRIMARY KEY, UNIQUE, and INDEX. Those are B+Trees; there is no option (unlike other vendors). Other than that, there are SPATIAL in FULLTEXT; they are more complex.
If you do SHOW CREATE TABLE foo, you get some info about the indexes. `information_schema has further info.
answered Nov 21 '18 at 18:04
Rick JamesRick James
69.5k561102
answered Nov 21 '18 at 18:04
Rick JamesRick James
69.5k561102
answered Nov 21 '18 at 18:04
Rick JamesRick James
69.5k561102
answered Nov 21 '18 at 18:04
Rick JamesRick James
69.5k561102
69.5k561102
I have my doubts about the everyday value of knowing that an index is btree or otherwise or how sql traverses the nodes. At the end of the day you tell sql what you want and the optimiser decides how to do what you want,including whether to use an index or not.
– P.Salmon
Nov 21 '18 at 19:38
B+Trees are excellent for "point queries" and "range queries". Still, the Optimizer may validly choose to ignore your index. Example: Most of the rows are needed anyway; bouncing between the index BTree and the data would be slower than simply scanning all the data.
– Rick James
Nov 21 '18 at 20:11
This is great information. This is the reason why I somehow need to get B+Trees object. So I can manipulate with it later. Many people probably didn't understand what I need. Thanks @RickJames you got vote up from me.
– exp2Tapavicki
Nov 22 '18 at 7:33
@P.Salmon - Yeah, TMI. Sometimes I feel like giving too much info. My Comment is the "Executive Summary" -- sufficient for most people.
– Rick James
Nov 22 '18 at 19:12
add a comment |
I have my doubts about the everyday value of knowing that an index is btree or otherwise or how sql traverses the nodes. At the end of the day you tell sql what you want and the optimiser decides how to do what you want,including whether to use an index or not.
– P.Salmon
Nov 21 '18 at 19:38
B+Trees are excellent for "point queries" and "range queries". Still, the Optimizer may validly choose to ignore your index. Example: Most of the rows are needed anyway; bouncing between the index BTree and the data would be slower than simply scanning all the data.
– Rick James
Nov 21 '18 at 20:11
This is great information. This is the reason why I somehow need to get B+Trees object. So I can manipulate with it later. Many people probably didn't understand what I need. Thanks @RickJames you got vote up from me.
– exp2Tapavicki
Nov 22 '18 at 7:33
@P.Salmon - Yeah, TMI. Sometimes I feel like giving too much info. My Comment is the "Executive Summary" -- sufficient for most people.
– Rick James
Nov 22 '18 at 19:12
I have my doubts about the everyday value of knowing that an index is btree or otherwise or how sql traverses the nodes. At the end of the day you tell sql what you want and the optimiser decides how to do what you want,including whether to use an index or not.
– P.Salmon
Nov 21 '18 at 19:38
I have my doubts about the everyday value of knowing that an index is btree or otherwise or how sql traverses the nodes. At the end of the day you tell sql what you want and the optimiser decides how to do what you want,including whether to use an index or not.
– P.Salmon
Nov 21 '18 at 19:38
B+Trees are excellent for "point queries" and "range queries". Still, the Optimizer may validly choose to ignore your index. Example: Most of the rows are needed anyway; bouncing between the index BTree and the data would be slower than simply scanning all the data.
– Rick James
Nov 21 '18 at 20:11
B+Trees are excellent for "point queries" and "range queries". Still, the Optimizer may validly choose to ignore your index. Example: Most of the rows are needed anyway; bouncing between the index BTree and the data would be slower than simply scanning all the data.
– Rick James
Nov 21 '18 at 20:11
This is great information. This is the reason why I somehow need to get B+Trees object. So I can manipulate with it later. Many people probably didn't understand what I need. Thanks @RickJames you got vote up from me.
– exp2Tapavicki
Nov 22 '18 at 7:33
This is great information. This is the reason why I somehow need to get B+Trees object. So I can manipulate with it later. Many people probably didn't understand what I need. Thanks @RickJames you got vote up from me.
– exp2Tapavicki
Nov 22 '18 at 7:33
@P.Salmon - Yeah, TMI. Sometimes I feel like giving too much info. My Comment is the "Executive Summary" -- sufficient for most people.
– Rick James
Nov 22 '18 at 19:12
@P.Salmon - Yeah, TMI. Sometimes I feel like giving too much info. My Comment is the "Executive Summary" -- sufficient for most people.
– Rick James
Nov 22 '18 at 19:12
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%2f53400402%2fhow-to-get-index-value-for-specific-table-from-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
dev.mysql.com/doc/refman/8.0/en/show-index.html
– P.Salmon
Nov 20 '18 at 19:54
This is how to find index information. Now question is how to get specific index BTREE object(value). After I get this BTREE I will traverse through it. At the moment I don't know how to get BTREE.
– exp2Tapavicki
Nov 21 '18 at 17:24
How do mean traverse through it.?
– P.Salmon
Nov 21 '18 at 17:34
It's not important because I need to get BTREE somehow first.
– exp2Tapavicki
Nov 22 '18 at 7:17