Query Optimization: Techniques, Execution Plans & Performance Tuning

TAKEAWAYS:

• Query optimization reduces execution time by rewriting SQL, choosing efficient indexes, and analyzing execution plans.
• The query optimizer in DBMS (e.g., Oracle, MySQL) uses cost-based optimization to pick the best execution path.
• Indexing strategies (B-tree, bitmap, composite) speed up searches but slow down writes—balance is key.
• Query hints (e.g., /*+ INDEX */) override the optimizer’s default choices when needed.
• Partitioning and materialized views pre-compute results for faster reads in high-traffic apps like Daraz.
• Real-world tuning: Optimizing a bank’s loan interest calculation query can save hours of CPU time daily.

---

What is Query Optimization?

Query optimization is the process of improving the speed, efficiency, and scalability of SQL queries by:

• Reducing I/O operations (disk reads/writes).
• Minimizing CPU usage (e.g., avoiding full table scans).
• Lowering memory consumption (e.g., using indexes to avoid sorting large datasets).

Why does it matter? Poorly optimized queries can:

• Slow down applications (e.g., eSewa transactions during peak hours).
• Increase server costs (e.g., Ncell’s billing system under heavy load).
• Cause timeouts or crashes in high-traffic systems (e.g., Daraz order processing).

---

How the Query Optimizer Works

The query optimizer (part of the DBMS) follows these steps to execute a query efficiently:

1. Parsing: Checks syntax and converts SQL to an internal tree.
2. Semantic Analysis: Validates objects (tables, columns) exist.
3. Query Rewriting: Simplifies logic (e.g., WHERE a > 5 AND a < 10 → WHERE a BETWEEN 6 AND 9).
4. Logical Optimization: Chooses the best logical operations (e.g., join order).
5. Physical Optimization: Decides how to access data (e.g., index scan vs. full scan).
6. Execution Plan: Generates a step-by-step plan (e.g., "Use index on customer_id").
7. Execution: Runs the plan and returns results.

---

Key Optimization Techniques

1. Indexing Strategies

Indexes speed up search, sort, and join operations but slow down INSERT/UPDATE/DELETE. Choose wisely!

Worked Example: Optimizing a Bank Loan Query Problem: A bank’s loan processing system runs this query daily:

SELECT * FROM loans
WHERE customer_id = 12345 AND interest_rate > 8.5;

Before Optimization:

• No index → Full table scan (slow for 1M+ records).
• Execution time: 4.2 seconds.

After Optimization:

CREATE INDEX idx_loans_customer_rate ON loans(customer_id, interest_rate);

• Uses composite index → Index scan (0.003 seconds).
• Savings: 99.9% faster!

---

2. Query Rewriting

Rewrite queries to reduce work. Common techniques:

a. Avoid SELECT *

-- Bad: Fetches all columns (wastes I/O)
SELECT * FROM orders;

-- Good: Fetch only needed columns
SELECT order_id, customer_id, amount FROM orders;

b. Use EXISTS Instead of IN for Subqueries

-- Slow: Returns all matching rows first
SELECT * FROM customers WHERE customer_id IN (SELECT customer_id FROM orders);

-- Faster: Stops at first match
SELECT * FROM customers WHERE EXISTS (SELECT 1 FROM orders WHERE customer_id = customers.customer_id);

c. Replace OR with UNION ALL

-- Slow: Evaluates both sides of OR
SELECT * FROM products WHERE category = 'Electronics' OR category = 'Clothing';

-- Faster: Uses UNION ALL (no duplicate removal)
SELECT * FROM products WHERE category = 'Electronics'
UNION ALL
SELECT * FROM products WHERE category = 'Clothing';

d. Use JOIN Instead of Subqueries

-- Slow: Nested subquery (executed per row)
SELECT o.order_id, c.customer_name
FROM orders o
WHERE EXISTS (SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id);

-- Faster: Single JOIN
SELECT o.order_id, c.customer_name
FROM orders o JOIN customers c ON o.customer_id = c.customer_id;

---

3. Execution Plan Analysis

The execution plan shows how the DBMS executes a query. Use:

• Oracle: EXPLAIN PLAN + DBMS_XPLAN.DISPLAY.
• MySQL: EXPLAIN SELECT ....
• SQL Server: SET SHOWPLAN_TEXT ON.

Example Plan for a Join Query:

Key Metrics to Check:

• Cost: Lower is better (relative to other plans).
• Rows: Estimated rows processed (high = inefficient).
• Type: INDEX (good) vs. FULL (bad).

---

4. Partitioning and Materialized Views

a. Partitioning

Splits large tables into smaller physical pieces (e.g., by date or range). Example: Daraz’s order table partitioned by order_date:

CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

Benefits:

• Faster queries (scans only relevant partitions).
• Easier backups (restore only needed partitions).

b. Materialized Views

Pre-computes and stores query results for reuse. Example: NEPSE’s daily stock summary:

CREATE MATERIALIZED VIEW daily_stock_summary AS
SELECT stock_id, SUM(volume) as total_volume, AVG(price) as avg_price
FROM trades
WHERE trade_date = SYSDATE - 1
GROUP BY stock_id;

Refresh manually or on schedule:

REFRESH MATERIALIZED VIEW daily_stock_summary;

---

In the Real World

1. eSewa (Nepal)

   • Problem: High latency during peak hours (e.g., festival seasons).
   • Solution: Optimized SQL queries for payment processing by:
     • Adding composite indexes on (user_id, transaction_date).
     • Rewriting OR conditions to UNION ALL in transaction logs.
   • Result: Reduced query time from 2.5s → 80ms during Diwali.

2. Ncell (Nepal)

   • Problem: Billing system slowdowns due to SELECT * FROM calls on 10M+ records.
   • Solution: Replaced with partitioned tables by billing_month and materialized views for daily summaries.
   • Result: Billing reports now generate in 3s vs. 2 minutes.

3. Google (YouTube)

   • Problem: Video recommendations needed fast lookups on user watch history.
   • Solution: Used bitmap indexes on low-cardinality columns (e.g., content_category) and query hints to force index usage.
   • Result: Recommendation latency dropped from 500ms → 120ms.

---

Common Pitfalls and How to Avoid Them

---

Exam Tip

How This Unit is Tested:

1. Theory (30%):

   • Define query optimization, execution plan, and cost-based optimization.
   • Compare B-tree vs. bitmap indexes (when to use each).
   • Explain partitioning and materialized views with examples.

2. Practical (70%):

   • Rewrite queries to improve performance (e.g., replace IN with EXISTS).
   • Analyze execution plans (identify bottlenecks like full table scans).
   • Design indexes for given scenarios (e.g., "Optimize a Daraz order lookup query").
   • Calculate query cost (e.g., "Which join order is cheaper?").

Common Exam Questions:

• "Explain how Oracle’s cost-based optimizer works. What factors does it consider?"
• "Rewrite this slow query using JOIN instead of subqueries."
• "Given an execution plan, identify the slowest step and suggest fixes."
• "When would you use a materialized view vs. a regular view?"

Pro Tip:

• Always check the execution plan before optimizing. Sometimes the DBMS’s default choice is already optimal!
• Test changes in a staging environment (e.g., a copy of the Daraz database) before applying to production.

---