Database Btree Indexing in SQLite

In specialty in B+tree is, you can do the search same as Btree and additionally, you can travel through all the values in leaf node if we put a pointer to each leaf nodes as follows.B+tree with leaf node referencingHow indexing used in a database?When Btree comes to the database indexing, this data structure getting little complicated by not just having a key, but also has a value associated with the key..this value is a reference to the actual data record..This key and value together are called a payload.Assume following three column table need to store on a database.┌───────┬───────┬─────┐│ Name │ Marks │ Age │├───────┼───────┼─────┤│ Jone │ 5 │ 28 ││ Alex │ 32 │ 45 ││ Tom │ 37 │ 23 ││ Ron │ 87 │ 13 ││ Mark │ 20 │ 48 ││ Bob │ 89 │ 32 │└───────┴───────┴─────┘First, the database creates a unique random index(or primary key) for each of the given record and convert relevant rows into a byte stream..Then it stores each of the key and record byte stream on a B+tree..Here, the random index used as the key for indexing..The key and record byte stream altogether know as Payload..Resulting B+tree could be represented as follows.B+tree on database pagesHere you can see, all records are stored in the leaf nodes of the B+tree and index used as the key to creating a B+tree..No records are stored on non-leaf nodes..Each of the leaf nodes has reference to next record in the tree..A database can perform a binary search by using the index or sequential search by searching through every element by only traveling through the leaf nodes.If no indexing used, Then database read each of these records to find the given record..When indexing enabled, then the database creates three Btrees for each of the columns in the table as follows..Here the key is the Btree key used to indexing.. More details

Leave a Reply