📘 Query Optimization Techniques

Query optimization is a critical skill for any SQL developer. In this chapter, we will explore advanced techniques to optimize your SQL queries, focusing on analyzing EXPLAIN plans and reducing the impact of nested subqueries.

💡 Understanding EXPLAIN Plans

An EXPLAIN plan is a tool that helps you understand how MySQL executes your query. It provides insights into the query execution strategy, including join order, access methods, and row estimates.

EXPLAIN SELECT * FROM orders WHERE customer_id = 10;

• `id`: The query identifier.
• `select_type`: The type of select (e.g., SIMPLE, PRIMARY, SUBQUERY).
• `table`: The table being accessed.
• `partitions`: Partitions used for the query.
• `type`: The access method (e.g., ALL, INDEX, RANGE).
• `possible_keys`: Possible indexes that could be used.
• `key`: The index actually used.
• `key_len`: Length of the key used.
• `ref`: Columns or constants compared to the key.
• `rows`: Estimated number of rows to examine.
• `filtered`: Percentage of rows filtered by conditions.

💡 Key EXPLAIN Plan Analysis Steps

• Identify slow queries with high `rows` estimates.
• Check for full table scans (`type: ALL`).
• Verify if optimal indexes are being used.
• Analyze join order and execution strategy.
• Optimize row estimates using statistics.

✅ Best Practices for EXPLAIN Plans

• Always include `EXPLAIN` in your query optimization workflow.
• Use `ANALYZE TABLE` to update statistics regularly.
• Monitor `rows` and `filtered` values for performance indicators.
• Experiment with different join orders using hints.

❌ Common Mistakes When Using EXPLAIN Plans

• Ignoring row estimates and filtered percentages.
• Not updating table statistics regularly.
• Focusing only on execution time without considering query structure.
• Over-optimizing without understanding the actual query behavior.

💡 Reducing Nested Subqueries

Nested subqueries can significantly impact performance, especially in complex queries. Understanding when and how to replace them with more efficient alternatives is crucial.

❌ When to Avoid Nested Subqueries

• Subqueries in the `SELECT` clause.
• Correlated subqueries with high complexity.
• Nested subqueries without proper indexing.

SELECT (SELECT COUNT(*) FROM orders WHERE customer_id = o.id) AS order_count
FROM customers o;

✅ Optimizing Nested Subqueries

• Replace nested subqueries with `JOIN`s where possible.
• Use Common Table Expressions (CTEs) for complex logic.
• Index columns used in subquery conditions.
• Optimize row comparisons using temporary tables.

WITH order_counts AS (
    SELECT customer_id, COUNT(*) as total
    FROM orders
    GROUP BY customer_id
)
SELECT o.name, oc.total
FROM customers o
LEFT JOIN order_counts oc ON o.id = oc.customer_id;

💡 Key Takeaways for Query Optimization

• Analyze EXPLAIN plans to understand query execution.
• Optimize indexes based on access patterns.
• Avoid unnecessary nested subqueries.
• Use CTEs and temporary tables for complex logic.

📘 Index Strategy

💡 Introduction to Index Strategy

Indexes are crucial for optimizing database performance. They allow databases to quickly locate and retrieve specific rows of data, reducing query execution time. However, they also introduce overhead in terms of space and time when performing insertions, updates, and deletions.

✅ When to Use Indexes

• `Columns frequently used in WHERE clauses` - Speed up filtering operations.
• `Columns involved in JOIN operations` - Improve join performance between tables.
• `Columns sorted in ORDER BY or GROUP BY clauses` - Enhance sorting and grouping efficiency.

CREATE INDEX idx_customer_name ON customers (name);

CREATE INDEX idx_order_date ON orders (order_date);

❌ When Not to Use Indexes

• `Small tables with few rows` - The overhead of maintaining the index outweighs potential benefits.
• `Columns with low cardinality` - Indexes are less effective when many rows share the same value.
• `Highly mutable columns` - Columns that frequently change values.

// Avoid indexing this column
AVOID INDEX idx_small_table_id ON small_table (id);

// Poor use of index due to low cardinality
CREATE INDEX idx_gender ON users (gender); // Only male/female values

💡 Best Practices for Index Strategy

• Always `monitor query performance` before adding indexes.
• Use `EXPLAIN PLAN` to understand how queries are executed and where indexes could help.
• Avoid `over-indexing` - Too many indexes can degrade write performance.
• Regularly `maintain indexes` through reindexing and vacuuming.

EXPLAIN SELECT * FROM users WHERE age > 25;

❌ Common Mistakes to Avoid

• Not `testing index impact` on both read and write operations.
• Creating `indexes on frequently updated columns` without considering the trade-offs.
• Assuming that `more indexes always lead to better performance`.

// Bad practice - Too many indexes
CREATE INDEX idx_col1 ON table (col1);
CREATE INDEX idx_col2 ON table (col2);
CREATE INDEX idx_col3 ON table (col3);

💡 Practical Examples of Index Strategy

Let's look at some practical scenarios where indexes can make a significant difference:

-- Scenario 1: Large table with frequent searches
CREATE INDEX idx_user_email ON users (email);

-- Scenario 2: High-concurrency application
AVOID INDEX idx_order_status ON orders (status); // Status changes frequently

📘 Partitioning

Partitioning is a critical technique in SQL performance optimization that allows you to divide large tables into smaller, more manageable pieces. This can significantly improve query performance, reduce locking contention, and simplify maintenance tasks.

💡 Horizontal vs Vertical Partitioning

There are two main types of partitioning in SQL: **horizontal** and **vertical**. Understanding the difference between them is essential for designing efficient database schemas.

• **Horizontal Partitioning**: Divides a table into multiple smaller tables based on specific criteria (e.g., date range, geographic region). Each partition contains rows that meet certain conditions.
• **Vertical Partitioning**: Splits a table into multiple tables by columns. This is often used when some columns are accessed more frequently than others.

💡 Sharding and Federation

Sharding is a form of horizontal partitioning that distributes data across multiple databases or servers. It's commonly used in large-scale applications to improve performance and scalability.

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    amount DECIMAL(10, 2),
    order_date TIMESTAMP
)
PARTITION BY RANGE (order_date);

-- Create partitions for different years
CREATE TABLE orders_2023 PARTITION OF orders 
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE orders_2024 PARTITION OF orders 
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

💡 Key Concepts and Best Practices

• Choose the right partitioning strategy based on your query patterns and data distribution.
• Use **hash partitioning** when you need a balanced distribution of data across partitions.
• Avoid creating too many small partitions, as this can lead to increased overhead.
• Regularly maintain and monitor your partitions to ensure optimal performance.

❌ Common Mistakes to Avoid

• Don't partition without understanding your query patterns.
• Avoid creating empty partitions, as they can cause unnecessary overhead.
• Don't forget to index your partitions properly.

💡 Partitioning and Indexes

Indexes play a crucial role in the performance of partitioned tables. Always create indexes on your partitions to ensure efficient query execution.

CREATE INDEX idx_orders_amount ON orders_2023 (amount);

-- Use index for faster queries
SELECT * FROM orders WHERE amount > 100;

💡 Managing Partitions

Regularly maintain your partitions to ensure optimal performance. This includes monitoring partition sizes, reorganizing data, and dropping old partitions when they're no longer needed.

-- Add new partition for 2025
CREATE TABLE orders_2025 PARTITION OF orders 
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

-- Drop old partition
DROP TABLE orders_2020;

💡 Real-World Applications of Partitioning

Partitioning is widely used in large-scale applications such as e-commerce platforms, social media networks, and financial systems. It's particularly effective for handling time-series data, customer segmentation, and geographic distribution of data.