Most everyday database trouble comes from a handful of ideas: what indexes cost, what a transaction promises, how tables are joined and designed, and what happens when two writers want the same rows. Get those right and the rest of SQL becomes much easier to reason about.
Indexes: faster reads, slower writes
An index is a separate, sorted structure that points back to the rows in a table. Without one, finding every order for customer 42 means reading the whole table row by row, a 'full table scan'. With an index on customer_id, the database jumps straight to the matching entries, the way you use the index at the back of a book instead of reading every page.
CREATE INDEX idx_orders_customer
ON orders (customer_id);
SELECT * FROM orders
WHERE customer_id = 42;That is what an index is for: reading. It doesn't make the table smaller (it takes extra storage of its own), and it does nothing about network latency between your app and the database.
The write cost
Every index is a copy of some of the table's data, and it has to be kept in step with the table. Each INSERT adds an entry to every index on the table, each DELETE removes one, and an UPDATE to an indexed column moves one. A table with ten indexes does ten extra pieces of work on every insert. Indexes also take disk space and memory, competing with the data itself for the cache.
Wondering how this was made? How we make these