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.
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.