Database Indexing makes DB Query faster

Database Indexing is one of the most common technique to make database query faster. In this post we will learn about how indexing makes DB Query faster.

Let’s make a user table,

6Juman jumanjuman@gmail.comedeuhdueh

You can consider each column has,

4 bytes32 bytes16 bytes48 bytes32 bytes

Each column has different bytes. The total bytes will be (4 + 32 + 16 + 48 + 32) = 132 bytes. Since we have 6 rows so we need (6 * 132) = 792 bytes in order to store the entire user table.

Generally our data is stored inside the Disk and inside of the disk we have a collection of blocks. Each block holds the certain amount of data. Depending on how many rows, there are number of blocks available.

In our example we have 6 rows and let’s say each block can hold 265 bytes of data. Since we’ve 6 rows and each row has 132 bytes,

265 / 132 = 2, so we can say each block can hold 2 rows.

This is how data stored inside blocks.

Let’s write a query,

SELECT * FROM users WHERE username=juman

We are collecting every information who has juman username. The query process will be,

  • Search the Block 1, not found.
  • Search the Block 2, not found.
  • Search the Block 3, found.
  • Then put the entire Block 3 to memory for further process.

Let’s suppose reading/searching a block can take 1 sec, since we searched 3 blocks it will take 1 * 3 = 3 sec. (What if we have 100 blocks then it will take 100 sec in worst case scenario.)

Now let’s optimize this through index.

Index is a referential table that holds row reference against a particular index value. Index is also stored inside the disk block. So for username the index table will be,


For each column it the size can be,

4 bytes4 bytes

So username has 4 bytes and block_id has also 4 bytes, so each column requires 8 bytes.

As we have 6 rows so the total size of the index table will be 6 * 8 = 48. Let’s suppose each block can hold 25 bytes of data. So we total number of blocks required for index is 48 / 25 = 2.

As our query is to find information for username juman. So the query process will be,

  • Searching the first index block, it will see username juman has block_id 3.
  • It will go to the data block 3.
  • Then put the entire Block 3 to memory for further process.

So we just searched index block 1 and data block 3. If we suppose each block read can take 1 second reading 2 blocks (index block 1 and data block 3) can take 2 seconds, in worst case scenario this is better than searching 100 blocks in 100 seconds.

This is how the index works.

Leave a Comment

Your email address will not be published. Required fields are marked *