Query Optimization
ELI5 — The Vibe Check
Query optimization is the art of making slow database queries fast. Add an index here, rewrite that subquery as a JOIN, fetch only the columns you need. The goal is to get the same data with far less work. The EXPLAIN command is your best friend.
Real Talk
Query optimization is the process of improving the performance of SQL queries. It involves analyzing execution plans (EXPLAIN ANALYZE), adding appropriate indexes, rewriting inefficient queries, avoiding SELECT *, minimizing N+1 patterns, and understanding how the query planner makes decisions. Both manual and automatic (optimizer) techniques are used.
When You'll Hear This
"The dashboard query was taking 10 seconds — after query optimization it's under 100ms." / "Run EXPLAIN ANALYZE to see what the query planner is doing."
Related Terms
Eager Loading
Eager loading fetches all the related data you need upfront in one or two queries.
Explain Plan
EXPLAIN shows you exactly how the database plans to execute your query — which indexes it uses, how many rows it scans, where it is slow.
Index
A database index is like the index in the back of a book. Without it, the database reads every single row to find what you want.
N+1 Query
N+1 is when your code runs 1 query to get a list of things, then runs 1 more query for EACH thing on the list.
Partitioning
Partitioning divides a huge table into smaller physical chunks while still appearing as one table to your queries.