optimize-query

name	optimize-query
description	Use when analyzing slow queries, optimizing SQL/JPA performance, reviewing EXPLAIN plans, or troubleshooting database bottlenecks.
argument-hint	["file-or-query"]
allowed-tools	Read, Grep, Glob, Bash
context	fork

Optimize Query

Help optimize database queries (SQL/JPA) for better performance in production environments. Focus on real-world performance bottlenecks with measurable improvements.

Analysis Workflow

Step 1: Understand Current Performance

Gather baseline metrics:

-- Execution time
EXPLAIN ANALYZE SELECT ...;

-- Row counts
SELECT COUNT(*) FROM orders;
SELECT COUNT(*) FROM order_items;

-- Table size
SELECT
    table_name,
    table_rows,
    data_length / 1024 / 1024 AS size_mb
FROM information_schema.tables
WHERE table_schema = 'your_database';

Required Information:

• Query execution time (ms)
• Data volume (number of rows)
• Expected QPS (queries per second)
• Current infrastructure (CPU, memory, disk)

Step 2: Identify Performance Problems

Problem Pattern 1: N+1 Query Problem

Symptom: Multiple queries when one would suffice

// Bad: N+1 Problem (1 + N queries)
List<Order> orders = orderRepository.findAll();  // 1 query
for (Order order : orders) {
    List<OrderItem> items = itemRepository.findByOrderId(order.getId());  // N queries
    order.setItems(items);
}

Impact: If you have 1000 orders, this executes 1001 queries!

Solution:

// Good: Single query with JOIN FETCH
@Query("SELECT DISTINCT o FROM Order o LEFT JOIN FETCH o.items")
List<Order> findAllWithItems();

// Alternative: Batch loading
@EntityGraph(attributePaths = {"items"})
List<Order> findAll();

Performance Gain: 1001 queries → 1 query (99.9% reduction)

Problem Pattern 2: Missing Index

Symptom: Full table scan on WHERE clause

-- EXPLAIN output shows: type=ALL, rows=5000000
EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;

Solution:

-- Create index on commonly queried column
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- Verify index usage
EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;
-- Should now show: type=ref, key=idx_orders_customer_id, rows=~50

Performance Gain: Full scan of 5M rows → Index lookup of ~50 rows

Problem Pattern 3: Inefficient Pagination

// Bad: OFFSET grows, performance degrades linearly
@Query("SELECT o FROM Order o ORDER BY o.createdAt DESC")
Page<Order> findAll(Pageable pageable);  // OFFSET 10000 LIMIT 20

Problem: Database must scan 10,020 rows to return 20 rows.

Solution: Keyset pagination

// Good: Use last seen ID
@Query("SELECT o FROM Order o WHERE o.id < :lastId ORDER BY o.id DESC")
List<Order> findNextPage(@Param("lastId") Long lastId, Pageable pageable);

Problem Pattern 4: SELECT * When You Need Few Columns

// Bad: Fetching unnecessary data
@Query("SELECT o FROM Order o WHERE o.status = :status")
List<Order> findByStatus(String status);  // Loads all columns

// Good: Projection for specific fields
@Query("SELECT new com.example.dto.OrderSummary(o.id, o.totalAmount, o.status) " +
       "FROM Order o WHERE o.status = :status")
List<OrderSummary> findSummaryByStatus(String status);

Problem Pattern 5: Inefficient Joins

-- Bad: Cartesian product risk
SELECT *
FROM orders o
JOIN order_items oi
WHERE o.customer_id = 123;  -- Missing join condition!

-- Good: Proper join condition
SELECT o.*, oi.*
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE o.customer_id = 123;

Step 3: Index Strategy

When to Create Index

Create index when:

• Column is frequently used in WHERE clause
• Column is used in JOIN conditions
• Column is used in ORDER BY
• Query selectivity is high (index returns < 15% of rows)

-- High selectivity: Good candidate
SELECT * FROM users WHERE email = '[e-mail]';  -- Returns 1 row

-- Low selectivity: Poor candidate
SELECT * FROM users WHERE is_active = true;  -- Returns 90% of rows

Composite Index Column Order

Rule: Most selective column first, then by query frequency

-- Query pattern
WHERE status = 'PENDING' AND customer_id = 123 AND created_at > '2024-01-01'

-- Analyze selectivity
SELECT
    COUNT(DISTINCT status) as status_values,  -- Result: 5
    COUNT(DISTINCT customer_id) as customers, -- Result: 50000
    COUNT(DISTINCT DATE(created_at)) as dates -- Result: 365
FROM orders;

-- Optimal index: customer_id (most selective) first
CREATE INDEX idx_orders_lookup ON orders(customer_id, status, created_at);

Covering Index

Index contains all columns needed by query:

-- Query needs: id, customer_id, total_amount
SELECT id, total_amount
FROM orders
WHERE customer_id = 123;

-- Covering index (no table access needed)
CREATE INDEX idx_orders_covering ON orders(customer_id, id, total_amount);

Benefit: Index-only scan - no table access needed

Step 4: JPA-Specific Optimizations

FetchType Strategy

// Default LAZY loading causes N+1
@Entity
public class Order {
    @OneToMany(mappedBy = "order")  // Default: LAZY
    private List<OrderItem> items;
}

// Solution 1: EAGER (use cautiously)
@OneToMany(mappedBy = "order", fetch = FetchType.EAGER)
private List<OrderItem> items;

// Solution 2: Query-specific fetch (preferred)
@Query("SELECT o FROM Order o LEFT JOIN FETCH o.items WHERE o.id = :id")
Optional<Order> findByIdWithItems(@Param("id") Long id);

// Solution 3: EntityGraph
@EntityGraph(attributePaths = {"items", "customer"})
Optional<Order> findById(Long id);

Batch Fetching

// Configure in application.properties
spring.jpa.properties.hibernate.default_batch_fetch_size=10

// Or per-entity
@Entity
@BatchSize(size = 10)
public class Order {
    @OneToMany
    @BatchSize(size = 10)
    private List<OrderItem> items;
}

Impact: Fetches associations in batches instead of one-by-one

Query Hints

// Read-only query optimization
@QueryHints(@QueryHint(name = "org.hibernate.readOnly", value = "true"))
List<Order> findByStatus(String status);

// Cache query results
@QueryHints(@QueryHint(name = "org.hibernate.cacheable", value = "true"))
List<Product> findAllProducts();

Step 5: Advanced Optimization Techniques

Denormalization for Read-Heavy Workloads

// Before: Join on every query
SELECT o.id, c.name, c.email
FROM orders o
JOIN customers c ON o.customer_id = c.id;

// After: Denormalize frequently accessed data
@Entity
public class Order {
    private Long customerId;
    private String customerName;  // Denormalized
    private String customerEmail; // Denormalized
}

Trade-off: Faster reads, slower writes, data duplication

Output Format

For each optimization, provide:

1. Current State Assessment

Execution Time: 3.5 seconds
Rows Examined: 5,000,000
Rows Returned: 150
Index Used: NONE
Problem: Full table scan on orders table

2. Root Cause Analysis

The query uses WHERE customer_id = 123 but there's no index
on customer_id column. MySQL performs a full table scan,
examining all 5M rows to find matching records.

EXPLAIN output:
type: ALL
rows: 5000000
Extra: Using where

3. Optimization Solution

SQL Changes:

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

JPA Changes:

@Entity
@Table(name = "orders", indexes = {
    @Index(name = "idx_orders_customer_id", columnList = "customer_id")
})
public class Order {
    // entity fields
}

4. Expected Improvement

Execution Time: 3.5s → 45ms (98.7% faster)
Rows Examined: 5,000,000 → 150 (99.997% reduction)
Index Used: idx_orders_customer_id

5. Verification Steps

-- Test query performance
SET profiling = 1;
SELECT * FROM orders WHERE customer_id = 123;
SHOW PROFILES;

-- Verify index usage
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

-- Check index statistics
SHOW INDEX FROM orders;

6. Trade-offs and Warnings

Index Overhead:

• Additional disk space: ~50MB for this index
• INSERT/UPDATE performance: ~10% slower (extra index maintenance)
• Worth it if reads >> writes (typical OLTP pattern)

Common Query Anti-Patterns

1. Using Functions in WHERE Clause

-- Bad: Prevents index usage
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- Good: Index-friendly
SELECT * FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

2. OR Conditions Spanning Multiple Columns

-- Bad: Can't use composite index efficiently
SELECT * FROM orders WHERE customer_id = 123 OR status = 'PENDING';

-- Good: Use UNION
SELECT * FROM orders WHERE customer_id = 123
UNION
SELECT * FROM orders WHERE status = 'PENDING';

3. Implicit Type Conversion

-- Bad: customer_id is INT, but querying with STRING
SELECT * FROM orders WHERE customer_id = '123';  -- Index not used!

-- Good: Match column type
SELECT * FROM orders WHERE customer_id = 123;

Optimization Checklist

Before optimizing:

• Measure baseline performance (execution time, rows examined)
• Identify exact bottleneck (EXPLAIN ANALYZE)
• Check existing indexes (SHOW INDEX)
• Understand data volume and growth rate
• Know read/write ratio

After optimizing:

• Verify performance improvement (before/after metrics)
• Check EXPLAIN output (index usage confirmed)
• Test with realistic data volume
• Monitor for 24-48 hours (catch edge cases)
• Document the change (comments in code, migration scripts)

Performance Tuning Priorities

1. Eliminate N+1 Queries (highest impact, usually easy fix)
2. Add Missing Indexes (high impact on large tables)
3. Optimize JOIN Operations (especially multi-table joins)
4. Implement Pagination (for large result sets)
5. Use Projections (select only needed columns)
6. Consider Caching (for rarely changing, frequently read data)
7. Database Connection Pooling (prevent connection overhead)

When to Apply

• 慢查詢分析或 EXPLAIN plan 審查
• N+1 查詢問題排查
• SQL/JPA 效能瓶頸排除
• Index 設計或調整建議

When NOT to Optimize

• Table has < 1000 rows (overhead > benefit)
• Query runs < 100ms and isn't frequent
• Write-heavy workload where index overhead hurts more
• Development/testing environment (optimize for production data)

IMPORTANT: All output must be in Traditional Chinese (繁體中文)

Gotchas

• EXPLAIN 在空表或少量資料上結果不準確：確保測試資料夠多（接近生產環境規模）才能得到有意義的 EXPLAIN 輸出
• 加 index 前先確認 selectivity：SELECT COUNT(DISTINCT col) / COUNT(*) FROM table — 低選擇性（如 status 只有 5 種值）的 index 效果很差
• MySQL query optimizer 可能忽略你的 index：用 FORCE INDEX 前先理解為什麼 optimizer 沒選這個 index，強制使用反而可能更慢
• Spring Data JPA findAll() 無分頁保護：資料量大時直接 OOM，查詢前確認是否需要 Pageable 或 limit
• JOIN FETCH + Pageable 組合有陷阱：Hibernate 會先載入全表再記憶體分頁（HHH000104 警告），需改為子查詢分頁