Skip to content
anythingai.space
Backend for AIintermediate11 min · 10 steps

Database Indexing

An index is a sorted side structure that lets the database jump to the rows a query needs instead of scanning the table: faster reads, paid for with storage and slower writes.

query1,800ms
pages readall
scan + sortEVERY ROW IS READ; ONLY A FEW MATCHE00213E77120E12345E50091E31007E90412E12345E08815E66402E41200… 2 million rowsQuerylast 20 for E12345messages2,000,000 rowsseq scan
heavy workrequestdataresponsesignal
metrics are illustrative
1 / 10
ProblemStep 1 of 10

Reading 2 million rows to return 20

The chat UI asks for the last 20 messages for user E12345, newest first. The messages table has 2 million rows and no suitable index.

So the database does a sequential scan: it reads every row, keeps the ones for E12345, sorts them by time and returns 20. About 1,800 ms (illustrative), and it gets slower every day the table grows.

SELECT * FROM messages
WHERE user_id = 'E12345'
ORDER BY created_at DESC
LIMIT 20;

Lesson 9 of 20 in Backend for AI