Run a query with no index and watch the scanner sweep every row; add an index and it jumps straight to the answer. Grow the table and the scan explodes while the index stays flat.
ChatSphere's feed query is slow. Pick an index, pick the query, grow the table, and watch how many rows the database actually has to read to answer it.
Query
Index
100,000 rows
Rows read
100,000
Of table
100%
vs full scan
1×
faster
The whole table — red = rows the engine had to read
Full table scan — reading all 100,000 rows to find 50. Grows with every new post.
What just happened
▹Without an index the database reads every row — a full table scan. Cost grows linearly with the table, so a query that's fine at 1k rows crawls at 1M.
▹An index lets the engine jump straight to matching rows instead of scanning. The right index turns a million-row scan into reading a few hundred rows.
▹A composite index must match the query's columns and order. An index on (user_id, status) answers a user_id+status filter directly; a single-column index still has to scan the user's rows to check status.