How database indexing actually works internally?
Let's start with a simple example showing why you would need indexing in a database.
Suppose we have a table called costing with three columns company_id, unit, unit_cost which is completely unordered.
If we run the below query -
SELECT
company_...
pankajtanwar.hashnode.dev5 min read
Michael Nielson
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.