How database indexing actually works internally?

so well explained 👏️

nice article and nice graphics !

Good article Pankaj. Can you throw some more light on last section indexing pitfalls. Especially why do we need index over amount also

This was a great read. Followed you. And bookmarked this.

Wonderfully explained! :) Bookmarked!

This is great. Bookmarked

This is misleading:

But, It will still do a full table scan because we are using "amount" column to calculate the total. So, we need to put an index on "amount" column too.

Two indexes don't add together (you might look up covering index).

There are also very legitimate reasons why the database will ignore an index that could be applied and do a full table scan anyway.

You've explained a few behaviors of indexes but not really how they work.