Query Optimization

IBM Db2

IBM Db2 is a family of relational database management systems from IBM, historically rooted in mainframe computing, that supports transactional and analytical…

Teradata

Teradata is a massively parallel processing (MPP) relational database platform designed for large-scale enterprise data warehousing and analytics, originally s…

Sharding

Sharding is a database scaling technique that splits a large dataset horizontally across multiple independent database instances (shards), each holding a subse…

Database Indexing

Database indexing is the technique of creating auxiliary data structures — most commonly B-trees or hash tables — that let a database engine locate rows matchi…

Query Optimization

Query optimization is the process of improving how a database query is written and executed so it returns correct results using the least amount of time, memor…

What is an Index in a Database?

A database index is a data structure that speeds up read queries by letting the database find rows without scanning the entire table, at the cost of extra stor…

What is Denormalization in Database?

Denormalization is the deliberate process of combining data from multiple related tables back into fewer, wider tables to reduce joins and speed up read querie…

HAVING vs WHERE Clause

WHERE filters individual rows before any grouping or aggregation happens, while HAVING filters groups after GROUP BY has aggregated them, so HAVING is the only…

What is a Cursor in SQL?

A cursor is a database object that lets a program process a query result set one row at a time, instead of the usual set-based operation that acts on all match…

What is the Difference Between GROUP BY and ORDER BY?

GROUP BY aggregates rows that share a common value into summary rows, while ORDER BY simply sorts the result set without changing how many rows are returned.

What is a Materialized View?

A materialized view is a database object that stores the physical result of a query on disk, unlike a regular view which recomputes its result every time it is…

What is a Covering Index in SQL?

A covering index is an index that includes every column a query needs — both the filter/sort columns and the selected columns — so the database engine can answ…

What Are Index Selectivity and Cardinality?

Cardinality is the number of distinct values in a column, and selectivity is the ratio of distinct values to total rows — a high-selectivity column (close to 1…

What is a Query Execution Plan?

A query execution plan is the step-by-step, low-level operation tree the database engine builds to actually run a SQL statement — which access paths, join orde…

What Does EXPLAIN ANALYZE Do?

EXPLAIN ANALYZE actually runs the query and returns the real execution plan annotated with true measured timings and row counts for every operator, unlike plai…

Cost-Based vs Rule-Based Query Optimization: What is the Difference?

A rule-based optimizer picks an execution plan by applying a fixed priority ranking of access methods regardless of the actual data, while a cost-based optimiz…

Index Scan vs Sequential Scan: When Does the Optimizer Choose Each?

A sequential scan reads every row of a table in physical order to find matches, while an index scan uses an index structure to jump directly to only the rows t…

How Does the Nested Loop Join Algorithm Work?

A nested loop join works by iterating over every row of an outer table and, for each one, scanning or probing an inner table to find matching rows, making it t…

What is a Recursive CTE and When Would You Use One?

A recursive CTE is a Common Table Expression that references itself, repeatedly building on its own previous output until no new rows are produced, making it t…

CTE vs Subquery: Which Performs Better?

Neither a CTE nor a subquery is inherently faster; on most modern optimizers, including PostgreSQL 12+ and SQL Server, a non-recursive CTE is inlined into the…

CTE vs Temporary Table: What is the Difference?

A CTE is a named result set scoped to a single statement with no storage or indexing of its own, while a temporary table is a real, physically stored table tha…

What Does It Mean for a CTE to be Materialized?

A materialized CTE is one the database engine fully computes and holds in a temporary work area exactly once, before the outer query runs against it, as oppose…

UNION vs UNION ALL: What is the Difference?

UNION combines the result sets of two or more SELECT queries and removes duplicate rows, while UNION ALL combines them and keeps every row, including duplicate…

What is the Difference Between a Semi-Join and an Anti-Join?

A semi-join returns rows from the first table that have at least one matching row in the second table, without duplicating rows or pulling in any columns from…

What is a Reliable Methodology for Tuning a Slow SQL Query?

A reliable query-tuning methodology starts with measuring, not guessing: capture the query's actual execution plan, identify the single most expensive operatio…

How Do You Analyze a Slow Query Log to Find Optimization Targets?

Analyzing a slow query log means aggregating logged queries by their normalized pattern and ranking them by total cumulative time, not by the single slowest in…

What is the N+1 Query Problem and How Do You Fix It?

The N+1 query problem is a performance anti-pattern where code runs one query to fetch a list of N parent records, then runs one additional query per parent to…

ORM Lazy Loading vs Eager Loading: What is the Difference?

Lazy loading fetches an object's related data only at the moment it is actually accessed in code, issuing a separate query on demand, while eager loading fetch…

What Query Batching Strategies Reduce Database Round Trips?

Query batching combines what would be many individual database round trips into fewer, larger operations — such as a single multi-row INSERT, a batched IN clau…