sqlANYexplainperformance
sql
EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 42;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;Use carefully in production because the query may actually execute.
sqlANYanalyzestatisticsperformance
sql
ANALYZE orders;Exact command support varies, but up-to-date statistics matter for good execution plans.
Index Strategy
Choose useful indexes and query shapes.
sqlANYindexlookupperformance
sql
CREATE INDEX idx_users_email
ON users (email);Index columns used heavily in WHERE, JOIN, and ORDER BY clauses.
sqlANYindexcomposite-indexcovering-index
sql
CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at);Composite indexes can satisfy multiple parts of a query pattern.
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';Applying functions to indexed columns can prevent index usage in many engines.
Pagination, Deduplication, and Search Patterns
Practical scalable query techniques.
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;Keyset pagination scales better than OFFSET for deep pages.
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;Useful for data cleanup and canonical row selection.
sqlANYsearchliketext
sql
SELECT *
FROM customers
WHERE first_name LIKE '%john%'
OR last_name LIKE '%john%'
OR email LIKE '%john%';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.
sqlANYdata-qualityduplicates
sql
SELECT email, COUNT(*) AS duplicate_count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;This query is often the first step before cleanup and adding a unique constraint.
sqlANYdata-qualityorphaned-rows
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;Helps audit referential integrity problems.
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
);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.