CODEWITHSUNDEEP
mysqlindexingpaginationcursor
exp2Tapavicki
exp2Tapavicki

Reputation: 311

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?

Upvotes: 0

Views: 749

Answers (1)

Rick James
Rick James

Reputation: 142398

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.

Upvotes: 1

Related Questions