Database Indexes: Why They Make Reads Faster and Writes Slower
How indexes avoid expensive table scans—and why adding too many can hurt write performance.
Your database is scanning every row in your table to answer your query.
If your table has 10 million rows and you are looking for one record, the database reads all 10 million before returning the result. That is what happens without an index.
10,000,000 rows read to return 1.
Think of it like a phone book with no alphabetical order. To find Ahmed Tawila you start at page 1 and read every name until you find it. It works — but it is exhausting and slow.
Now imagine the phone book is sorted alphabetically. You know A is at the front, M is in the middle, T is around three quarters in. You open somewhere near T, adjust, and find the name in seconds. You read maybe 20 pages instead of 1,000.
That is exactly what a database index does. It keeps a sorted copy of a column's values with pointers back to the full row. When a query hits that column, the database jumps directly to the right region instead of reading everything.
But indexes are not free.
Every time you insert, update, or delete a row, the database must update every index on that table. A table with 8 indexes pays 8 write operations for every 1 row change. Over-indexing is a real production problem that slows writes silently as the table grows.
The rule is simple: index the columns your queries filter on most. Leave the rest alone until you have evidence they need it.
A missing index costs you read speed. An unnecessary index costs you write speed. The job is knowing which columns deserve one.