CODEWITHSUNDEEP
mysqlindexing
vRm
vRm

Reputation: 35

what will be value stored in DB indexes?

I understand that relational DB's like mysql uses hashmap or B+trees for indexing. What will be the value stored in B+tree ? is it hash value of the keys for which indexes are created ? or the key value itself ?

Upvotes: 0

Views: 46

Answers (1)

Rick James
Rick James

Reputation: 142316

Let's talk ENGINE=InnoDB only.

Indexes are BTrees; there are no Hash indexes, nor hashed strings. (There are also FULLTEXT and SPATIAL.)

The data is in a BTree ordered by the PRIMARY KEY. We say that the PK is "clustered".

Each "secondary key" is in a separate BTree, ordered by the key column(s). In the leaf is a copy of the PK, so that it can reach over to get the rest of the columns.

Aside from overhead, an INT takes 4 bytes in the index; a VARCHAR takes enough space for the text, plus a 1- or 2- byte length. Etc.

Yes, typically a VARCHAR is bulkier than an INT. But that is not necessarily bad. For example, if you have a "natural" PK that is VARCHAR, there may (or may not) be justification for instead creating an INT AUTO_INCREMENT as the PK. Show me an example CREATE TABLE; I will critique the indexes.

Upvotes: 1

Related Questions