𝗗𝗼 𝗜 𝗸𝗻𝗼𝘄 𝗺𝘆 𝗗𝗮𝘁𝗮𝗯𝗮𝘀𝗲?
A series to understand the working of different databases. Starting this after being inspired from Hussein Nasser as he often points out how different implementaions of features/functions in a DBMS can affect performance if you are unaware of the details in his videos.
Today I want to start by sharing some details of the index of MySQL.
Major reference : one of the developers of MySQL, Jeremy Cole ( blog.jcole.us/about-me/ ).
Some general things to keep in mind for an index in MySQL (and its database engine InnoDB) :
If a table has no PRIMARY KEY or suitable UNIQUE index, InnoDB generates a hidden clustered index named GEN_CLUST_INDEX on a synthetic column that contains row ID values. The rows are ordered by the row ID that InnoDB assigns. The row ID is a 6-byte field that increases monotonically as new rows are inserted.
InnoDB (MySQL’s default database engine, not MyISAM) table storage is organized based on the values of the primary key columns, to speed up queries and sorts involving the primary key columns. For best performance, choose the primary key columns carefully based on the most performance-critical queries. Because modifying the columns of the clustered index is an expensive operation, choose primary columns that are rarely or never updated.
When new records are inserted into an InnoDB clustered index, InnoDB tries to leave 1/16 of the page free for future insertions and updates of the index records. If index records are inserted in a sequential order (ascending or descending), the resulting index pages are about 15/16 full. If records are inserted in a random order, the pages are from 1/2 to 15/16 full.
(check out impact of ordered vs random insertion in innodb)
will keep it short because of the word limit.
