Optimizing Sql

SQL Optimization

Provide tactical guidance for optimizing SQL query performance across PostgreSQL, MySQL, and SQL Server through execution plan analysis, strategic indexing, and query rewriting.

When to Use This Skill

Trigger this skill when encountering:

• Slow query performance or database timeouts
• Analyzing EXPLAIN plans or execution plans
• Determining index requirements
• Rewriting inefficient queries
• Identifying query anti-patterns (N+1, SELECT *, correlated subqueries)
• Database-specific optimization needs (PostgreSQL, MySQL, SQL Server)

Core Optimization Workflow

Step 1: Analyze Query Performance

Run execution plan analysis to identify bottlenecks:

PostgreSQL:

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';

MySQL:

EXPLAIN FORMAT=JSON SELECT * FROM products WHERE category_id = 5;

SQL Server: Use SQL Server Management Studio: Display Estimated Execution Plan (Ctrl+L)

Key Metrics to Monitor:

• Cost: Estimated resource consumption
• Rows: Number of rows processed (estimated vs actual)
• Scan Type: Sequential scan vs index scan
• Execution Time: Actual time spent on operation

For detailed execution plan interpretation, see references/explain-guide.md.

Step 2: Identify Optimization Opportunities

Common Red Flags:

For scan type interpretation, see references/scan-types.md.

Step 3: Apply Indexing Strategies

Index Decision Framework:

Is column used in WHERE, JOIN, ORDER BY, or GROUP BY?
├─ YES → Is column selective (many unique values)?
│  ├─ YES → Is table frequently queried?
│  │  ├─ YES → ADD INDEX
│  │  └─ NO → Consider based on query frequency
│  └─ NO (low selectivity) → Skip index
└─ NO → Skip index

Index Types by Use Case:

PostgreSQL:

• B-tree (default): General-purpose, supports <, ≤, =, ≥, >, BETWEEN, IN
• Hash: Equality comparisons only (=)
• GIN: Full-text search, JSONB, arrays
• GiST: Spatial data, geometric types
• BRIN: Very large tables with naturally ordered data

MySQL:

• B-tree (default): General-purpose index
• Full-text: Text search on VARCHAR/TEXT columns
• Spatial: Spatial data types

SQL Server:

• Clustered: Table data sorted by index (one per table)
• Non-clustered: Separate index structure (multiple allowed)

For comprehensive indexing guidance, see references/indexing-decisions.md and references/index-types.md.

Step 4: Design Composite Indexes

For queries filtering on multiple columns, use composite indexes:

Column Order Matters: 1. Equality filters first (most selective) 2. Additional equality filters (by selectivity) 3. Range filters or ORDER BY (last)

Example:

-- Query pattern
SELECT * FROM orders
WHERE customer_id = 123 AND status = 'shipped'
ORDER BY created_at DESC
LIMIT 10;

-- Optimal composite index
CREATE INDEX idx_orders_customer_status_created
ON orders (customer_id, status, created_at DESC);

For composite index design patterns, see references/composite-indexes.md.

Step 5: Rewrite Inefficient Queries

Common Anti-Patterns to Avoid:

*1. SELECT (Over-fetching)**

-- ❌ Bad: Fetches all columns
SELECT * FROM users WHERE id = 1;

-- ✅ Good: Fetch only needed columns
SELECT id, name, email FROM users WHERE id = 1;

2. N+1 Queries

-- ❌ Bad: 1 + N queries
SELECT * FROM users LIMIT 100;
-- Then in loop: SELECT * FROM posts WHERE user_id = ?;

-- ✅ Good: Single JOIN
SELECT users.*, posts.id AS post_id, posts.title
FROM users
LEFT JOIN posts ON users.id = posts.user_id;

3. Non-Sargable Queries (functions on indexed columns)

-- ❌ Bad: Function prevents index usage
SELECT * FROM orders WHERE YEAR(created_at) = 2025;

-- ✅ Good: Sargable range condition
SELECT * FROM orders
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';

4. Correlated Subqueries

-- ❌ Bad: Subquery executes per row
SELECT name,
  (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id)
FROM users;

-- ✅ Good: JOIN with GROUP BY
SELECT users.name, COUNT(orders.id) AS order_count
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.id, users.name;

For complete anti-pattern reference, see references/anti-patterns.md. For efficient query patterns, see references/efficient-patterns.md.

Quick Reference Tables

Index Selection Guide

Join Optimization Checklist

• [ ] Index foreign key columns on both sides of JOIN
• [ ] Order joins starting with table returning fewest rows
• [ ] Use INNER JOIN when possible (more efficient than OUTER JOIN)
• [ ] Avoid joining more than 5 tables (break into CTEs or subqueries)
• [ ] Consider denormalization for frequently joined tables in read-heavy systems

Execution Plan Performance Targets

Database-Specific Optimizations

PostgreSQL-Specific Features

Partial Indexes (index subset of rows):

CREATE INDEX idx_active_users_login
ON users (last_login)
WHERE status = 'active';

Expression Indexes (index computed values):

CREATE INDEX idx_users_email_lower
ON users (LOWER(email));

Covering Indexes (avoid heap access):

CREATE INDEX idx_users_email_covering
ON users (email) INCLUDE (id, name);

For comprehensive PostgreSQL optimization, see references/postgresql.md.

MySQL-Specific Features

Index Hints (override optimizer):

SELECT * FROM orders USE INDEX (idx_orders_customer)
WHERE customer_id = 123;

Storage Engine Selection:

• InnoDB (default): Transactional, row-level locks, clustered primary key
• MyISAM: Faster reads, no transactions, table-level locks

For comprehensive MySQL optimization, see references/mysql.md.

SQL Server-Specific Features

Query Store (track query performance over time):

ALTER DATABASE YourDatabase SET QUERY_STORE = ON;

Execution Plan Warnings:

• Look for yellow exclamation marks in graphical execution plans
• Thick arrows indicate high row counts

For comprehensive SQL Server optimization, see references/sqlserver.md.

Advanced Optimization Techniques

Common Table Expressions (CTEs)

Break complex queries into readable, maintainable parts:

WITH active_customers AS (
  SELECT id, name FROM customers WHERE status = 'active'
),
recent_orders AS (
  SELECT customer_id, COUNT(*) as order_count
  FROM orders
  WHERE created_at > NOW() - INTERVAL '30 days'
  GROUP BY customer_id
)
SELECT ac.name, COALESCE(ro.order_count, 0) as orders
FROM active_customers ac
LEFT JOIN recent_orders ro ON ac.id = ro.customer_id;

EXISTS vs IN for Subqueries

Use EXISTS for better performance with large datasets:

-- ✅ Good: EXISTS stops at first match
SELECT * FROM users
WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id);

-- ❌ Less efficient: IN builds full list
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders);

Denormalization Decision Framework

Consider denormalization when:

• Query joins 3+ tables frequently
• Data is relatively static (infrequent updates)
• Read performance is critical
• Write overhead is acceptable

Denormalization Strategies: 1. Duplicate columns: Copy foreign key data into main table 2. Summary tables: Pre-aggregate data 3. Materialized views: Database-maintained denormalized views 4. Application caching: Redis/Memcached for frequently accessed data

Optimization Workflow Example

Scenario: API endpoint taking 2 seconds to load

Step 1: Identify Slow Query

Use APM/observability tools to identify database query causing delay

Step 2: Run EXPLAIN ANALYZE

EXPLAIN ANALYZE SELECT * FROM orders
WHERE customer_id = 123
ORDER BY created_at DESC
LIMIT 10;

Step 3: Analyze Output

Seq Scan on orders (cost=0.00..2500.00 rows=10)
  Filter: (customer_id = 123)
  Rows Removed by Filter: 99990

Problem: Sequential scan filtering 99,990 rows

Step 4: Add Composite Index

CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at DESC);

Step 5: Verify Improvement

EXPLAIN ANALYZE SELECT * FROM orders
WHERE customer_id = 123
ORDER BY created_at DESC
LIMIT 10;

Index Scan using idx_orders_customer_created (cost=0.42..12.44 rows=10)
  Index Cond: (customer_id = 123)

Result: 200x faster (2000ms → 10ms)

Monitoring and Maintenance

Regular Optimization Tasks:

• Review slow query logs weekly
• Update database statistics regularly (ANALYZE in PostgreSQL, UPDATE STATISTICS in SQL Server)
• Monitor index usage (drop unused indexes)
• Archive old data to keep tables manageable
• Review execution plans for critical queries quarterly

PostgreSQL Statistics Update:

ANALYZE table_name;

MySQL Statistics Update:

ANALYZE TABLE table_name;

SQL Server Statistics Update:

UPDATE STATISTICS table_name;

Related Skills

• databases-relational: Schema design and database fundamentals
• observability: Performance monitoring and slow query detection
• api-patterns: API-level optimization (pagination, caching)
• performance-engineering: Application performance profiling

Additional Resources

For comprehensive documentation, reference these files:

• references/explain-guide.md - Detailed EXPLAIN plan interpretation
• references/scan-types.md - Scan type meanings and performance implications
• references/indexing-decisions.md - When and how to add indexes
• references/index-types.md - Database-specific index types
• references/composite-indexes.md - Multi-column index design
• references/anti-patterns.md - Common anti-patterns with solutions
• references/efficient-patterns.md - Efficient query patterns
• references/postgresql.md - PostgreSQL-specific optimizations
• references/mysql.md - MySQL-specific optimizations
• references/sqlserver.md - SQL Server-specific optimizations

For working SQL examples, see examples/ directory.

SQL Anti-Patterns

Common SQL performance anti-patterns with explanations, impact analysis, and solutions.

Table of Contents

1. SELECT * Anti-Pattern 2. N+1 Query Problem 3. Missing Indexes on Foreign Keys 4. Non-Sargable Queries 5. Implicit Type Conversion 6. Correlated Subqueries 7. Unnecessary DISTINCT 8. OR vs IN Performance 9. NOT IN with NULL Values 10. Wildcard at Start of LIKE

SELECT * Anti-Pattern

Problem Description

Fetching all columns when only subset needed.

Anti-Pattern:

-- ❌ Bad: Fetches all 50 columns
SELECT * FROM users WHERE id = 1;

Impact:

• Increased I/O (reading unnecessary data from disk)
• Higher network transfer (sending unnecessary data)
• More memory usage (larger result sets)
• Slower query execution
• Breaks application when schema changes

Solution:

-- ✅ Good: Fetch only needed columns
SELECT id, name, email, created_at FROM users WHERE id = 1;

When SELECT * is Acceptable

Exception 1: Small tables with few columns

SELECT * FROM settings;  -- OK: 3-5 columns, small table

Exception 2: Exploratory queries (development only)

-- OK during development/debugging
SELECT * FROM users LIMIT 5;

Exception 3: All columns genuinely needed

-- OK if truly need every column
SELECT * FROM user_profiles WHERE user_id = 123;

Performance Comparison

Test Case: Users table with 50 columns, 100,000 rows

-- SELECT * : 250ms, 500MB transferred
SELECT * FROM users WHERE status = 'active';

-- Specific columns: 50ms, 50MB transferred
SELECT id, name, email FROM users WHERE status = 'active';

Result: 5x performance improvement

N+1 Query Problem

Problem Description

Executing 1 query to fetch parent records, then N queries (one per parent) to fetch related records.

Anti-Pattern:

-- ❌ Bad: 1 + N queries

-- Query 1: Fetch users
SELECT * FROM users LIMIT 100;

-- Query 2-101: For each user, fetch posts (executed 100 times in application loop)
SELECT * FROM posts WHERE user_id = ?;

Impact:

• 101 database round trips instead of 1
• Network latency multiplied by N
• Database connection overhead × N
• Poor scalability (linear growth with N)

Solution 1: Single JOIN

-- ✅ Good: Single query with JOIN
SELECT
  users.id,
  users.name,
  posts.id AS post_id,
  posts.title,
  posts.content
FROM users
LEFT JOIN posts ON users.id = posts.user_id
WHERE users.id IN (1, 2, 3, ...);

Solution 2: Separate Queries with IN Clause

-- ✅ Also good: 2 queries instead of N+1
-- Query 1: Fetch users
SELECT * FROM users LIMIT 100;

-- Query 2: Fetch all posts for these users
SELECT * FROM posts WHERE user_id IN (1, 2, 3, ..., 100);

N+1 Detection

Rails ActiveRecord Example:

# ❌ N+1 Problem
users = User.limit(100)
users.each do |user|
  puts user.posts.count  # Triggers N queries
end

# ✅ Fixed with eager loading
users = User.includes(:posts).limit(100)
users.each do |user|
  puts user.posts.count  # Uses preloaded data
end

Django ORM Example:

# ❌ N+1 Problem
users = User.objects.all()[:100]
for user in users:
    print(user.posts.count())  # Triggers N queries

# ✅ Fixed with select_related / prefetch_related
users = User.objects.prefetch_related('posts').all()[:100]
for user in users:
    print(user.posts.count())  # Uses preloaded data

Performance Comparison

Test Case: 100 users, average 5 posts each

-- N+1 approach: 101 queries, ~1000ms
-- JOIN approach: 1 query, ~50ms

Result: 20x performance improvement

Missing Indexes on Foreign Keys

Problem Description

Foreign key columns without indexes cause slow joins and cascading operations.

Anti-Pattern:

-- ❌ Bad: No index on foreign key
CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,  -- No index!
  total DECIMAL(10,2),
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Impact:

• Slow joins (sequential scan on orders table)
• Slow cascading deletes (must scan entire orders table)
• Slow cascading updates
• Poor performance for queries filtering by customer_id

Solution:

-- ✅ Good: Index on foreign key
CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  total DECIMAL(10,2),
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE INDEX idx_orders_customer ON orders (customer_id);

Why This Matters

Query Without Index:

EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

Seq Scan on orders  (cost=0.00..10000.00 rows=100)
  Filter: (customer_id = 123)

100,000 rows scanned

Query With Index:

EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

Index Scan using idx_orders_customer on orders  (cost=0.42..12.44 rows=100)
  Index Cond: (customer_id = 123)

100 rows accessed directly

Cascading Delete Performance

Without Index:

DELETE FROM customers WHERE id = 123;
-- Scans entire orders table to find matching rows
-- Time: 500ms for 1 million row orders table

With Index:

DELETE FROM customers WHERE id = 123;
-- Uses index to find matching rows directly
-- Time: 5ms

Result: 100x faster cascading deletes

Non-Sargable Queries

Problem Description

Sargable: Search ARGument ABLE - conditions that can use indexes.

Non-sargable: Functions or operations on indexed columns prevent index usage.

Anti-Pattern 1: Function on Indexed Column

Anti-Pattern:

-- ❌ Bad: Function prevents index usage
SELECT * FROM orders WHERE YEAR(created_at) = 2025;

Why It Fails:

• Index on created_at cannot be used
• Database must evaluate YEAR() for every row
• Results in sequential scan

Solution:

-- ✅ Good: Sargable range condition
SELECT * FROM orders
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';

Anti-Pattern 2: Arithmetic on Indexed Column

Anti-Pattern:

-- ❌ Bad: Arithmetic prevents index usage
SELECT * FROM products WHERE price * 1.1 > 100;

Solution:

-- ✅ Good: Move arithmetic to other side
SELECT * FROM products WHERE price > 100 / 1.1;

Anti-Pattern 3: String Concatenation

Anti-Pattern:

-- ❌ Bad: Concatenation prevents index usage
SELECT * FROM users WHERE first_name || ' ' || last_name = 'John Doe';

Solution 1: Separate Conditions

-- ✅ Better: Use separate conditions
SELECT * FROM users WHERE first_name = 'John' AND last_name = 'Doe';

Solution 2: Computed Column (SQL Server, MySQL)

-- Add computed column with index
ALTER TABLE users ADD full_name AS (first_name + ' ' + last_name);
CREATE INDEX idx_users_full_name ON users (full_name);

Anti-Pattern 4: LIKE with Leading Wildcard

Anti-Pattern:

-- ❌ Bad: Leading wildcard prevents index usage
SELECT * FROM users WHERE email LIKE '%@example.com';

Why It Fails:

• Cannot use B-tree index for prefix search
• Must scan entire table

Solution 1: Trailing Wildcard (if applicable)

-- ✅ Good: Trailing wildcard can use index
SELECT * FROM users WHERE email LIKE 'john%';

Solution 2: Full-Text Index

-- PostgreSQL: Use trigram index
CREATE INDEX idx_users_email_trgm ON users USING GIN (email gin_trgm_ops);
SELECT * FROM users WHERE email LIKE '%@example.com';

-- MySQL: Full-text index
CREATE FULLTEXT INDEX idx_users_email_fulltext ON users (email);
SELECT * FROM users WHERE MATCH(email) AGAINST('example.com');

Sargable vs Non-Sargable Examples

Implicit Type Conversion

Problem Description

Comparing different data types forces type conversion, preventing index usage.

Anti-Pattern:

-- ❌ Bad: user_id is INT, '123' is VARCHAR
SELECT * FROM users WHERE user_id = '123';

Why It Fails:

• Database converts every row's user_id to string
• Index on user_id cannot be used efficiently
• Sequential scan likely

EXPLAIN Output:

Seq Scan on users  (cost=0.00..1500.00 rows=1)
  Filter: ((user_id)::text = '123'::text)

Solution:

-- ✅ Good: Matching types
SELECT * FROM users WHERE user_id = 123;

EXPLAIN Output:

Index Scan using idx_users_id on users  (cost=0.42..8.44 rows=1)
  Index Cond: (user_id = 123)

Common Type Mismatch Scenarios

Scenario 1: String to Number

-- ❌ Bad
SELECT * FROM orders WHERE order_id = '12345';  -- order_id is INT

-- ✅ Good
SELECT * FROM orders WHERE order_id = 12345;

Scenario 2: Date String Comparison

-- ❌ Bad: String comparison on DATE column
SELECT * FROM events WHERE event_date = '2025-01-01';  -- Implicit conversion

-- ✅ Good: Explicit DATE type
SELECT * FROM events WHERE event_date = DATE '2025-01-01';

Scenario 3: UUID Format

-- PostgreSQL: UUID column
-- ❌ Bad
SELECT * FROM records WHERE uuid_column = 'a1b2c3d4-e5f6-7890-abcd-ef1234567890';

-- ✅ Good
SELECT * FROM records WHERE uuid_column = 'a1b2c3d4-e5f6-7890-abcd-ef1234567890'::uuid;

Correlated Subqueries

Problem Description

Subquery executes once per row in outer query, resulting in poor performance.

Anti-Pattern:

-- ❌ Bad: Correlated subquery
SELECT
  name,
  (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count
FROM users;

Why It Fails:

• Subquery executes once per user
• For 10,000 users → 10,000 subquery executions
• Even with indexes, overhead is significant

Solution 1: JOIN with GROUP BY

-- ✅ Good: Single query with JOIN
SELECT
  users.name,
  COUNT(orders.id) AS order_count
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.id, users.name;

Solution 2: Lateral Join (PostgreSQL)

-- ✅ Good: Lateral join for complex subqueries
SELECT
  users.name,
  recent_orders.order_count
FROM users
LEFT JOIN LATERAL (
  SELECT COUNT(*) AS order_count
  FROM orders
  WHERE orders.user_id = users.id
    AND orders.created_at > NOW() - INTERVAL '30 days'
) recent_orders ON true;

Performance Comparison

Test Case: 10,000 users, 100,000 orders

-- Correlated subquery: ~5000ms
-- JOIN with GROUP BY: ~100ms

Result: 50x performance improvement

Unnecessary DISTINCT

Problem Description

Using DISTINCT when data is already unique adds expensive deduplication step.

Anti-Pattern:

-- ❌ Bad: DISTINCT on primary key (already unique)
SELECT DISTINCT id FROM users WHERE status = 'active';

Why It Fails:

• DISTINCT requires sorting or hashing
• Unnecessary overhead when uniqueness guaranteed
• Wasted CPU and memory

Solution:

-- ✅ Good: Remove DISTINCT (id is unique)
SELECT id FROM users WHERE status = 'active';

When DISTINCT is Necessary

Necessary Use Case:

-- ✅ Good: DISTINCT needed for duplicate customer_ids
SELECT DISTINCT customer_id FROM orders WHERE status = 'completed';

Alternative (often better):

-- ✅ Better: Use GROUP BY (can add aggregations)
SELECT customer_id FROM orders WHERE status = 'completed' GROUP BY customer_id;

DISTINCT in JOINs

Anti-Pattern:

-- ❌ Bad: DISTINCT to fix JOIN duplication
SELECT DISTINCT users.id, users.name
FROM users
INNER JOIN orders ON users.id = orders.user_id;

Why It's Wrong:

• DISTINCT is band-aid for incorrect JOIN
• Expensive deduplication

Solution:

-- ✅ Good: Use EXISTS or LEFT JOIN properly
SELECT id, name FROM users
WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id);

-- Or if you need aggregation:
SELECT users.id, users.name, COUNT(orders.id) AS order_count
FROM users
INNER JOIN orders ON users.id = orders.user_id
GROUP BY users.id, users.name;

OR vs IN Performance

Problem Description

Multiple OR conditions can be slower than IN clause or UNION ALL.

Anti-Pattern:

-- ❌ Suboptimal: Multiple OR conditions
SELECT * FROM users
WHERE status = 'active'
   OR status = 'pending'
   OR status = 'verified'
   OR status = 'trial';

Why It's Suboptimal:

• May not use index efficiently
• Query planner may choose sequential scan

Solution 1: Use IN

-- ✅ Better: IN clause
SELECT * FROM users
WHERE status IN ('active', 'pending', 'verified', 'trial');

Solution 2: Use UNION ALL (if separate indexes exist)

-- ✅ Alternative: UNION ALL with separate index scans
SELECT * FROM users WHERE status = 'active'
UNION ALL
SELECT * FROM users WHERE status = 'pending'
UNION ALL
SELECT * FROM users WHERE status = 'verified'
UNION ALL
SELECT * FROM users WHERE status = 'trial';

OR on Different Columns

Anti-Pattern:

-- ❌ Bad: OR on different columns
SELECT * FROM users WHERE email = 'x@y.com' OR phone = '555-1234';

Why It Fails:

• Cannot use indexes effectively
• Often results in sequential scan

Solution: UNION ALL

-- ✅ Good: UNION ALL allows index usage on both columns
SELECT * FROM users WHERE email = 'x@y.com'
UNION ALL
SELECT * FROM users WHERE phone = '555-1234';

NOT IN with NULL Values

Problem Description

NOT IN with NULL values returns unexpected results (empty set).

Anti-Pattern:

-- ❌ Bad: NOT IN with potential NULLs
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM deleted_users);
-- Returns ZERO rows if any user_id is NULL!

Why It Fails:

• SQL three-valued logic (TRUE, FALSE, NULL)
• NULL in list makes entire NOT IN return NULL
• NULL is not TRUE, so row is excluded

Solution 1: NOT EXISTS

-- ✅ Good: NOT EXISTS handles NULLs correctly
SELECT * FROM users
WHERE NOT EXISTS (
  SELECT 1 FROM deleted_users WHERE deleted_users.user_id = users.id
);

Solution 2: Filter NULLs in Subquery

-- ✅ Also good: Explicitly exclude NULLs
SELECT * FROM users
WHERE id NOT IN (
  SELECT user_id FROM deleted_users WHERE user_id IS NOT NULL
);

Performance Comparison

-- NOT IN: May be slower, fails with NULLs
-- NOT EXISTS: Usually faster, handles NULLs correctly

Wildcard at Start of LIKE

Problem Description

Leading wildcard in LIKE prevents index usage.

Anti-Pattern:

-- ❌ Bad: Leading wildcard
SELECT * FROM products WHERE name LIKE '%widget%';

Why It Fails:

• B-tree index cannot be used
• Must scan entire table
• Evaluate LIKE for every row

Solution 1: Trailing Wildcard (if applicable)

-- ✅ Good: Trailing wildcard can use index
SELECT * FROM products WHERE name LIKE 'super%';

Solution 2: Full-Text Search

-- PostgreSQL: Full-text search with GIN index
CREATE INDEX idx_products_name_fts ON products
  USING GIN (to_tsvector('english', name));

SELECT * FROM products
WHERE to_tsvector('english', name) @@ to_tsquery('english', 'widget');

-- MySQL: Full-text index
CREATE FULLTEXT INDEX idx_products_name_fulltext ON products (name);

SELECT * FROM products
WHERE MATCH(name) AGAINST('widget' IN NATURAL LANGUAGE MODE);

Solution 3: Trigram Index (PostgreSQL)

-- PostgreSQL: Trigram index for LIKE patterns
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);

-- Now LIKE '%widget%' can use index
SELECT * FROM products WHERE name LIKE '%widget%';

Quick Reference: Anti-Patterns Summary

Anti-Pattern Detection Queries

PostgreSQL: Find SELECT * Queries

-- Enable query logging
ALTER DATABASE yourdb SET log_statement = 'all';

-- Analyze logs for SELECT * patterns
-- (requires log analysis tool or grep on log files)

PostgreSQL: Find Missing Foreign Key Indexes

SELECT
  c.conrelid::regclass AS table_name,
  a.attname AS column_name,
  c.conname AS constraint_name
FROM pg_constraint c
JOIN pg_attribute a ON a.attnum = ANY(c.conkey) AND a.attrelid = c.conrelid
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid
      AND a.attnum = ANY(i.indkey)
  );

SQL Server: Find Implicit Conversions

-- Check execution plans for warnings
-- Look for "CONVERT_IMPLICIT" warnings in graphical plans

Composite Index Design

Guide to designing multi-column composite indexes for optimal query performance.

Column Order Rules

Rule 1: Equality Filters First (Most Selective)

Query Pattern:

SELECT * FROM orders
WHERE customer_id = 123 AND status = 'shipped';

Optimal Index:

CREATE INDEX idx_orders_customer_status
ON orders (customer_id, status);

Why: Most selective filters first reduce index tree traversal.

Rule 2: Range Filters After Equality

Query Pattern:

SELECT * FROM orders
WHERE customer_id = 123
  AND created_at > '2025-01-01';

Optimal Index:

CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at);

Why: Equality narrows down to specific branch, then range scan within.

Rule 3: ORDER BY Columns Last

Query Pattern:

SELECT * FROM orders
WHERE customer_id = 123
ORDER BY created_at DESC
LIMIT 10;

Optimal Index:

CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at DESC);

Why: Pre-sorted results, no separate sort operation needed.

Left-Prefix Rule

Composite Index: (A, B, C)

Can Be Used For:

• WHERE A = ?
• WHERE A = ? AND B = ?
• WHERE A = ? AND B = ? AND C = ?
• WHERE A = ? ORDER BY B

Cannot Be Used For:

• WHERE B = ? (skips leading column A)
• WHERE C = ? (skips leading columns A, B)
• WHERE B = ? AND C = ? (skips leading column A)

Example:

-- Index: (customer_id, status, created_at)

-- ✅ Uses index (customer_id)
SELECT * FROM orders WHERE customer_id = 123;

-- ✅ Uses index (customer_id, status)
SELECT * FROM orders WHERE customer_id = 123 AND status = 'pending';

-- ✅ Uses full index
SELECT * FROM orders
WHERE customer_id = 123 AND status = 'pending' AND created_at > '2025-01-01';

-- ❌ Cannot use index (skips customer_id)
SELECT * FROM orders WHERE status = 'pending';

Common Patterns

Pattern 1: Multi-Tenant Application

Query:

SELECT * FROM documents
WHERE tenant_id = 123 AND status = 'active'
ORDER BY updated_at DESC;

Index:

CREATE INDEX idx_documents_tenant_status_updated
ON documents (tenant_id, status, updated_at DESC);

Why: tenant_id first (always filtered), status second, updated_at for sorting.

Pattern 2: Status + Time Range

Query:

SELECT * FROM tasks
WHERE status = 'pending' AND due_date < NOW();

Index:

CREATE INDEX idx_tasks_status_due
ON tasks (status, due_date);

Pattern 3: Multiple Equality + Range

Query:

SELECT * FROM products
WHERE category_id = 5
  AND brand_id = 10
  AND price > 100;

Index:

CREATE INDEX idx_products_category_brand_price
ON products (category_id, brand_id, price);

Column Order: Most selective first, range last.

Pattern 4: JOIN + Filter

Query:

SELECT * FROM order_items
INNER JOIN orders ON order_items.order_id = orders.id
WHERE orders.customer_id = 123;

Indexes:

-- Index on order_items for JOIN
CREATE INDEX idx_order_items_order ON order_items (order_id);

-- Composite index on orders
CREATE INDEX idx_orders_customer ON orders (customer_id);

Selectivity Ordering

High Selectivity → Low Selectivity

Table: 1,000,000 orders

• customer_id: 10,000 unique values (high selectivity)
• status: 5 unique values (low selectivity)

Query:

SELECT * FROM orders WHERE customer_id = 123 AND status = 'pending';

Optimal:

CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);

Why:

• customer_id = 123 narrows to ~100 rows
• status = 'pending' filters within those 100 rows
• Much better than filtering 200,000 pending orders for customer_id

Suboptimal:

CREATE INDEX idx_orders_status_customer ON orders (status, customer_id);

• status = 'pending' starts with 200,000 rows
• Then filters for customer_id
• Larger initial scan

Index vs Query Mismatch

Mismatch Example

Index:

CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);

Query:

-- ❌ Query uses status first (skips customer_id)
SELECT * FROM orders WHERE status = 'pending';

Solution: Create separate index for status-only queries:

CREATE INDEX idx_orders_status ON orders (status);

Partial Index Solution (PostgreSQL)

Instead of full index on low-selectivity column:

-- ✅ Partial index for specific status
CREATE INDEX idx_orders_pending
ON orders (created_at)
WHERE status = 'pending';

Including Non-Indexed Columns

PostgreSQL INCLUDE Clause

Query:

SELECT customer_id, status, total, created_at
FROM orders
WHERE customer_id = 123;

Covering Index:

CREATE INDEX idx_orders_customer_covering
ON orders (customer_id)
INCLUDE (status, total, created_at);

Benefit: Index-Only Scan (no heap access).

MySQL Approach

Composite Index with Extra Columns:

CREATE INDEX idx_orders_customer_covering
ON orders (customer_id, status, total, created_at);

Trade-off: Larger index, but enables covering scans.

SQL Server INCLUDE Clause

Covering Index:

CREATE NONCLUSTERED INDEX IX_Orders_CustomerID
ON Orders (CustomerID)
INCLUDE (Status, Total, CreatedAt);

Multi-Column vs Multiple Indexes

Single Composite Index

Index:

CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);

Queries Supported:

• WHERE customer_id = ?
• WHERE customer_id = ? AND status = ?

Queries NOT Supported:

• WHERE status = ? (skips leading column)

Multiple Single-Column Indexes

Indexes:

CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE INDEX idx_orders_status ON orders (status);

Queries Supported:

• WHERE customer_id = ? (uses idx_orders_customer)
• WHERE status = ? (uses idx_orders_status)
• WHERE customer_id = ? AND status = ? (bitmap scan in PostgreSQL, index merge in MySQL)

Trade-off:

• More indexes = slower writes
• More flexible for varied query patterns

Recommendation

High-frequency query patterns: Composite index Varied query patterns: Multiple indexes or partial indexes

Testing Composite Indexes

Before Creating Index

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 123 AND status = 'pending'
ORDER BY created_at DESC;

Look for:

• Sequential Scan / Table Scan
• High row counts
• Sort operation

After Creating Index

CREATE INDEX idx_orders_customer_status_created
ON orders (customer_id, status, created_at DESC);

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 123 AND status = 'pending'
ORDER BY created_at DESC;

Look for:

• Index Scan / Index Seek
• Low row counts
• No sort operation (pre-sorted by index)

Quick Reference

Column Order Priority

1. Equality filters (most selective first) 2. Additional equality filters (by selectivity) 3. Range filters (after all equality) 4. ORDER BY columns (matching sort direction) 5. GROUP BY columns (if not already covered)

Common Mistakes

❌ Wrong order:

CREATE INDEX ON orders (created_at, customer_id);
-- Query: WHERE customer_id = ? ORDER BY created_at
-- Inefficient: Large scan on created_at first

✅ Correct order:

CREATE INDEX ON orders (customer_id, created_at);
-- Query: WHERE customer_id = ? ORDER BY created_at
-- Efficient: Narrow by customer_id, then sorted by created_at

Index Size Considerations

Each additional column:

• Increases index size by column width
• Slows down write operations slightly
• Enables more query patterns

Balance:

• 2-4 columns typical
• 5+ columns rare (diminishing returns)

Efficient Query Patterns

Collection of proven efficient SQL patterns for optimal query performance.

Table of Contents

1. Existence Checks 2. Pagination 3. Aggregation Patterns 4. Union Operations 5. Window Functions 6. Batch Operations

Existence Checks

Pattern: EXISTS vs COUNT

Use Case: Check if related records exist.

Inefficient:

-- ❌ Bad: Counts all matching rows
SELECT * FROM users
WHERE (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) > 0;

Efficient:

-- ✅ Good: Stops at first match
SELECT * FROM users
WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id);

Why EXISTS is Better:

• Stops execution at first matching row
• No need to count all rows
• Better performance for large result sets

Performance Comparison:

COUNT(*) with 1000 matches: ~100ms
EXISTS with 1000 matches: ~5ms (stops at first match)

Pattern: EXISTS vs IN

Use Case: Filter by values in subquery.

Less Efficient:

-- ❌ Suboptimal: Builds full list
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

More Efficient:

-- ✅ Better: Can stop early with semi-join
SELECT * FROM users
WHERE EXISTS (
  SELECT 1 FROM orders WHERE orders.user_id = users.id AND orders.total > 1000
);

When to Use Each:

• EXISTS: When checking existence or correlated conditions
• IN: When list is small and static (e.g., IN (1, 2, 3))

Pagination

Pattern: Efficient LIMIT/OFFSET

Inefficient for Deep Pagination:

-- ❌ Bad: Offset skips rows but database still processes them
SELECT * FROM posts
ORDER BY created_at DESC
LIMIT 20 OFFSET 10000;  -- Skip 10,000 rows

Why It's Slow:

• Database processes all 10,020 rows
• Sorts 10,020 rows
• Then discards first 10,000
• Performance degrades linearly with offset

Efficient Keyset Pagination:

-- ✅ Good: Use last seen value as cursor
SELECT * FROM posts
WHERE created_at < '2025-01-15 10:30:00'  -- Last seen timestamp
ORDER BY created_at DESC
LIMIT 20;

Why It's Fast:

• Index scan starts at cursor position
• No offset processing
• Constant performance regardless of page depth

Implementation Pattern:

-- Page 1
SELECT id, title, created_at FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Page 2 (last_created_at = '2025-01-15 10:30:00', last_id = 12345)
SELECT id, title, created_at FROM posts
WHERE (created_at < '2025-01-15 10:30:00')
   OR (created_at = '2025-01-15 10:30:00' AND id < 12345)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Required Index:

CREATE INDEX idx_posts_created_id ON posts (created_at DESC, id DESC);

Pattern: Cursor-Based Pagination

API Response Format:

{
  "data": [...],
  "cursor": {
    "next": "eyJjcmVhdGVkX2F0IjoiMjAyNS0wMS0xNSIsImlkIjoxMjM0NX0=",
    "has_more": true
  }
}

Cursor Encoding:

import base64
import json

# Encode cursor
cursor_data = {"created_at": "2025-01-15 10:30:00", "id": 12345}
cursor = base64.b64encode(json.dumps(cursor_data).encode()).decode()

# Decode cursor
cursor_data = json.loads(base64.b64decode(cursor).decode())

Aggregation Patterns

Pattern: Conditional Aggregation

Use Case: Multiple aggregations with different conditions.

Inefficient (Multiple Subqueries):

-- ❌ Bad: Multiple scans of orders table
SELECT
  c.id,
  c.name,
  (SELECT COUNT(*) FROM orders WHERE customer_id = c.id) AS total_orders,
  (SELECT COUNT(*) FROM orders WHERE customer_id = c.id AND status = 'completed') AS completed_orders,
  (SELECT SUM(total) FROM orders WHERE customer_id = c.id AND status = 'completed') AS revenue
FROM customers c;

Efficient (Single Query with CASE):

-- ✅ Good: Single scan with conditional aggregation
SELECT
  c.id,
  c.name,
  COUNT(o.id) AS total_orders,
  COUNT(CASE WHEN o.status = 'completed' THEN 1 END) AS completed_orders,
  SUM(CASE WHEN o.status = 'completed' THEN o.total ELSE 0 END) AS revenue
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;

Performance Comparison:

Multiple subqueries: 3 table scans, ~300ms
Conditional aggregation: 1 table scan, ~100ms

Pattern: Filtered Aggregation (PostgreSQL)

PostgreSQL FILTER Clause:

-- ✅ PostgreSQL-specific: FILTER clause (more readable)
SELECT
  c.id,
  c.name,
  COUNT(o.id) AS total_orders,
  COUNT(o.id) FILTER (WHERE o.status = 'completed') AS completed_orders,
  SUM(o.total) FILTER (WHERE o.status = 'completed') AS revenue
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;

Union Operations

Pattern: UNION ALL vs UNION

Inefficient:

-- ❌ Bad: UNION removes duplicates (expensive)
SELECT id, name FROM active_users
UNION
SELECT id, name FROM trial_users;

Why UNION is Expensive:

• Sorts both result sets
• Performs deduplication
• Additional memory and CPU overhead

Efficient:

-- ✅ Good: UNION ALL (no deduplication)
SELECT id, name FROM active_users
UNION ALL
SELECT id, name FROM trial_users;

When to Use Each:

• UNION ALL: When duplicates acceptable or datasets don't overlap (99% of cases)
• UNION: Only when duplicates must be removed and datasets may overlap

Performance Comparison:

UNION with 100k rows: ~500ms (sorting + dedup)
UNION ALL with 100k rows: ~50ms (no overhead)

Pattern: Efficient Set Operations

Use Case: Combine results from partitioned tables.

-- ✅ Good: UNION ALL for partitioned data
SELECT * FROM orders_2024 WHERE status = 'pending'
UNION ALL
SELECT * FROM orders_2025 WHERE status = 'pending';

Window Functions

Pattern: Row Numbering for Deduplication

Use Case: Get first/last record per group.

Inefficient (Correlated Subquery):

-- ❌ Bad: Correlated subquery
SELECT *
FROM products p
WHERE p.created_at = (
  SELECT MAX(created_at)
  FROM products
  WHERE category_id = p.category_id
);

Efficient (Window Function):

-- ✅ Good: Window function with CTE
WITH ranked_products AS (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY created_at DESC) AS rn
  FROM products
)
SELECT * FROM ranked_products WHERE rn = 1;

Performance Comparison:

Correlated subquery: ~2000ms (N subqueries)
Window function: ~200ms (single scan)

Pattern: Running Totals

Use Case: Calculate cumulative sum.

Inefficient (Correlated Subquery):

-- ❌ Bad: Subquery per row
SELECT
  order_date,
  total,
  (SELECT SUM(total)
   FROM orders o2
   WHERE o2.order_date <= orders.order_date) AS running_total
FROM orders;

Efficient (Window Function):

-- ✅ Good: Window function
SELECT
  order_date,
  total,
  SUM(total) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM orders;

Pattern: Rank Within Group

Use Case: Find top N items per category.

-- ✅ Efficient: Top 3 products per category by sales
WITH ranked_products AS (
  SELECT
    category_id,
    product_name,
    sales,
    RANK() OVER (PARTITION BY category_id ORDER BY sales DESC) AS rank
  FROM products
)
SELECT * FROM ranked_products WHERE rank <= 3;

Batch Operations

Pattern: Bulk INSERT

Inefficient:

-- ❌ Bad: Individual INSERTs (1000 round trips)
INSERT INTO users (name, email) VALUES ('User1', 'user1@example.com');
INSERT INTO users (name, email) VALUES ('User2', 'user2@example.com');
-- ... 998 more

Efficient:

-- ✅ Good: Bulk INSERT (1 round trip)
INSERT INTO users (name, email) VALUES
  ('User1', 'user1@example.com'),
  ('User2', 'user2@example.com'),
  ('User3', 'user3@example.com'),
  -- ... up to 1000 rows
  ('User1000', 'user1000@example.com');

Performance Comparison:

Individual INSERTs: ~5000ms
Bulk INSERT: ~50ms

Batch Size Recommendations:

• PostgreSQL: 1000-5000 rows per INSERT
• MySQL: 1000 rows per INSERT (max_allowed_packet limit)
• SQL Server: 1000 rows per INSERT

Pattern: Bulk UPDATE with CASE

Use Case: Update multiple rows with different values.

Inefficient:

-- ❌ Bad: Multiple UPDATE statements
UPDATE products SET price = 19.99 WHERE id = 1;
UPDATE products SET price = 29.99 WHERE id = 2;
UPDATE products SET price = 39.99 WHERE id = 3;

Efficient:

-- ✅ Good: Single UPDATE with CASE
UPDATE products
SET price = CASE id
  WHEN 1 THEN 19.99
  WHEN 2 THEN 29.99
  WHEN 3 THEN 39.99
END
WHERE id IN (1, 2, 3);

Pattern: Upsert (INSERT or UPDATE)

PostgreSQL (ON CONFLICT):

-- ✅ Efficient upsert
INSERT INTO user_stats (user_id, login_count, last_login)
VALUES (123, 1, NOW())
ON CONFLICT (user_id)
DO UPDATE SET
  login_count = user_stats.login_count + 1,
  last_login = NOW();

MySQL (ON DUPLICATE KEY UPDATE):

-- ✅ Efficient upsert
INSERT INTO user_stats (user_id, login_count, last_login)
VALUES (123, 1, NOW())
ON DUPLICATE KEY UPDATE
  login_count = login_count + 1,
  last_login = NOW();

SQL Server (MERGE):

-- ✅ Efficient upsert
MERGE INTO user_stats AS target
USING (SELECT 123 AS user_id, 1 AS login_count, GETDATE() AS last_login) AS source
ON target.user_id = source.user_id
WHEN MATCHED THEN
  UPDATE SET login_count = target.login_count + 1, last_login = GETDATE()
WHEN NOT MATCHED THEN
  INSERT (user_id, login_count, last_login)
  VALUES (source.user_id, source.login_count, source.last_login);

Common Table Expressions (CTEs)

Pattern: Readable Complex Queries

Inefficient (Nested Subqueries):

-- ❌ Hard to read and maintain
SELECT *
FROM (
  SELECT *
  FROM (
    SELECT user_id, SUM(total) as revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY user_id
  ) AS user_revenue
  WHERE revenue > 1000
) AS high_value_users
INNER JOIN users ON users.id = high_value_users.user_id;

Efficient (CTEs):

-- ✅ Readable and maintainable
WITH user_revenue AS (
  SELECT user_id, SUM(total) as revenue
  FROM orders
  WHERE status = 'completed'
  GROUP BY user_id
),
high_value_users AS (
  SELECT * FROM user_revenue WHERE revenue > 1000
)
SELECT
  users.*,
  high_value_users.revenue
FROM high_value_users
INNER JOIN users ON users.id = high_value_users.user_id;

Benefits:

• Better readability
• Easier debugging (can SELECT from CTEs individually)
• Query optimizer can optimize entire query

Pattern: Recursive CTEs

Use Case: Hierarchical data (org charts, nested categories).

-- ✅ Recursive CTE for org chart
WITH RECURSIVE org_chart AS (
  -- Base case: top-level managers
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive case: employees reporting to previous level
  SELECT e.id, e.name, e.manager_id, oc.level + 1
  FROM employees e
  INNER JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level, name;

Index-Friendly Patterns

Pattern: Prefix Matching

Efficient:

-- ✅ Can use B-tree index
SELECT * FROM users WHERE email LIKE 'john%';

Index:

CREATE INDEX idx_users_email ON users (email);

Pattern: Range Queries

Efficient:

-- ✅ Can use B-tree index
SELECT * FROM orders
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';

Index:

CREATE INDEX idx_orders_created ON orders (created_at);

Pattern: Composite Filters

Efficient:

-- ✅ Uses composite index efficiently
SELECT * FROM orders
WHERE customer_id = 123 AND status = 'pending'
ORDER BY created_at DESC
LIMIT 10;

Optimal Index:

CREATE INDEX idx_orders_customer_status_created
ON orders (customer_id, status, created_at DESC);

Quick Reference

Existence Checks

• Use EXISTS instead of COUNT(*) > 0
• Use NOT EXISTS instead of NOT IN (handles NULLs)

Pagination

• Use keyset/cursor pagination instead of OFFSET for deep pagination
• Index columns in ORDER BY and WHERE clauses

Aggregation

• Use conditional aggregation (CASE in aggregate) instead of multiple subqueries
• PostgreSQL: Use FILTER clause for readability

Unions

• Use UNION ALL by default (no deduplication overhead)
• Only use UNION when duplicates must be removed

Window Functions

• Use window functions instead of correlated subqueries for ranking/running totals
• More efficient and more readable

Batch Operations

• Bulk INSERT/UPDATE instead of row-by-row operations
• Use upsert operations (ON CONFLICT, ON DUPLICATE KEY, MERGE)

CTEs

• Use CTEs for complex queries (better readability)
• PostgreSQL 12+: CTEs are inline-optimized by default

EXPLAIN Analysis Guide

Comprehensive guide to interpreting execution plans across PostgreSQL, MySQL, and SQL Server.

Table of Contents

1. PostgreSQL EXPLAIN 2. MySQL EXPLAIN 3. SQL Server Execution Plans 4. Key Metrics to Monitor 5. Common Patterns and Solutions

PostgreSQL EXPLAIN

Basic EXPLAIN Syntax

EXPLAIN - Show query plan without execution:

EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

EXPLAIN ANALYZE - Execute query and show actual timing:

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';

EXPLAIN (ANALYZE, BUFFERS) - Include buffer usage:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE email = 'user@example.com';

Reading PostgreSQL Output

Example Output:

Seq Scan on users  (cost=0.00..1500.00 rows=1 width=100) (actual time=50.123..50.124 rows=1 loops=1)
  Filter: (email = 'user@example.com'::text)
  Rows Removed by Filter: 99999
Planning Time: 0.100 ms
Execution Time: 50.150 ms

Key Components:

• Operation: Seq Scan (scan type)
• Table: users (table being scanned)
• cost=0.00..1500.00: Estimated cost range (startup..total)
• rows=1: Estimated rows returned
• width=100: Average row size in bytes
• actual time=50.123..50.124: Actual time range (milliseconds)
• rows=1: Actual rows returned
• loops=1: Number of times operation executed

Cost Interpretation:

• Cost is arbitrary units (not milliseconds)
• Compare relative costs between plans
• Lower cost = better (usually)

PostgreSQL Scan Types

Sequential Scan (Seq Scan):

• Reads entire table from disk
• No index used
• Acceptable for small tables or full table queries
• Red flag for large tables with WHERE clause

Index Scan:

• Direct index traversal
• Excellent for small result sets
• Accesses heap table to retrieve full rows

Index-Only Scan:

• All data retrieved from index (no heap access)
• Best performance
• Requires covering index with all needed columns

Bitmap Heap Scan:

• Two-step process: identify rows in index → fetch from heap
• Efficient for medium result sets
• Combines multiple index scans

Nested Loop Join:

• Iterate outer table, lookup inner table per row
• Good when outer table is small
• Requires index on inner table join column

Hash Join:

• Build hash table of inner table, probe with outer
• Good for large tables
• Memory-intensive

Merge Join:

• Sort both tables, merge sorted results
• Good for pre-sorted data
• Expensive if sorting required

PostgreSQL Optimization Examples

Example 1: Sequential Scan → Index Scan

Before:

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';

Seq Scan on users  (cost=0.00..1500.00 rows=1) (actual time=50.123..50.124 rows=1)
  Filter: (email = 'user@example.com')
  Rows Removed by Filter: 99999

Optimization:

CREATE INDEX idx_users_email ON users (email);

After:

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';

Index Scan using idx_users_email on users  (cost=0.42..8.44 rows=1) (actual time=0.025..0.026 rows=1)
  Index Cond: (email = 'user@example.com')

Result: 1000x faster (50ms → 0.05ms)

MySQL EXPLAIN

Basic EXPLAIN Syntax

Standard EXPLAIN:

EXPLAIN SELECT * FROM products WHERE category_id = 5 AND price > 100;

JSON Format (detailed output):

EXPLAIN FORMAT=JSON SELECT * FROM products WHERE category_id = 5;

Reading MySQL Output

Example Output:

+----+-------------+----------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | products | ALL  | NULL          | NULL | NULL    | NULL | 50000 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+-------+-------------+

Key Columns:

• id: Query identifier
• select_type: SIMPLE, PRIMARY, SUBQUERY, DERIVED, UNION
• table: Table being accessed
• type: Access type (performance indicator)
• possible_keys: Indexes MySQL could use
• key: Index actually used (NULL = no index)
• key_len: Bytes of index used
• ref: Columns/constants compared to index
• rows: Estimated rows examined
• Extra: Additional information

MySQL Access Types (type column)

Best to Worst Performance:

1. system - Single row table (best) 2. const - Primary key or unique index lookup 3. eq_ref - One row from table for each previous row combination 4. ref - Non-unique index lookup 5. range - Index range scan (BETWEEN, >, <, IN) 6. index - Full index scan 7. ALL - Full table scan (worst)

Target: Achieve const, eq_ref, ref, or range types.

Red Flag: ALL (full table scan) on large tables.

MySQL Extra Column Meanings

• Using index: Index-only scan (excellent)
• Using where: Filtering rows after retrieval
• Using temporary: Temporary table created (expensive)
• Using filesort: Sorting required (expensive for large result sets)
• Using join buffer: Join buffer used (index missing on join column)

MySQL Optimization Examples

Example 1: ALL → range

Before:

EXPLAIN SELECT * FROM products WHERE category_id = 5 AND price > 100;

type: ALL, possible_keys: NULL, key: NULL, rows: 50000

Optimization:

CREATE INDEX idx_products_category_price ON products (category_id, price);

After:

EXPLAIN SELECT * FROM products WHERE category_id = 5 AND price > 100;

type: range, key: idx_products_category_price, rows: 150

Result: Rows examined reduced from 50,000 to 150.

SQL Server Execution Plans

Accessing Execution Plans

Estimated Execution Plan (Ctrl+L in SSMS):

-- Right-click query → Display Estimated Execution Plan
SELECT * FROM Sales.Orders WHERE CustomerID = 123;

Actual Execution Plan (Ctrl+M, then execute):

-- Query → Include Actual Execution Plan → Execute
SELECT * FROM Sales.Orders WHERE CustomerID = 123;

Reading SQL Server Execution Plans

Graphical Execution Plan Components:

• Operations: Boxes representing operations (Scan, Seek, Join)
• Arrows: Data flow direction (right to left)
• Thickness: Relative row count (thick = many rows)
• Warnings: Yellow exclamation marks (missing indexes, implicit conversions)
• Cost %: Percentage of total query cost

Read Direction: Right to left, top to bottom.

SQL Server Scan Types

Table Scan:

• Reads entire table
• No index available
• Red flag for large tables

Clustered Index Scan:

• Reads entire clustered index (full table)
• Similar to table scan

Index Seek:

• Direct index lookup
• Excellent performance
• Target for WHERE, JOIN conditions

Index Scan:

• Reads entire index
• Better than table scan if index is smaller
• Still inefficient for large indexes

Key Lookup:

• Additional lookup to retrieve non-indexed columns
• Indicates covering index opportunity

SQL Server Optimization Examples

Example 1: Identify Missing Index

Execution Plan Warning:

Missing Index (Impact 95%)
CREATE NONCLUSTERED INDEX [<Name of Missing Index>]
ON [dbo].[Orders] ([CustomerID])

Action:

CREATE NONCLUSTERED INDEX IX_Orders_CustomerID
ON dbo.Orders (CustomerID);

Example 2: Query Store Analysis

Find top 10 expensive queries:

SELECT TOP 10
    q.query_id,
    qt.query_sql_text,
    rs.avg_duration / 1000.0 AS avg_duration_ms,
    rs.avg_logical_io_reads,
    rs.count_executions
FROM sys.query_store_query q
INNER JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_plan p ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
ORDER BY rs.avg_duration DESC;

Key Metrics to Monitor

Cross-Database Metrics

PostgreSQL-Specific Metrics

• Buffer hits vs reads: High hit ratio indicates good cache usage
• Planning time: Should be <1ms typically
• Execution time: Target <100ms for user-facing queries

MySQL-Specific Metrics

• Handler_read_rnd_next: High value indicates full table scans
• Created_tmp_tables: Temporary table creation count
• Sort_scan: Number of sorts requiring table scan

SQL Server-Specific Metrics

• Logical reads: Pages read from cache
• Physical reads: Pages read from disk (minimize)
• CPU time: CPU milliseconds consumed

Common Patterns and Solutions

Pattern 1: High Row Count with Low Results

Symptom:

Seq Scan on table  (cost=0.00..10000.00 rows=100000)
  Filter: (column = value)
  Rows Removed by Filter: 99999

Solution: Add index on filter column

CREATE INDEX idx_table_column ON table (column);

Pattern 2: Nested Loop with Large Outer Table

Symptom:

Nested Loop  (cost=0.00..50000.00 rows=10000)
  -> Seq Scan on large_table (rows=10000)
  -> Index Scan on small_table

Solutions: 1. Add index on large_table filter columns (reduce outer rows) 2. Reorder joins (start with smaller result set) 3. Force hash join if appropriate (PostgreSQL: SET enable_nestloop = off)

Pattern 3: Sort Operation on Large Result Set

Symptom:

Sort  (cost=5000.00..6000.00 rows=100000)
  Sort Key: created_at DESC
  -> Seq Scan on orders

Solution: Create index matching ORDER BY

CREATE INDEX idx_orders_created ON orders (created_at DESC);

Pattern 4: Multiple OR Conditions

Symptom:

SELECT * FROM users WHERE status = 'active' OR status = 'pending' OR status = 'verified';

Seq Scan on users
  Filter: ((status = 'active') OR (status = 'pending') OR (status = 'verified'))

Solution: Use IN or UNION ALL

-- Better: Use IN
SELECT * FROM users WHERE status IN ('active', 'pending', 'verified');

-- Or: Use UNION ALL with separate indexes
SELECT * FROM users WHERE status = 'active'
UNION ALL
SELECT * FROM users WHERE status = 'pending'
UNION ALL
SELECT * FROM users WHERE status = 'verified';

Quick Reference: EXPLAIN Command Comparison

Best Practices

1. Always run EXPLAIN before optimizing - Understand the problem before solving it 2. Compare before/after plans - Verify optimizations work 3. Use ANALYZE variant when possible - Actual timing beats estimates 4. Check row estimates vs actuals - Large discrepancies indicate outdated statistics 5. Update statistics regularly - Run ANALYZE/UPDATE STATISTICS weekly 6. Monitor production queries - Enable slow query log or Query Store 7. Archive execution plans - Track performance changes over time

Index Types by Database

Quick reference guide to index types across PostgreSQL, MySQL, and SQL Server.

PostgreSQL Index Types

Recommendations:

• Default: B-tree (99% of use cases)
• Full-text: GIN on tsvector
• JSONB: GIN
• Spatial: GiST
• Time-series >100GB: BRIN

MySQL Index Types

Recommendations:

• Default: B-tree
• Text search: Full-text index
• Spatial: Spatial index

SQL Server Index Types

Recommendations:

• Primary key: Clustered index (default)
• Foreign keys: Non-clustered index
• Frequent queries: Covering index
• Analytics: Columnstore index

Cross-Database Index Comparison

General-Purpose Indexes

Full-Text Search

Partial/Filtered Indexes

Expression/Computed Indexes

Covering Indexes

Index Selection Decision Tree

What type of query?
├─ Equality (column = value)
│  ├─ PostgreSQL → B-tree or Hash
│  ├─ MySQL → B-tree
│  └─ SQL Server → Non-clustered
│
├─ Range (column > value, BETWEEN)
│  ├─ PostgreSQL → B-tree
│  ├─ MySQL → B-tree
│  └─ SQL Server → Non-clustered
│
├─ Full-text search
│  ├─ PostgreSQL → GIN (tsvector)
│  ├─ MySQL → Full-text
│  └─ SQL Server → Full-text
│
├─ JSON queries
│  ├─ PostgreSQL → GIN (JSONB)
│  ├─ MySQL → Generated column + B-tree
│  └─ SQL Server → JSON index (2016+)
│
├─ Spatial queries
│  ├─ PostgreSQL → GiST (PostGIS)
│  ├─ MySQL → Spatial
│  └─ SQL Server → Spatial
│
└─ Large time-series table
   ├─ PostgreSQL → BRIN
   ├─ MySQL → Partitioning + B-tree
   └─ SQL Server → Partitioning + Columnstore

Indexing Decisions Guide

Comprehensive framework for deciding when and how to add indexes to optimize SQL query performance.

Table of Contents

1. Index Decision Framework 2. Index Selection Criteria 3. Single-Column vs Composite Indexes 4. Covering Indexes 5. When NOT to Add Indexes 6. Index Maintenance

Index Decision Framework

Primary Decision Tree

Is column used in WHERE, JOIN, ORDER BY, or GROUP BY?
├─ YES → Is column selective (many unique values)?
│  ├─ YES → Is table frequently queried?
│  │  ├─ YES → Is table write-heavy?
│  │  │  ├─ YES → Balance read vs write performance
│  │  │  └─ NO → ADD INDEX ✅
│  │  └─ NO → Consider based on query frequency
│  └─ NO (low selectivity) → Skip index ❌
│     Exception: Partial index for specific subset
└─ NO → Skip index ❌

Selectivity Assessment

High Selectivity (Good for Indexes):

• Primary keys (100% unique)
• Email addresses (usually unique)
• UUIDs (unique)
• User IDs (unique per user)
• Timestamps (high variety)

Low Selectivity (Poor for Indexes):

• Boolean fields (true/false)
• Status fields with few values (active/inactive)
• Gender fields (limited values)
• Small enum fields (<10 values)

Selectivity Formula:

-- PostgreSQL
SELECT
  COUNT(DISTINCT column_name)::float / COUNT(*)::float AS selectivity
FROM table_name;

Guideline: Selectivity > 0.1 (10% unique) = candidate for index

Query Frequency Assessment

High Frequency (Prioritize Indexing):

• User-facing queries (every page load)
• API endpoints hit frequently
• Real-time dashboards
• Background jobs running every minute

Medium Frequency (Evaluate Trade-offs):

• Admin dashboards
• Reporting queries (daily/weekly)
• Batch processes (hourly)

Low Frequency (Deprioritize Indexing):

• Ad-hoc queries
• One-time data migrations
• Infrequent reports (monthly/quarterly)

Index Selection Criteria

Criteria 1: Column Usage Patterns

WHERE Clause:

SELECT * FROM orders WHERE customer_id = 123;

Decision: Index customer_id (equality filter)

JOIN Conditions:

SELECT * FROM orders
INNER JOIN customers ON orders.customer_id = customers.id;

Decision: Index orders.customer_id and customers.id (foreign key + primary key)

ORDER BY:

SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;

Decision: Index created_at DESC (sort optimization)

GROUP BY:

SELECT status, COUNT(*) FROM orders GROUP BY status;

Decision: Index status (grouping optimization)

Criteria 2: Table Size

Criteria 3: Write vs Read Ratio

Read-Heavy Tables (>90% reads):

• Aggressive indexing strategy
• Multiple indexes acceptable
• Covering indexes beneficial

Balanced Tables (50-90% reads):

• Moderate indexing
• Focus on most frequent queries
• Limit to 3-5 indexes per table

Write-Heavy Tables (>50% writes):

• Minimal indexing
• Only index critical queries
• Consider batching writes

Write Performance Impact:

Each additional index:
- INSERT: +5-10% overhead per index
- UPDATE: +5-10% overhead per affected index
- DELETE: +5-10% overhead per index

Criteria 4: Data Type Considerations

Good for Indexing:

• Integer types (INT, BIGINT)
• UUID/GUID
• Timestamps (DATE, TIMESTAMP)
• Short strings (VARCHAR(100))

Acceptable for Indexing:

• Medium strings (VARCHAR(255))
• DECIMAL/NUMERIC

Poor for Indexing:

• Large TEXT/BLOB columns
• Very long strings (>1000 chars)
• JSON (use specialized indexes: GIN for PostgreSQL, generated columns for MySQL)

Exception: Full-text indexes for TEXT columns

Single-Column vs Composite Indexes

When to Use Single-Column Index

Use Case 1: Single Filter

SELECT * FROM users WHERE email = 'user@example.com';

Index:

CREATE INDEX idx_users_email ON users (email);

Use Case 2: Simple JOIN

SELECT * FROM orders
INNER JOIN customers ON orders.customer_id = customers.id;

Index:

CREATE INDEX idx_orders_customer ON orders (customer_id);

When to Use Composite Index

Use Case 1: Multiple Equality Filters

SELECT * FROM orders
WHERE customer_id = 123 AND status = 'shipped';

Index:

CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);

Use Case 2: Filter + Sort

SELECT * FROM orders
WHERE customer_id = 123
ORDER BY created_at DESC;

Index:

CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at DESC);

Use Case 3: Filter + Group

SELECT customer_id, status, COUNT(*)
FROM orders
WHERE created_at > '2025-01-01'
GROUP BY customer_id, status;

Index:

CREATE INDEX idx_orders_created_customer_status
ON orders (created_at, customer_id, status);

Composite Index Column Order

Rule of Thumb: 1. Equality filters (most selective first) 2. Range filters (if any) 3. ORDER BY columns (matching sort direction)

Example:

SELECT * FROM orders
WHERE customer_id = 123        -- Equality (put first)
  AND status IN ('shipped', 'pending')  -- Equality (put second)
  AND total > 100              -- Range (put third)
ORDER BY created_at DESC;      -- Sort (put last)

Optimal Index:

CREATE INDEX idx_orders_customer_status_total_created
ON orders (customer_id, status, total, created_at DESC);

Left-Prefix Rule: Composite index on (A, B, C) can be used for:

• WHERE A = ?
• WHERE A = ? AND B = ?
• WHERE A = ? AND B = ? AND C = ?

But NOT for:

• WHERE B = ? (skips leading column)
• WHERE C = ? (skips leading columns)

Covering Indexes

What is a Covering Index?

Index that contains ALL columns needed by query (no heap table access required).

Benefits:

• Fastest possible performance (Index-Only Scan)
• Reduced I/O (no heap access)
• Better cache utilization

Creating Covering Indexes

PostgreSQL INCLUDE Clause:

CREATE INDEX idx_users_email_covering
ON users (email) INCLUDE (id, name, created_at);

MySQL Composite Index:

-- MySQL doesn't have INCLUDE, add columns to index
CREATE INDEX idx_users_email_id_name
ON users (email, id, name);

SQL Server INCLUDE Clause:

CREATE NONCLUSTERED INDEX IX_Users_Email_Covering
ON Users (Email)
INCLUDE (ID, Name, CreatedAt);

When to Use Covering Indexes

Use Case 1: Frequent Query with Specific Columns

-- Query runs 1000x per second
SELECT id, name FROM users WHERE email = 'user@example.com';

Covering Index:

CREATE INDEX idx_users_email_covering ON users (email) INCLUDE (id, name);

Use Case 2: API Endpoint Returning Specific Fields

-- API: GET /api/orders?customer_id=123 (returns id, total, status)
SELECT id, total, status FROM orders WHERE customer_id = 123;

Covering Index:

CREATE INDEX idx_orders_customer_covering
ON orders (customer_id) INCLUDE (id, total, status);

Covering Index Trade-offs

Benefits:

• Dramatic performance improvement (Index-Only Scan)
• No heap access = less I/O

Costs:

• Larger index size
• Slower writes (more data to update)
• More storage required

Guideline: Use covering indexes for critical queries (user-facing, high-frequency).

When NOT to Add Indexes

Anti-Pattern 1: Indexing Low-Selectivity Columns

Bad:

CREATE INDEX idx_users_is_active ON users (is_active);  -- ❌ Boolean

Why: Only 2 values (true/false), index scan often worse than seq scan.

Exception: Partial index for minority case

-- If 99% inactive, 1% active
CREATE INDEX idx_users_active ON users (id) WHERE is_active = true;

Anti-Pattern 2: Indexing Small Tables

Bad:

CREATE INDEX idx_config_key ON config (key);  -- ❌ Table has 50 rows

Why: Small tables fit in memory, seq scan faster than index overhead.

Guideline: Skip indexes for tables <1000 rows (unless foreign keys).

Anti-Pattern 3: Over-Indexing

Bad:

CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_users_email_name ON users (email, name);       -- ❌ Redundant
CREATE INDEX idx_users_email_name_id ON users (email, name, id); -- ❌ Redundant

Why: Multiple overlapping indexes waste space and slow writes.

Fix: Use single covering index

CREATE INDEX idx_users_email_covering ON users (email) INCLUDE (name, id);

Anti-Pattern 4: Indexing Write-Heavy Columns

Bad:

CREATE INDEX idx_pageviews_timestamp ON pageviews (timestamp);  -- ❌ High-insert table

Why: Each insert updates index, slowing down write-heavy operations.

Alternative: Partition table by time range, index within partitions.

Anti-Pattern 5: Indexing Calculated Columns (Without Expression Index)

Bad:

-- Query uses function
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
-- Regular index won't help
CREATE INDEX idx_users_email ON users (email);  -- ❌ Not used

Fix: Expression index (PostgreSQL, SQL Server)

CREATE INDEX idx_users_email_lower ON users (LOWER(email));

Index Maintenance

Monitoring Index Usage

PostgreSQL:

-- Find unused indexes
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexname NOT LIKE 'pg_toast_%'
ORDER BY idx_scan;

MySQL:

-- Enable index statistics
SELECT * FROM sys.schema_unused_indexes;

SQL Server:

-- Find unused indexes
SELECT
  OBJECT_NAME(i.object_id) AS TableName,
  i.name AS IndexName,
  s.user_seeks,
  s.user_scans,
  s.user_lookups,
  s.user_updates
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s
  ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE s.user_seeks = 0
  AND s.user_scans = 0
  AND s.user_lookups = 0
  AND i.type_desc = 'NONCLUSTERED';

Removing Unused Indexes

Process: 1. Identify unused indexes (0 scans over 7+ days) 2. Verify index not used for constraints 3. Drop index during low-traffic period 4. Monitor for performance regressions

Drop Command:

DROP INDEX idx_unused_index ON table_name;

Updating Index Statistics

PostgreSQL:

-- Analyze entire database
ANALYZE;

-- Analyze specific table
ANALYZE table_name;

MySQL:

-- Analyze table
ANALYZE TABLE table_name;

SQL Server:

-- Update statistics
UPDATE STATISTICS table_name;

-- Update statistics for specific index
UPDATE STATISTICS table_name index_name;

Schedule: Weekly for active tables, monthly for stable tables.

Rebuilding Fragmented Indexes

PostgreSQL:

-- Rebuild index (locks table)
REINDEX INDEX idx_name;

-- Rebuild concurrently (no lock)
REINDEX INDEX CONCURRENTLY idx_name;

MySQL:

-- Optimize table (rebuilds indexes)
OPTIMIZE TABLE table_name;

SQL Server:

-- Rebuild index
ALTER INDEX index_name ON table_name REBUILD;

-- Reorganize index (online, less intrusive)
ALTER INDEX index_name ON table_name REORGANIZE;

When to Rebuild:

• Fragmentation >30%
• After large batch deletes
• Query performance degrades over time

Index Design Patterns

Pattern 1: Foreign Key Indexing

Rule: Always index foreign keys.

Example:

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- Always add this index
CREATE INDEX idx_orders_customer ON orders (customer_id);

Why: Enables efficient joins and cascading deletes.

Pattern 2: Status + Timestamp Indexing

Use Case: Queries filtering by status and ordering by time.

SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 10;

Index:

CREATE INDEX idx_orders_status_created
ON orders (status, created_at DESC);

Pattern 3: Multi-Tenant Indexing

Use Case: SaaS application with tenant_id in most queries.

SELECT * FROM documents
WHERE tenant_id = 123 AND status = 'active'
ORDER BY updated_at DESC;

Index:

CREATE INDEX idx_documents_tenant_status_updated
ON documents (tenant_id, status, updated_at DESC);

Rule: Always lead with tenant_id in multi-tenant applications.

Pattern 4: Partial Index for Subset

Use Case: Query only active records (minority of data).

SELECT * FROM users WHERE status = 'active';

Full Index (Inefficient):

CREATE INDEX idx_users_status ON users (status);  -- Large index

Partial Index (Efficient - PostgreSQL):

CREATE INDEX idx_users_active ON users (id)
WHERE status = 'active';  -- Smaller, faster

Pattern 5: Expression Index for Case-Insensitive Search

Use Case: Case-insensitive email lookup.

SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

Expression Index (PostgreSQL, SQL Server):

CREATE INDEX idx_users_email_lower ON users (LOWER(email));

MySQL Alternative (Generated Column):

ALTER TABLE users ADD COLUMN email_lower VARCHAR(255)
  GENERATED ALWAYS AS (LOWER(email)) STORED;
CREATE INDEX idx_users_email_lower ON users (email_lower);

Quick Reference

Index Decision Checklist

• [ ] Column used in WHERE, JOIN, ORDER BY, or GROUP BY?
• [ ] High selectivity (>10% unique values)?
• [ ] Table size >1,000 rows?
• [ ] Query frequency high (user-facing or frequent)?
• [ ] Write-to-read ratio acceptable (<50% writes)?
• [ ] No overlapping composite indexes already exist?
• [ ] Index size acceptable (<20% of table size)?

Index Type Selection

Index Maintenance Schedule

MySQL-Specific Optimizations

MySQL-specific features, storage engines, and optimization techniques.

MySQL Storage Engines

InnoDB (Default)

Characteristics:

• ACID-compliant transactions
• Row-level locking
• Crash recovery
• Foreign key support
• Clustered primary key

Use Case: Default choice for most applications.

Creation:

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  total DECIMAL(10,2)
) ENGINE=InnoDB;

Clustered Index:

• Table data stored in primary key order
• Fast primary key lookups
• Choose small primary key (INT vs BIGINT vs UUID)

Recommendation: Use AUTO_INCREMENT INT or BIGINT for primary keys.

MyISAM

Characteristics:

• No transactions
• Table-level locking
• Faster reads (no MVCC overhead)
• No foreign keys
• No crash recovery

Use Case: Read-heavy tables with no writes (archives, logs).

Creation:

CREATE TABLE archive_logs (
  id INT PRIMARY KEY,
  message TEXT
) ENGINE=MyISAM;

Warning: Deprecated, avoid for new applications.

MySQL Index Types

B-tree Index (Default)

Use Case: General-purpose index.

Creation:

CREATE INDEX idx_users_email ON users (email);

Prefix Indexes for Long Strings:

-- Index first 10 characters
CREATE INDEX idx_articles_title ON articles (title(10));

Composite Indexes:

CREATE INDEX idx_orders_customer_status
ON orders (customer_id, status);

Full-Text Index

Use Case: Text search on VARCHAR/TEXT columns.

Creation:

-- Add full-text index
CREATE FULLTEXT INDEX idx_articles_content ON articles (content);

-- Or in table definition
CREATE TABLE articles (
  id INT PRIMARY KEY,
  title VARCHAR(255),
  content TEXT,
  FULLTEXT (title, content)
) ENGINE=InnoDB;

Query Syntax:

-- Natural language search
SELECT * FROM articles
WHERE MATCH(content) AGAINST('mysql optimization');

-- Boolean mode (AND/OR/NOT)
SELECT * FROM articles
WHERE MATCH(content) AGAINST('+mysql +optimization -postgres' IN BOOLEAN MODE);

-- Query expansion (finds related terms)
SELECT * FROM articles
WHERE MATCH(content) AGAINST('database' WITH QUERY EXPANSION);

Spatial Index

Use Case: Geometric data (points, polygons).

Creation:

CREATE TABLE locations (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  coordinates POINT NOT NULL,
  SPATIAL INDEX(coordinates)
) ENGINE=InnoDB;

Query:

-- Find locations within bounding box
SELECT name FROM locations
WHERE MBRContains(
  ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'),
  coordinates
);

MySQL EXPLAIN Analysis

EXPLAIN Output Format

Basic EXPLAIN:

EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

Output:

+----+-------------+--------+------+-------------------+------+---------+------+-------+-------------+
| id | select_type | table  | type | possible_keys     | key  | key_len | ref  | rows  | Extra       |
+----+-------------+--------+------+-------------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | orders | ref  | idx_orders_cust   | idx  | 4       | const| 150   | Using where |
+----+-------------+--------+------+-------------------+------+---------+------+-------+-------------+

EXPLAIN FORMAT=JSON

Detailed JSON Output:

EXPLAIN FORMAT=JSON
SELECT * FROM orders WHERE customer_id = 123;

Benefits:

• More detailed information
• Nested structure for complex queries
• Cost estimates
• Filtering statistics

Access Types (type column)

Performance Ranking (Best to Worst):

1. system - Single row table 2. const - Primary key/unique lookup with constant 3. eq_ref - One row per previous table row (unique index join) 4. ref - Non-unique index lookup 5. range - Index range scan (BETWEEN, >, <, IN) 6. index - Full index scan 7. ALL - Full table scan

Target: const, eq_ref, ref, or range

Extra Column Meanings

Good:

• Using index - Index-only scan (covering index)
• Using index condition - Index condition pushdown (ICP)

Acceptable:

• Using where - WHERE clause filtering after retrieval

Warning:

• Using temporary - Temporary table created
• Using filesort - Sorting required (not index-based)

Bad:

• Using join buffer - Join without index (add index!)

MySQL Index Hints

USE INDEX

Suggest Index:

SELECT * FROM orders USE INDEX (idx_orders_customer)
WHERE customer_id = 123 AND created_at > '2025-01-01';

When to Use: Optimizer chooses wrong index.

FORCE INDEX

Force Index Usage:

SELECT * FROM orders FORCE INDEX (idx_orders_customer)
WHERE customer_id = 123;

When to Use: Must use specific index (rare).

IGNORE INDEX

Prevent Index Usage:

SELECT * FROM orders IGNORE INDEX (idx_orders_status)
WHERE customer_id = 123 AND status = 'pending';

When to Use: Force full table scan or different index.

Optimizer Hints (MySQL 8.0+)

JOIN Order Hints:

SELECT /*+ JOIN_ORDER(orders, customers) */
  orders.*, customers.name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id;

Index Hints:

SELECT /*+ INDEX(orders idx_orders_customer) */
  * FROM orders WHERE customer_id = 123;

Subquery Hints:

SELECT /*+ SUBQUERY(MATERIALIZATION) */
  * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

MySQL-Specific Optimizations

Generated Columns (MySQL 5.7+)

Use Case: Index computed values.

Virtual Generated Column:

ALTER TABLE users
ADD COLUMN email_lower VARCHAR(255)
  GENERATED ALWAYS AS (LOWER(email)) VIRTUAL;

-- Index generated column
CREATE INDEX idx_users_email_lower ON users (email_lower);

Stored Generated Column:

ALTER TABLE users
ADD COLUMN full_name VARCHAR(510)
  GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) STORED;

CREATE INDEX idx_users_full_name ON users (full_name);

Difference:

• VIRTUAL: Computed on read (no storage overhead)
• STORED: Computed on write (faster reads, storage overhead)

JSON Indexes

Create Generated Column for JSON Path:

-- Extract JSON field
ALTER TABLE users
ADD COLUMN premium_status VARCHAR(10)
  GENERATED ALWAYS AS (metadata->>'$.premium') STORED;

-- Index generated column
CREATE INDEX idx_users_premium ON users (premium_status);

Query:

SELECT * FROM users WHERE premium_status = 'true';

Index Condition Pushdown (ICP)

MySQL 5.6+ Feature: Push WHERE conditions down to storage engine.

Without ICP:

-- Storage engine returns all rows matching first index column
-- MySQL server filters remaining conditions

With ICP:

-- Storage engine filters all index columns
-- Fewer rows returned to MySQL server

Check if Enabled:

SHOW VARIABLES LIKE 'optimizer_switch';
-- Look for index_condition_pushdown=on

EXPLAIN Indicator:

Extra: Using index condition

Multi-Range Read (MRR)

MySQL 5.6+ Feature: Optimize range scans by sorting row IDs before fetching.

Benefits:

• Sequential I/O instead of random I/O
• Fewer page fetches

Enable:

SET optimizer_switch='mrr=on,mrr_cost_based=off';

MySQL Configuration Tuning

Buffer Pool Size (InnoDB)

Recommendation: 70-80% of system RAM for dedicated database server.

-- Check current setting
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- Set in my.cnf / my.ini
[mysqld]
innodb_buffer_pool_size = 8G

Query Cache (Deprecated)

Warning: Query cache removed in MySQL 8.0.

MySQL 5.7 and Earlier:

-- Check query cache status
SHOW VARIABLES LIKE 'query_cache%';

-- Disable query cache (recommended for modern apps)
query_cache_type = 0
query_cache_size = 0

Replacement: Application-level caching (Redis, Memcached).

Join Buffer Size

Used for: Joins without indexes.

-- Check current setting
SHOW VARIABLES LIKE 'join_buffer_size';

-- Set per session
SET SESSION join_buffer_size = 8388608;  -- 8MB

Sort Buffer Size

Used for: ORDER BY, GROUP BY operations.

-- Check current setting
SHOW VARIABLES LIKE 'sort_buffer_size';

-- Set per session
SET SESSION sort_buffer_size = 2097152;  -- 2MB

MySQL Monitoring

Slow Query Log

Enable:

-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;  -- Log queries > 500ms
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';

Analyze Slow Queries:

# mysqldumpslow - Parse slow query log
mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log
# -s t: Sort by time
# -t 10: Top 10 queries

Performance Schema

Enable:

-- Check if enabled
SHOW VARIABLES LIKE 'performance_schema';

-- Enable in my.cnf
[mysqld]
performance_schema = ON

Query Statistics:

-- Top 10 slowest queries
SELECT
  DIGEST_TEXT,
  COUNT_STAR,
  AVG_TIMER_WAIT / 1000000000 AS avg_ms,
  SUM_TIMER_WAIT / 1000000000 AS total_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

Index Usage:

-- Tables without primary key
SELECT
  TABLE_SCHEMA,
  TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema')
  AND ENGINE = 'InnoDB'
  AND TABLE_TYPE = 'BASE TABLE'
  AND TABLE_CATALOG IS NULL
  AND NOT EXISTS (
    SELECT 1 FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = TABLES.TABLE_SCHEMA
      AND TABLE_NAME = TABLES.TABLE_NAME
      AND INDEX_NAME = 'PRIMARY'
  );

Table Statistics

Update Statistics:

-- Analyze table
ANALYZE TABLE orders;

-- Optimize table (rebuilds, updates stats)
OPTIMIZE TABLE orders;

View Statistics:

-- Table sizes
SELECT
  TABLE_SCHEMA,
  TABLE_NAME,
  ROUND(DATA_LENGTH / 1024 / 1024, 2) AS data_mb,
  ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS index_mb,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS total_mb,
  TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database'
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;

MySQL Best Practices

Primary Key Selection

Recommended:

-- Auto-increment integer (clustered index friendly)
CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ...
) ENGINE=InnoDB;

Avoid:

-- UUID as primary key (poor clustered index performance)
CREATE TABLE orders (
  id CHAR(36) PRIMARY KEY DEFAULT (UUID()),  -- ❌ Fragmentation
  ...
) ENGINE=InnoDB;

UUID Alternative (MySQL 8.0+):

-- Use UUID_TO_BIN with reordering for better clustering
CREATE TABLE orders (
  id BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID(), 1)),
  ...
) ENGINE=InnoDB;

Composite Index Order

Rule: Equality filters → Range filters → ORDER BY columns

-- Query pattern
SELECT * FROM orders
WHERE customer_id = 123
  AND status IN ('pending', 'processing')
  AND created_at > '2025-01-01'
ORDER BY created_at DESC;

-- Optimal index
CREATE INDEX idx_orders_customer_status_created
ON orders (customer_id, status, created_at);

Avoid SELECT *

Bad:

SELECT * FROM users WHERE id = 123;

Good:

SELECT id, name, email FROM users WHERE id = 123;

Use LIMIT for Large Result Sets

Always limit:

-- Good: Limits results
SELECT * FROM orders ORDER BY created_at DESC LIMIT 100;

-- Bad: No limit on large table
SELECT * FROM orders ORDER BY created_at DESC;

Quick Reference

Index Type Selection

Storage Engine Selection

Configuration Priorities

1. innodb_buffer_pool_size: 70-80% of RAM 2. innodb_log_file_size: 256MB-1GB 3. max_connections: Based on workload (100-200 typical) 4. innodb_flush_log_at_trx_commit: 2 for better performance (1 for durability)