[2026] SQL Query Optimization Practical Complete Guide | Index & Execution Plan

2026년 4월 7일 · 76분 읽기

🎯 What You’ll Learn (Reading Time: 30 minutes)

TL;DR: Learn how to improve SQL query performance 10x. Master immediately applicable optimization techniques from index design to EXPLAIN analysis and N+1 problem resolution. What You’ll Learn:

✅ Master index design principles and practical application
✅ Acquire query execution plan analysis skills with EXPLAIN
✅ Solve anti-patterns like N+1 problems and join optimization
✅ Learn performance improvement know-how from real optimization cases Real-World Applications:
🔥 Make slow queries 10x faster
🔥 Reduce database load (cut server costs)
🔥 Optimize large-scale data processing
🔥 Improve API response times Difficulty: Intermediate | Practical Examples: 15 | Performance Comparisons: Included

Core Message

Reducing one scan is reducing cost. Focus on indexes, EXPLAIN, and common anti-patterns (N+1, etc.).

Why Query Optimization Matters
Index Basics
Analyzing Queries with EXPLAIN
Index Design Strategy
Solving N+1 Problem
Join Optimization
Subquery Optimization
Practical Optimization Cases

Prerequisites (Basics for Beginners)

1. Database Table Structure

Tables store data in rows and columns. Here’s detailed implementation code using SQL. Examine the code while understanding the role of each part.

-- users table
CREATE TABLE users (
  id INT PRIMARY KEY,      -- Unique number
  name VARCHAR(100),       -- Name
  email VARCHAR(100),      -- Email
  created_at TIMESTAMP     -- Registration date
);
-- Data example
+----+--------+-------------------+---------------------+
| id | name   | email             | created_at          |
+----+--------+-------------------+---------------------+
|  1 | Alice  | [email protected]  | 2026-01-01 10:00:00 |
|  2 | Bob    | [email protected]    | 2026-01-02 11:00:00 |
|  3 | Carol  | [email protected] | 2026-01-03 12:00:00 |
+----+--------+-------------------+---------------------+

2. Basic SQL Queries

Here’s an implementation example using SQL. Examine the code while understanding the role of each part.

-- Query data (SELECT)
SELECT name, email FROM users WHERE id = 1;
-- Insert data (INSERT)
INSERT INTO users (name, email) VALUES ('Dave', '[email protected]');
-- Update data (UPDATE)
UPDATE users SET name = 'David' WHERE id = 4;
-- Delete data (DELETE)
DELETE FROM users WHERE id = 4;

3. Query Execution Process

Here’s an implementation example using text. Examine the code while understanding the role of each part.

1. Write query
   SELECT * FROM users WHERE email = '[email protected]';
2. Database executes
   - Table scan (check all rows)
   - Find rows matching condition
   - Return results
3. Problem: What if 1 million rows?
   - May scan 1 million rows
   - Without index: takes seconds to minutes

4. What is an Index?

Index is like a book’s table of contents that lets you jump directly to the location. Here’s an implementation example using text. Try running the code directly to verify its behavior.

Finding without index:
- Read from first page to last
- 1000-page book → must read ~500 pages on average
Finding with index:
- Check page number in table of contents
- Jump directly to that page
- 1000-page book → find in 1 second!

SQL Index: Here’s an implementation example using SQL. Try running the code directly to verify its behavior.

-- Create index
CREATE INDEX idx_email ON users(email);
-- Now searching by email is fast!
SELECT * FROM users WHERE email = '[email protected]';
-- 1 million records → 0.001 seconds

1. Why Query Optimization Matters

Impact of Slow Queries

Here’s an implementation example using text. Try running the code directly to verify its behavior.

Query execution time: 5 seconds
1 user: 5-second wait (inconvenient)
100 users: Server overload
1000 users: Server load concentration
After optimization: 0.05 seconds
→ 100x faster!
→ No problem even with 1000 concurrent users

Real Case

Before optimization: Here’s a simple SQL code example. Try running the code directly to verify its behavior.

-- Execution time: 8.5 seconds
SELECT * FROM orders 
WHERE user_id = 123 
  AND created_at >= '2026-01-01';

After optimization: Here’s an implementation example using SQL. Try running the code directly to verify its behavior.

-- Add index
CREATE INDEX idx_user_created ON orders(user_id, created_at);
-- Execution time: 0.02 seconds (425x faster!)
SELECT * FROM orders 
WHERE user_id = 123 
  AND created_at >= '2026-01-01';

2. Index Basics

How Indexes Work

Full Table Scan (No Index) Here’s an implementation example using text. Examine the code while understanding the role of each part.

SELECT * FROM users WHERE email = '[email protected]';
┌────┬───────┬──────────────────┐
│ id │ name  │ email            │
├────┼───────┼──────────────────┤
│ 1  │ Alice │ [email protected] │ ← Check
│ 2  │ Bob   │ [email protected]   │ ← Check
│ 3  │ Carol │ [email protected]│ ← Check
│....│ ....  │ ....             │ ← Check
│1M  │ ....  │ ....             │ ← Check
Check all rows! (slow)

Index Scan (With Index) Here’s an implementation example using text. Examine the code while understanding the role of each part.

CREATE INDEX idx_email ON users(email);
Index (B-Tree structure):
              [email protected]
            /              \
    [email protected]        [email protected]
   /        \            /        \
alice@....bob@.... carol@....dave@...
Find quickly with binary search!
1 million records → find in ~20 comparisons

Creating Indexes

Here’s an implementation example using SQL. Examine the code while understanding the role of each part.

-- Single column index
CREATE INDEX idx_email ON users(email);
-- Composite index (multiple columns)
CREATE INDEX idx_user_created ON orders(user_id, created_at);
-- Unique index
CREATE UNIQUE INDEX idx_email_unique ON users(email);
-- Drop index
DROP INDEX idx_email ON users;
-- List indexes
SHOW INDEX FROM users;

Index Usage Examples

Here’s an implementation example using SQL. Try running the code directly to verify its behavior.

-- ✅ Index used (fast)
SELECT * FROM users WHERE email = '[email protected]';
-- ❌ Index not used (slow)
SELECT * FROM users WHERE LOWER(email) = '[email protected]';
-- Reason: Functions invalidate indexes
-- ✅ Solution: Function-based index
CREATE INDEX idx_email_lower ON users(LOWER(email));

3. Analyzing Queries with EXPLAIN

What is EXPLAIN?

EXPLAIN shows in what order the optimizer will read.

EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

Reading EXPLAIN Results

MySQL Example: Here’s an implementation example using text. Try running the code directly to verify its behavior.

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

Key Column Explanations: 1) type (Access Method) Here’s an implementation example using text. Try running the code directly to verify its behavior.

Performance order (fast → slow):
system > const > eq_ref > ref > range > index > ALL
✅ const: Find 1 row with PRIMARY KEY or UNIQUE (best)
✅ ref: Find multiple rows with index (good)
✅ range: Index range scan (okay)
❌ index: Full index scan (slow)
❌ ALL: Full table scan (very slow)

2) rows (Estimated Row Count)

rows = 1000000  ← Scan 1 million rows (slow!)
rows = 10       ← Scan only 10 rows (fast!)

3) key (Index Used)

key = NULL      ← No index used (slow!)
key = idx_email ← Index used (fast!)

Practical Example

Before optimization: Here’s an implementation example using SQL. Try running the code directly to verify its behavior.

EXPLAIN SELECT * FROM orders WHERE user_id = 123;
+------+-------+------+---------------+------+------+----------+
| type | table | key  | possible_keys | rows | Extra           |
+------+-------+------+---------------+------+------+----------+
| ALL  | orders| NULL | NULL          | 500K | Using where     |
+------+-------+------+---------------+------+------+----------+
Problem: type = ALL (full scan), rows = 500,000

After optimization: Here’s an implementation example using SQL. Examine the code while understanding the role of each part.

CREATE INDEX idx_user_id ON orders(user_id);
EXPLAIN SELECT * FROM orders WHERE user_id = 123;
+------+-------+------------+---------------+------+-------+
| type | table | key        | possible_keys | rows | Extra |
+------+-------+------------+---------------+------+-------+
| ref  | orders| idx_user_id| idx_user_id   | 50   |       |
+------+-------+------------+---------------+------+-------+
Improvement: type = ref (index used), rows = 50

4. Index Design Strategy

Composite Index Order

Important: Index column order greatly affects performance! Here’s an implementation example using SQL. Examine the code while understanding the role of each part.

-- Query
SELECT * FROM orders 
WHERE user_id = 123 
  AND status = 'completed'
  AND created_at >= '2026-01-01';
-- ❌ Inefficient
CREATE INDEX idx_bad ON orders(created_at, status, user_id);
-- ✅ Efficient
CREATE INDEX idx_good ON orders(user_id, status, created_at);

Index Column Order Principles: Here’s an implementation example using text. Examine the code while understanding the role of each part.

1. = conditions (first)
2. Range conditions (last)
3. High selectivity columns first
Example:
WHERE user_id = 123        ← = condition (1st priority)
  AND status = 'completed' ← = condition (2nd priority)
  AND created_at >= '2026-01-01' ← range condition (3rd priority)
Index: (user_id, status, created_at)

Index Usage Conditions

✅ Index Used: Here’s detailed implementation code using SQL. Examine the code while understanding the role of each part.

-- 1. = condition
SELECT * FROM users WHERE id = 1;
-- 2. IN condition
SELECT * FROM users WHERE id IN (1, 2, 3);
-- 3. Range condition
SELECT * FROM users WHERE created_at >= '2026-01-01';
-- 4. LIKE (prefix matching)
SELECT * FROM users WHERE email LIKE 'alice%';
-- 5. Composite index from left
CREATE INDEX idx_abc ON table(a, b, c);
SELECT * FROM table WHERE a = 1 AND b = 2;  -- Index used

❌ Index Not Used: Here’s detailed implementation code using SQL. Examine the code while understanding the role of each part.

-- 1. Function usage
SELECT * FROM users WHERE YEAR(created_at) = 2026;
-- 2. Calculation
SELECT * FROM users WHERE id + 1 = 2;
-- 3. LIKE (middle/end matching)
SELECT * FROM users WHERE email LIKE '%@gmail.com';
-- 4. OR condition (with non-indexed column)
SELECT * FROM users WHERE id = 1 OR name = 'Alice';
-- 5. Using only middle column of composite index
CREATE INDEX idx_abc ON table(a, b, c);
SELECT * FROM table WHERE b = 2;  -- Index not used!

5. Solving N+1 Problem

What is N+1 Problem?

N+1 problem is a pattern where something that can be fetched once is fetched N additional times. Here’s an implementation example using JavaScript. Efficiently perform tasks with async processing and process data with loops. Examine the code while understanding the role of each part.

// ❌ N+1 problem (101 queries)
// 1. Query user list (1 query)
const users = await db.query('SELECT * FROM users LIMIT 100');
// 2. Query post count for each user (100 queries)
for (const user of users) {
  const posts = await db.query(
    'SELECT COUNT(*) FROM posts WHERE user_id = ?',
    [user.id]
  );
  user.postCount = posts[0].count;
}
// Total 101 queries! (very slow)

Solution 1: JOIN

Here’s an implementation example using SQL. Examine the code while understanding the role of each part.

-- ✅ Solve with 1 query
SELECT 
  u.id,
  u.name,
  COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.name
LIMIT 100;
-- 101 queries → 1 query (100x faster!)

Solution 2: IN Clause

Here’s detailed implementation code using JavaScript. Efficiently perform tasks with async processing and process data with loops. Examine the code while understanding the role of each part.

// ✅ Solve with 2 queries
// 1. Query user list
const users = await db.query('SELECT * FROM users LIMIT 100');
const userIds = users.map(u => u.id);
// 2. Query all users' post counts at once
const postCounts = await db.query(`
  SELECT user_id, COUNT(*) as count
  FROM posts
  WHERE user_id IN (?)
  GROUP BY user_id
`, [userIds]);
// 3. Map results
const countMap = Object.fromEntries(
  postCounts.map(p => [p.user_id, p.count])
);
users.forEach(user => {
  user.postCount = countMap[user.id] || 0;
});
// 101 queries → 2 queries (50x faster!)

N+1 Problem in ORMs

Sequelize (Node.js) Here’s an implementation example using JavaScript. Efficiently perform tasks with async processing and process data with loops. Examine the code while understanding the role of each part.

// ❌ N+1 problem
const users = await User.findAll();
for (const user of users) {
  const posts = await user.getPosts();  // N queries
}
// ✅ Eager Loading
const users = await User.findAll({
  include: [{ model: Post }]  // 1 query (JOIN)
});

Django (Python) Here’s an implementation example using Python. Examine the code while understanding the role of each part.

# ❌ N+1 problem
users = User.objects.all()
for user in users:
    posts = user.posts.all()  # N queries
# ✅ select_related (1:1, N:1)
users = User.objects.select_related('profile').all()
# ✅ prefetch_related (1:N, N:N)
users = User.objects.prefetch_related('posts').all()

6. Join Optimization

JOIN Types

Here’s an implementation example using SQL. Examine the code while understanding the role of each part.

-- INNER JOIN (intersection)
SELECT u.name, o.order_id
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- LEFT JOIN (all from left table)
SELECT u.name, o.order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- RIGHT JOIN (all from right table)
SELECT u.name, o.order_id
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

JOIN Optimization Tips

1) Smaller Table First Here’s an implementation example using SQL. Ensure stability with error handling. Examine the code while understanding the role of each part.

-- ❌ Large table first
SELECT *
FROM orders o  -- 1 million records
JOIN users u ON o.user_id = u.id  -- 1000 records
WHERE u.country = 'KR';
-- ✅ Small table first
SELECT *
FROM users u  -- 1000 records
JOIN orders o ON u.id = o.user_id  -- 1 million records
WHERE u.country = 'KR';

2) Index on JOIN Conditions Here’s an implementation example using SQL. Try running the code directly to verify its behavior.

-- Index required on JOIN condition columns
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_id ON users(id);  -- Auto-created if PRIMARY KEY
SELECT *
FROM users u
JOIN orders o ON u.id = o.user_id;

3) Filter with WHERE First Here’s an implementation example using SQL. Examine the code while understanding the role of each part.

-- ❌ Filter after JOIN
SELECT *
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at >= '2026-01-01';
-- ✅ Filter first with subquery
SELECT *
FROM users u
JOIN (
  SELECT * FROM orders 
  WHERE created_at >= '2026-01-01'
) o ON u.id = o.user_id;

7. Subquery Optimization

Inefficient Subqueries

Here’s an implementation example using SQL. Try running the code directly to verify its behavior.

-- ❌ Correlated Subquery
-- Subquery executes for each row of outer query (slow!)
SELECT 
  u.name,
  (SELECT COUNT(*) FROM posts WHERE user_id = u.id) as post_count
FROM users u;
-- If 1000 users, subquery executes 1000 times!

Optimize with JOIN

Here’s an implementation example using SQL. Try running the code directly to verify its behavior.

-- ✅ Use JOIN (fast)
SELECT 
  u.name,
  COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.name;
-- Solved with 1 query!

EXISTS vs IN

Here’s detailed implementation code using SQL. Examine the code while understanding the role of each part.

-- Check existence only in large table
-- ✅ Use EXISTS (fast)
SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o 
  WHERE o.user_id = u.id
);
-- ❌ Use IN (slow)
SELECT * FROM users u
WHERE u.id IN (
  SELECT user_id FROM orders
);
-- EXISTS stops at first match
-- IN loads all results into memory

8. Practical Optimization Cases

Case 1: Pagination

❌ Using OFFSET (slow) Here’s an implementation example using SQL. Try running the code directly to verify its behavior.

-- Query 1 millionth page
SELECT * FROM posts 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 1000000;
-- Problem: Reads all 1 million to skip them!
-- Execution time: 5 seconds

✅ Cursor-Based Pagination (fast) Here’s an implementation example using SQL. Examine the code while understanding the role of each part.

-- First page
SELECT * FROM posts 
ORDER BY created_at DESC 
LIMIT 20;
-- Next page (using last created_at)
SELECT * FROM posts 
WHERE created_at < '2026-03-31 10:00:00'
ORDER BY created_at DESC 
LIMIT 20;
-- Uses index only, no skipping!
-- Execution time: 0.01 seconds (500x faster!)

Case 2: COUNT(*) Optimization

❌ Full Count (slow)

-- 1 million record table
SELECT COUNT(*) FROM posts;
-- Execution time: 2 seconds

✅ Use Approximation (fast) Here’s an implementation example using SQL. Examine the code while understanding the role of each part.

-- MySQL
SELECT TABLE_ROWS 
FROM information_schema.TABLES 
WHERE TABLE_NAME = 'posts';
-- Execution time: 0.001 seconds
-- PostgreSQL
SELECT reltuples::bigint 
FROM pg_class 
WHERE relname = 'posts';

✅ Use Caching Here’s an implementation example using JavaScript. Efficiently perform tasks with async processing and handle branching with conditionals. Try running the code directly to verify its behavior.

// Cache in Redis
const cachedCount = await redis.get('posts:count');
if (cachedCount) {
  return cachedCount;
}
const count = await db.query('SELECT COUNT(*) FROM posts');
await redis.setex('posts:count', 3600, count); // Cache for 1 hour
return count;

Case 3: LIKE Search Optimization

❌ Middle Matching (index not used)

SELECT * FROM users WHERE name LIKE '%alice%';
-- Full scan! (slow)

✅ Full-Text Search Index Here’s an implementation example using SQL. Examine the code while understanding the role of each part.

-- MySQL
CREATE FULLTEXT INDEX idx_name_fulltext ON users(name);
SELECT * FROM users 
WHERE MATCH(name) AGAINST('alice');
-- Uses full-text search index! (fast)
-- PostgreSQL
CREATE INDEX idx_name_gin ON users USING gin(to_tsvector('english', name));
SELECT * FROM users 
WHERE to_tsvector('english', name) @@ to_tsquery('alice');

✅ Use Elasticsearch Here’s an implementation example using JavaScript. Efficiently perform tasks with async processing. Try running the code directly to verify its behavior.

// Elasticsearch recommended for large-scale text search
const result = await esClient.search({
  index: 'users',
  body: {
    query: {
      match: { name: 'alice' }
    }
  }
});

9. Query Optimization Checklist

Essential Checks

Here’s an implementation example using text. Examine the code while understanding the role of each part.

✅ Check execution plan with EXPLAIN
✅ Add index if type is ALL
✅ Improve WHERE conditions if rows is large
✅ Confirm indexes exist on JOIN conditions
✅ Check for N+1 problems
✅ Remove unnecessary SELECT *
✅ Use LIMIT (only what's needed)
✅ Use JOIN instead of subqueries
✅ Minimize function usage
✅ Optimize composite index order

Query Writing Principles

1) Query Only Needed Columns Here’s an implementation example using SQL. Try running the code directly to verify its behavior.

-- ❌ Query all columns
SELECT * FROM users;
-- ✅ Only needed columns
SELECT id, name, email FROM users;

2) Use DISTINCT Carefully Here’s an implementation example using SQL. Try running the code directly to verify its behavior.

-- ❌ DISTINCT (requires sorting, slow)
SELECT DISTINCT user_id FROM orders;
-- ✅ GROUP BY (utilizes index)
SELECT user_id FROM orders GROUP BY user_id;

3) UNION vs UNION ALL Here’s an implementation example using SQL. Try running the code directly to verify its behavior.

-- ❌ UNION (removes duplicates, slow)
SELECT id FROM users
UNION
SELECT id FROM deleted_users;
-- ✅ UNION ALL (allows duplicates, fast)
SELECT id FROM users
UNION ALL
SELECT id FROM deleted_users;

10. Index Monitoring

Finding Unused Indexes

MySQL: Here’s an implementation example using SQL. Try running the code directly to verify its behavior.

-- Index usage statistics
SELECT 
  TABLE_NAME,
  INDEX_NAME,
  SEQ_IN_INDEX,
  COLUMN_NAME
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'mydb'
ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;

PostgreSQL: Here’s an implementation example using SQL. Examine the code while understanding the role of each part.

-- Unused indexes
SELECT 
  schemaname,
  tablename,
  indexname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0  -- Never used
ORDER BY schemaname, tablename;

Check Index Size

Here’s detailed implementation code using SQL. Examine the code while understanding the role of each part.

-- MySQL
SELECT 
  TABLE_NAME,
  INDEX_NAME,
  ROUND(STAT_VALUE * @@innodb_page_size / 1024 / 1024, 2) as size_mb
FROM mysql.innodb_index_stats
WHERE DATABASE_NAME = 'mydb'
ORDER BY size_mb DESC;
-- PostgreSQL
SELECT
  tablename,
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

11. Practical Optimization Workflow

Step 1: Find Slow Queries

Enable MySQL Slow Query Log: Here’s an implementation example using SQL. Try running the code directly to verify its behavior.

-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- Log queries over 1 second
-- Check log file location
SHOW VARIABLES LIKE 'slow_query_log_file';

Application Logging: Here’s an implementation example using JavaScript. Efficiently perform tasks with async processing and handle branching with conditionals. Try running the code directly to verify its behavior.

// Measure query execution time
const start = Date.now();
const result = await db.query('SELECT ...');
const duration = Date.now() - start;
if (duration > 1000) {
  console.warn(`Slow query (${duration}ms):`, query);
}

Step 2: EXPLAIN Analysis

Here’s an implementation example using SQL. Try running the code directly to verify its behavior.

EXPLAIN SELECT * FROM orders 
WHERE user_id = 123 
  AND created_at >= '2026-01-01';
-- Check type, rows, key

Step 3: Add Index

CREATE INDEX idx_user_created ON orders(user_id, created_at);

Step 4: Re-measure

Here’s an implementation example using SQL. Try running the code directly to verify its behavior.

EXPLAIN SELECT * FROM orders 
WHERE user_id = 123 
  AND created_at >= '2026-01-01';
-- Verify improvement

Step 5: Production Deployment

Here’s an implementation example using SQL. Try running the code directly to verify its behavior.

-- Index creation may cause table lock
-- Online index creation (MySQL 5.6+)
CREATE INDEX idx_user_created ON orders(user_id, created_at) ALGORITHM=INPLACE, LOCK=NONE;
-- PostgreSQL
CREATE INDEX CONCURRENTLY idx_user_created ON orders(user_id, created_at);

12. Database-Specific Optimization

MySQL Optimization

Here’s an implementation example using SQL. Try running the code directly to verify its behavior.

-- 1. Query cache (MySQL 5.7 and below)
SET GLOBAL query_cache_size = 268435456;  -- 256MB
-- 2. InnoDB buffer pool size
SET GLOBAL innodb_buffer_pool_size = 2147483648;  -- 2GB
-- 3. Connection pool
SET GLOBAL max_connections = 200;

PostgreSQL Optimization

Here’s an implementation example using SQL. Examine the code while understanding the role of each part.

-- 1. Update statistics
ANALYZE users;
-- 2. VACUUM (reclaim space)
VACUUM ANALYZE users;
-- 3. Configuration optimization
-- postgresql.conf
shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 50MB

13. Monitoring Tools

Query Profiling

MySQL: Here’s an implementation example using SQL. Try running the code directly to verify its behavior.

-- Enable profiling
SET profiling = 1;
-- Execute query
SELECT * FROM users WHERE email = '[email protected]';
-- Check profile
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

PostgreSQL:

-- Execution plan + actual execution time
EXPLAIN ANALYZE 
SELECT * FROM users WHERE email = '[email protected]';

APM Tools

Here’s an implementation example using text. Try running the code directly to verify its behavior.

Recommended tools:
- New Relic: Comprehensive monitoring
- Datadog: Real-time monitoring
- Percona Monitoring: MySQL specialist
- pgBadger: PostgreSQL log analysis

14. Practical Example: Dashboard Query Optimization

Before Optimization (8 seconds)

Here’s an implementation example using SQL. Try running the code directly to verify its behavior.

-- Query dashboard statistics
SELECT 
  (SELECT COUNT(*) FROM users) as total_users,
  (SELECT COUNT(*) FROM orders) as total_orders,
  (SELECT SUM(amount) FROM orders WHERE status = 'completed') as revenue,
  (SELECT COUNT(*) FROM orders WHERE created_at >= CURDATE()) as today_orders;
-- 4 independent queries execute (slow!)

After Optimization (0.05 seconds)

Here’s an implementation example using SQL. Examine the code while understanding the role of each part.

-- Consolidate into 1 query
SELECT 
  COUNT(DISTINCT u.id) as total_users,
  COUNT(o.id) as total_orders,
  SUM(CASE WHEN o.status = 'completed' THEN o.amount ELSE 0 END) as revenue,
  SUM(CASE WHEN o.created_at >= CURDATE() THEN 1 ELSE 0 END) as today_orders
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- 160x faster!

Additional Optimization: Caching

Here’s an implementation example using JavaScript. Efficiently perform tasks with async processing and handle branching with conditionals. Examine the code while understanding the role of each part.

// Redis caching
async function getDashboardStats() {
  const cached = await redis.get('dashboard:stats');
  if (cached) {
    return JSON.parse(cached);
  }
  
  const stats = await db.query('SELECT ...');
  await redis.setex('dashboard:stats', 300, JSON.stringify(stats)); // 5 minutes
  return stats;
}

FAQ

Q1. Are more indexes always better? No! Too many indexes:

Slow INSERT/UPDATE/DELETE (indexes also update)
Increased disk space usage
Create only necessary indexes Q2. Should I create indexes for all WHERE conditions? No. Create only for high-selectivity columns. Here’s an implementation example using SQL. Try running the code directly to verify its behavior.

-- ❌ Low selectivity (index ineffective)
CREATE INDEX idx_gender ON users(gender);  -- Only 2 values (M/F)
-- ✅ High selectivity (index effective)
CREATE INDEX idx_email ON users(email);  -- All values different

Q3. Composite index vs single indexes? Depends on query patterns. Here’s an implementation example using SQL. Try running the code directly to verify its behavior.

-- Always used together: composite index
WHERE user_id = 123 AND status = 'completed'
→ CREATE INDEX idx_user_status ON orders(user_id, status);
-- Used separately: single indexes
WHERE user_id = 123  (query 1)
WHERE status = 'completed'  (query 2)
→ CREATE INDEX idx_user_id ON orders(user_id);
→ CREATE INDEX idx_status ON orders(status);

Q4. Still slow after adding index Here’s an implementation example using SQL. Try running the code directly to verify its behavior.

-- Check with EXPLAIN
EXPLAIN SELECT ...;
-- Reasons index not used:
-- 1. Function usage: WHERE YEAR(created_at) = 2026
-- 2. Type mismatch: WHERE id = '123' (id is INT but string used)
-- 3. OR condition: WHERE a = 1 OR b = 2
-- 4. Composite index order mismatch

Summary

Key Points

Indexes:

Like a book’s table of contents
Create on WHERE, JOIN, ORDER BY columns
Composite index order matters EXPLAIN:
Check query execution plan
Optimize if type = ALL
Add index if rows is large N+1 Problem:
Executing N+1 queries when 1 is possible
Solve with JOIN or IN Optimization Order:

Find slow queries
Analyze with EXPLAIN
Add indexes
Re-measure

Performance Improvement Effects

Here’s an implementation example using text. Try running the code directly to verify its behavior.

Before → After optimization
Full Scan:     5s → 0.01s (500x)
N+1 Problem:   10s → 0.1s (100x)
Composite Index: 2s → 0.02s (100x)
Pagination:    3s → 0.01s (300x)

🎯 What You’ll Learn (Reading Time: 30 minutes)

Core Message

Reducing one scan is reducing cost. Focus on indexes, EXPLAIN, and common anti-patterns (N+1, etc.).

Table of Contents

Prerequisites (Basics for Beginners)

1. Database Table Structure

2. Basic SQL Queries

3. Query Execution Process

4. What is an Index?

1. Why Query Optimization Matters

Impact of Slow Queries

Real Case

2. Index Basics

How Indexes Work

Creating Indexes

Index Usage Examples

3. Analyzing Queries with EXPLAIN

What is EXPLAIN?

Reading EXPLAIN Results

Practical Example

4. Index Design Strategy

Composite Index Order

Index Usage Conditions

5. Solving N+1 Problem

What is N+1 Problem?

Solution 1: JOIN

Solution 2: IN Clause

N+1 Problem in ORMs

6. Join Optimization

JOIN Types

JOIN Optimization Tips

7. Subquery Optimization

Inefficient Subqueries

Optimize with JOIN

EXISTS vs IN

8. Practical Optimization Cases

Case 1: Pagination

Case 2: COUNT(*) Optimization

Case 3: LIKE Search Optimization

9. Query Optimization Checklist

Essential Checks

Query Writing Principles

10. Index Monitoring

Finding Unused Indexes

Check Index Size

11. Practical Optimization Workflow

Step 1: Find Slow Queries

Step 2: EXPLAIN Analysis

Step 3: Add Index

Step 4: Re-measure

Step 5: Production Deployment

12. Database-Specific Optimization

MySQL Optimization

PostgreSQL Optimization

13. Monitoring Tools

Query Profiling

APM Tools

14. Practical Example: Dashboard Query Optimization

Before Optimization (8 seconds)

After Optimization (0.05 seconds)

Additional Optimization: Caching

FAQ

Summary

Key Points

Performance Improvement Effects

Recommended Next Articles