I had a hard time titling this question but I hope its appropriate.

I have a table of transactions, and each transaction has a Date column (of type Date).

I want to run a query that gets the latest 100 transactions by date (simple enough with an ORDERBY query).

My question is, in order to make this an extremely cheap operation, would it make sense to sort my entire table so that I just need to select the top 100 rows every time, or do i simply create an index on the date column? Not sure if first option is even possible and or/good sql db practice.

CodePudding user response：

You would add an index on the column with the date and query:

SELECT * FROM tab
ORDER BY datecol DESC
LIMIT 100;

The problem with your other idea is that there is no well-defined order in a table. Every UPDATE changes this "order", and even if you don't modify anything, a sequential scan need not start at the beginning of the table.