Hi folks, Today we’ll learn how indexing works in a database. we have heard a lot of times when our database grows our query gets slower and then we gotta have to index the database to make the query faster. let’s just understand how indexing makes our query faster.
Let’s suppose you have 10000 rows in a table and each row consists of 20 bytes. which contains 10000*20= 200000 bytes. we know all the data gets stored on disc and i/o operation on disc is slower. our disc stored data in blocks let’s suppose each block size is 40 bytes.
blocks needed to store 200000 bytes = 200000/40 = 50000
our data will be stored as
each block size is 40 and the row size is 20 so each block will have 2 rows. To find roll no 89. we need to travel 4 blocks. and let’s suppose each block takes 2 seconds for i/o operation (it is way much less than 2 seconds in actuality. This is just for understanding) then we would have taken 8(2*4blocks) seconds to find roll no 89.
To make this faster here comes the indexing. indexing is a data structure technique that allows for quick access to specific data in a database. It works by minimizing the number of searches required to query records.
we will be mapping roll no with ID to minimize the searches. here is how mapping will look like
This mapping will also be stored in a disc. let’s just see how much block it will take.
each row will have an 8-byte size (4+4). we have 10000 rows which means 80000 bytes.
blocks needed to store the mapping = 80000/40 = 2000
each block can consist of 5 rows (40-byte size of block / 8-byte size 1 row ).
Now if we need to search roll no 89. we would have to travel 2 blocks and we know each block takes 2 seconds for i/o which means it will gonna take 4 seconds to get roll no 89.
Without indexing getting roll no 89 was taking us 8 seconds and with indexing it is fetching results in 4 seconds that’s 50% less time.
there are a lot of ways we can make it even faster by designing better indexing for our database. this is just to understand the concept better.