Understanding SQL Indexing

SQL indexing is one of the most important tools for improving database performance. Indexes allow the SQL engine to quickly locate rows without scanning the entire table, making queries significantly faster especially as data size grows.

---

What Is an SQL Index?

An SQL index is a data structure—commonly a B-tree—that helps the database engine find data efficiently. Without an index, the database must perform a full table scan to locate matching rows.

Indexes are especially useful for columns commonly used in:

• WHERE conditions
• JOIN operations
• ORDER BY sorting
• GROUP BY

---

Types of SQL Indexes

1. Single-Column Index

Creates an index on one column.

CREATE INDEX idx_customer ON sales_invoice (customer);

2. Composite (Multi-Column) Index

Indexes multiple columns in a specific order.

CREATE INDEX idx_date_customer ON sales_invoice (posting_date, customer);

Note: Composite indexes are most effective when queries filter using the left-most column(s).

3. Unique Index

Ensures values in the indexed column(s) are unique.

CREATE UNIQUE INDEX idx_unique_email ON users (email);

4. Fulltext Index

Used for searching text content efficiently.

CREATE FULLTEXT INDEX idx_content_search ON articles (content);

---

How SQL Indexes Improve Performance

Indexes speed up read operations such as SELECT queries by reducing the amount of data the database must scan. However, they also introduce overhead during INSERT, UPDATE, and DELETE operations because the index must be updated.

• Faster SELECT performance
• Slightly slower writes due to index maintenance
• Increased storage usage

---

When to Add an Index

Add an index when a column is frequently used for:

• Filtering (WHERE)
• Sorting (ORDER BY)
• Joining tables
• Ensuring uniqueness

Avoid indexing:

• Columns with low selectivity (e.g., boolean values)
• Columns rarely used in queries
• Large text fields unless using FULLTEXT indexes

---

Viewing Existing Indexes

MySQL / MariaDB

SHOW INDEXES FROM sales_invoice;

PostgreSQL

SELECT indexname, indexdef 
FROM pg_indexes 
WHERE tablename = 'sales_invoice';

---

Removing an Index

MySQL / MariaDB

DROP INDEX idx_customer ON sales_invoice;

PostgreSQL

DROP INDEX idx_customer;

---

Example: Speeding Up a Query

Slow Query (no index)

SELECT * FROM orders
WHERE customer_id = 'CUST-0001'
AND order_date BETWEEN '2025-01-01' AND '2025-02-01';

Improvement with Composite Index

CREATE INDEX idx_customer_date 
ON orders (customer_id, order_date);

This index dramatically improves performance by allowing the SQL engine to locate matching rows using the indexed pair.

---

Best Practices

• Index columns used frequently in WHERE and JOIN clauses.
• Use composite indexes for multi-column filtering—but follow the left-most rule.
• Do not over-index; too many indexes slow down write operations.
• Avoid indexing low-selectivity fields.
• Periodically check for unused or duplicate indexes.

---