sql
EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 42;Notes
EXPLAIN reveals how the optimizer plans to execute the query.
SQL Indexing and Performance
Query tuning patterns with EXPLAIN, indexing, keyset pagination, deduplication, and optimization-oriented SQL recipes.
Export
Copy the compact sheet, download it, or print it.
Download
`D` dense toggle · `C` copy all
Execution Plans
Inspect how queries run.
Explain with runtime stats
Run a query and collect timing or actual row information where supported.
sqlANYexplain-analyzeperformance
sql
EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 42;Notes
Use carefully in production because the query may actually execute.
Refresh optimizer statistics
Update table statistics for better planning.
sqlANYanalyzestatisticsperformance
sql
ANALYZE orders;Notes
Exact command support varies, but up-to-date statistics matter for good execution plans.
Index Strategy
Choose useful indexes and query shapes.
Index a common lookup column
Speed up frequent equality or range predicates.
sqlANYindexlookupperformance
sql
CREATE INDEX idx_users_email
ON users (email);Notes
Index columns used heavily in WHERE, JOIN, and ORDER BY clauses.
Use a covering index pattern
Create an index that supports filtering and ordering efficiently.
sqlANYindexcomposite-indexcovering-index
sql
CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at);Notes
Composite indexes can satisfy multiple parts of a query pattern.
Avoid wrapping indexed columns in functions
Preserve index usability in predicates.
sqlANYindexperformancepredicates
sql
-- Less index-friendly
SELECT *
FROM users
WHERE LOWER(email) = 'a@example.com';
-- Prefer normalized storage or computed/indexed support where available
SELECT *
FROM users
WHERE email = 'a@example.com';Notes
Applying functions to indexed columns can prevent index usage in many engines.
Pagination, Deduplication, and Search Patterns
Practical scalable query techniques.
Use keyset pagination
Paginate by a stable cursor instead of a large OFFSET.
sqlANYpaginationkeyset-paginationperformance
sql
SELECT *
FROM posts
WHERE (published_at, post_id) < (TIMESTAMP "2025-03-01 10:00:00", 12345)
ORDER BY published_at DESC, post_id DESC
FETCH FIRST 20 ROWS ONLY;Notes
Keyset pagination scales better than OFFSET for deep pages.
Deduplicate rows with ROW_NUMBER
Keep one row per business key and remove duplicates logically.
sqlANYdeduplicationrow-numberwindow-function
sql
WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn
FROM users
)
SELECT *
FROM ranked
WHERE rn = 1;Notes
Useful for data cleanup and canonical row selection.
Search across multiple columns
Apply a simple wildcard search over several text fields.
sqlANYsearchliketext
sql
SELECT *
FROM customers
WHERE first_name LIKE '%john%'
OR last_name LIKE '%john%'
OR email LIKE '%john%';Notes
Simple LIKE search is easy to start with, though full-text search is better at scale.
Data Quality and Maintenance Queries
Checks, audits, and cleanup-oriented SQL.
Find duplicate business keys
Detect repeated values that should be unique.
sqlANYdata-qualityduplicates
sql
SELECT email, COUNT(*) AS duplicate_count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;Notes
This query is often the first step before cleanup and adding a unique constraint.
sql
SELECT o.order_id
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;Notes
Helps audit referential integrity problems.
Delete duplicate rows with a CTE
Keep one row per key and delete the extras.
sqlANYdeleteduplicatescte
sql
WITH ranked AS (
SELECT user_id, email,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at ASC) AS rn
FROM users
)
DELETE FROM users
WHERE user_id IN (
SELECT user_id
FROM ranked
WHERE rn > 1
);Notes
Always test duplicate-removal queries carefully before running them on production data.
More in SQL
SQL Window Functions and CTEs
Use CTEs, recursive queries, ranking, running totals, partitions, and lag/lead patterns for analytical SQL.
SQL DDL and DML
Create and change schema objects, then insert, update, delete, merge, and manage transactional data changes.
SQL Functions and Aggregation
Reference for string, date, numeric, conditional, null-handling, and aggregate SQL functions.
SQL Joins and Subqueries
Advanced relational query patterns for joins, subqueries, EXISTS, IN, set operations, and correlated queries.
SQL Cheat Sheet
Core SQL syntax and patterns for querying, filtering, sorting, joining, grouping, and modifying data.