Performance Tuning in SQL: Indexes and Query Optimization

Table of Contents

1. Introduction
2. Why Performance Tuning Matters
3. What Is Query Optimization?
4. What Is an Index in SQL?
5. Types of Indexes
6. How Indexes Work Internally
7. Benefits of Using Indexes
8. When Not to Use Indexes
9. Creating Indexes
10. Composite Indexes
11. Covering Indexes
12. Using EXPLAIN or EXPLAIN PLAN
13. Optimizing SELECT Queries
14. Optimizing Joins
15. Optimizing WHERE Clauses
16. Avoiding SELECT *
17. Using Appropriate Data Types
18. Understanding Query Execution Plans
19. Real-World Indexing Examples
20. Summary and What’s Next

1. Introduction

As your database grows, query performance becomes critical. Poorly written queries and missing indexes can slow down applications. SQL performance tuning ensures that queries run efficiently, reducing load and improving response time.

2. Why Performance Tuning Matters

• Faster query execution
• Better user experience
• Reduced resource consumption
• Increased database scalability
• Lower hardware costs

3. What Is Query Optimization?

Query optimization is the process of analyzing SQL queries and modifying them (or the database) to improve execution speed without changing output results.

4. What Is an Index in SQL?

An index is a data structure that speeds up the retrieval of rows from a table, much like a book index helps you find information quickly without reading the whole book.

5. Types of Indexes

6. How Indexes Work Internally

Most RDBMSs implement indexes using B-trees or hash tables. When a query uses a column with an index, the database searches the index tree rather than scanning the full table.

7. Benefits of Using Indexes

• Speed up searches using WHERE, JOIN, ORDER BY
• Improve sorting performance
• Enable fast lookups for foreign keys
• Help in aggregates and filtering

8. When Not to Use Indexes

• On very small tables — full scan is faster
• On columns with high update/delete frequency — may slow writes
• On columns with low selectivity (e.g., boolean flags)

9. Creating Indexes

sqlCopyEdit-- Single-column index
CREATE INDEX idx_lastname ON employees(last_name);

-- Composite index
CREATE INDEX idx_dept_salary ON employees(department_id, salary);

Always name your indexes meaningfully for maintenance.

10. Composite Indexes

Use when queries filter or sort by multiple columns:

sqlCopyEdit-- Helps with queries using department and salary together
CREATE INDEX idx_dept_salary ON employees(department_id, salary);

Note: Order matters. An index on (department_id, salary) will help with:

• WHERE department_id = ?
• WHERE department_id = ? AND salary = ?

But not with:

• WHERE salary = ? alone

11. Covering Indexes

A covering index includes all columns used in a query:

sqlCopyEdit-- Covers both WHERE and SELECT columns
CREATE INDEX idx_cover ON orders(order_date, status, total);

Avoids accessing the actual table — improves performance dramatically.

12. Using EXPLAIN or EXPLAIN PLAN

Use EXPLAIN to analyze how a query will execute:

sqlCopyEditEXPLAIN SELECT * FROM orders WHERE customer_id = 1001;

Key info to look for:

• type: shows how rows are accessed (e.g., ALL, index, ref)
• key: which index is used
• rows: how many rows are examined

In PostgreSQL:

sqlCopyEditEXPLAIN ANALYZE SELECT * FROM products WHERE price > 100;

13. Optimizing SELECT Queries

• Avoid SELECT * — only select needed columns
• Use proper WHERE filters
• Ensure filter columns are indexed
• Use limits for pagination and test queries

14. Optimizing Joins

• Ensure join keys are indexed
• Use INNER JOIN when possible
• Minimize row counts before joining large tables
• Use explicit JOIN syntax instead of old-style comma joins

15. Optimizing WHERE Clauses

• Use indexed columns in WHERE
• Avoid using functions on columns:

sqlCopyEdit-- BAD: function on column prevents index use
WHERE YEAR(order_date) = 2024

-- GOOD: rewrite using range
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'

• Avoid OR conditions; use UNION or rewrite with IN

16. Avoiding SELECT *

sqlCopyEdit-- BAD
SELECT * FROM users;

-- GOOD
SELECT id, name, email FROM users;

Benefits:

• Reduces memory use
• Improves performance
• Avoids unnecessary data transfer
• Ensures index coverage in some queries

17. Using Appropriate Data Types

• Use smallest possible type (e.g., TINYINT instead of INT)
• Avoid overusing TEXT, BLOB, VARCHAR(MAX)
• Normalize repeated data where necessary
• Ensure consistent types in join/filter conditions

18. Understanding Query Execution Plans

Learn to interpret key parts of EXPLAIN output:

19. Real-World Indexing Examples

Speeding up a dashboard:

sqlCopyEditCREATE INDEX idx_orders_date ON orders(order_date);

Optimizing user login:

sqlCopyEditCREATE INDEX idx_login ON users(email, password_hash);

Accelerating product filters:

sqlCopyEditCREATE INDEX idx_price_stock ON products(price, in_stock);

20. Summary and What’s Next

SQL performance tuning through indexes and query optimization is essential for building fast, scalable applications. Mastering indexing, writing efficient queries, and using tools like EXPLAIN allows you to reduce latency and improve the overall health of your database.