Glossary · Database

Query Optimization

The process of improving a SQL query's execution speed and resource usage without changing the results.

Query optimization involves analyzing and restructuring SQL queries to minimize execution time, memory usage, and I/O operations. This includes choosing efficient join strategies, leveraging indexes, reducing table scans, optimizing subqueries, using appropriate aggregation methods, and considering execution plan costs. Database engines have built-in query optimizers that analyze queries and choose execution plans, but the initial query structure significantly impacts what the optimizer can achieve. Common optimization techniques include adding indexes, rewriting correlated subqueries as joins, using CTEs for readability and performance, partitioning large tables, and denormalizing for read-heavy workloads.

Fig — every answer ships with the tables, rows and SQL behind it.