SQL Notes
Learn what SQL indexes are, how they speed up database queries, types of indexes, when to use them, and how they impact INSERT/UPDATE performance.
Imagine a textbook with 500 pages but no table of contents and no index at the back. To find information about "binary search trees," you would have to flip through every single page. That is exactly what a database does without an index — it scans every row in the table to find your data. This is called a full table scan, and for large tables with millions of rows, it is painfully slow.
An index is a special data structure that the database maintains alongside your table. It organizes column values in a way that allows the database to find specific rows without scanning the entire table. Just like a book index tells you "binary search trees: pages 145, 203, 287," a database index tells the engine exactly which rows match your query.
Creating an Index
The basic syntax for creating an index:
CREATE INDEX index_name ON table_name (column_name);Example:
-- Create an index on the Email column
CREATE INDEX idx_emp_email ON Employees (Email);
-- Create an index on the Department column
CREATE INDEX idx_emp_dept ON Employees (Department);
-- Create an index on multiple columns (composite index)
CREATE INDEX idx_emp_dept_salary ON Employees (Department, Salary);After creating these indexes, queries that filter or sort by these columns become significantly faster.
Types of Indexes
Single-Column Index
An index on one column — the most common type:
CREATE INDEX idx_customer_email ON Customers (Email);
CREATE INDEX idx_order_date ON Orders (OrderDate);
CREATE INDEX idx_product_category ON Products (Category);Multi-Column (Composite) Index
An index spanning multiple columns. Useful when queries frequently filter by combinations:
-- Useful for: WHERE Department = 'Engineering' AND Salary > 50000
CREATE INDEX idx_dept_salary ON Employees (Department, Salary);
-- Useful for: WHERE City = 'Mumbai' AND Status = 'Active'
CREATE INDEX idx_city_status ON Customers (City, Status);Column order matters in composite indexes. The index is most effective when queries filter from left to right.
Unique Index
Enforces uniqueness while also providing fast lookups:
CREATE UNIQUE INDEX idx_unique_email ON Users (Email);
CREATE UNIQUE INDEX idx_unique_sku ON Products (SKU);This is similar to a UNIQUE constraint — it prevents duplicate values and speeds up searches.
When Indexes Help
Indexes improve performance for:
-- WHERE clause filtering
SELECT * FROM Orders WHERE CustomerID = 101;
-- JOIN operations
SELECT o.OrderID, c.Name
FROM Orders o JOIN Customers c ON o.CustomerID = c.CustomerID;
-- ORDER BY sorting
SELECT * FROM Products ORDER BY Price DESC;
-- GROUP BY aggregation
SELECT Department, COUNT(*) FROM Employees GROUP BY Department;Real-World Example: E-Commerce Database
Consider an e-commerce platform with millions of products:
-- Products table with no indexes (slow queries)
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
Name VARCHAR(200),
Category VARCHAR(50),
Price DECIMAL(10,2),
Brand VARCHAR(100),
Stock INT,
CreatedAt DATETIME
);Without indexes, these common queries perform full table scans:
-- Customer browsing Electronics category
SELECT * FROM Products WHERE Category = 'Electronics' ORDER BY Price;
-- Searching for a brand
SELECT * FROM Products WHERE Brand = 'Samsung';
-- Finding low-stock items
SELECT * FROM Products WHERE Stock < 10;Add indexes to speed them up:
CREATE INDEX idx_product_category ON Products (Category);
CREATE INDEX idx_product_brand ON Products (Brand);
CREATE INDEX idx_product_stock ON Products (Stock);
CREATE INDEX idx_product_cat_price ON Products (Category, Price);Now each query uses the appropriate index instead of scanning millions of rows.
The Cost of Indexes
Indexes are not free. They come with trade-offs:
Storage Space
Each index takes up disk space. A table with 5 indexes essentially stores extra copies of column data in different organizations.
Slower Writes
Every INSERT, UPDATE, or DELETE must also update all indexes on that table:
-- Without indexes: Just insert into the table
-- With 5 indexes: Insert into table + update 5 index structures
INSERT INTO Products (Name, Category, Price, Brand, Stock)
VALUES ('New Phone', 'Electronics', 25000, 'Samsung', 100);Index Maintenance
As data changes, indexes can become fragmented and need periodic rebuilding.
| Operation | Without Indexes | With Indexes |
|---|---|---|
| SELECT (with WHERE) | Slow (full scan) | Fast (index lookup) |
| INSERT | Fast | Slower (must update indexes) |
| UPDATE | Fast | Slower (must update indexes) |
| DELETE | Fast | Slower (must update indexes) |
When NOT to Use Indexes
Avoid indexes when:
- Tables are small (under 1000 rows) — full scans are fast enough
- Columns have very few unique values — a gender column with only M/F benefits little from indexing
- Tables are heavily written, rarely read — log tables that are written constantly but rarely queried
- You are indexing too many columns — more indexes means slower writes
Viewing Existing Indexes
MySQL
SHOW INDEX FROM Employees;
SHOW INDEX FROM Products;SQL Server
SELECT name, type_desc FROM sys.indexes WHERE object_id = OBJECT_ID('Employees');PostgreSQL
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'employees';Dropping an Index
-- MySQL and PostgreSQL
DROP INDEX idx_emp_email ON Employees;
-- SQL Server
DROP INDEX idx_emp_email ON Employees;
-- PostgreSQL alternative
DROP INDEX idx_emp_email;Real-World Example: Student Database
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(100),
Email VARCHAR(255),
Course VARCHAR(50),
Semester INT,
CGPA DECIMAL(3,2),
City VARCHAR(50)
);
-- Index for searching by course (common filter)
CREATE INDEX idx_student_course ON Students (Course);
-- Index for email lookups (login system)
CREATE UNIQUE INDEX idx_student_email ON Students (Email);
-- Composite index for course + semester queries
CREATE INDEX idx_course_sem ON Students (Course, Semester);
-- Index for city-based filtering
CREATE INDEX idx_student_city ON Students (City);How to Decide What to Index
Follow this decision process:
- Look at your WHERE clauses — columns you filter on frequently should be indexed
- Check your JOIN conditions — foreign key columns benefit from indexes
- Review ORDER BY columns — indexes help avoid sorting operations
- Examine slow queries — use EXPLAIN to see which queries perform full table scans
- Monitor index usage — drop indexes that are never used
Common Mistakes to Avoid
Mistake 1: Indexing every column. More indexes means slower writes. Only index columns that appear in WHERE, JOIN, or ORDER BY.
Mistake 2: Ignoring composite index column order. An index on (A, B) helps queries filtering by A, or by A and B. It does NOT help queries filtering by B alone.
Mistake 3: Not indexing foreign keys. Foreign key columns are used in JOINs constantly. Always index them.
Mistake 4: Creating duplicate indexes. PRIMARY KEY and UNIQUE constraints automatically create indexes. Do not create additional indexes on those columns.
Best Practices
- Always index foreign key columns for faster JOINs
- Use composite indexes for queries that filter on multiple columns together
- Put the most selective column first in composite indexes
- Monitor query performance with EXPLAIN before and after adding indexes
- Remove unused indexes to improve write performance
- Rebuild fragmented indexes periodically for optimal performance
Indexes are the single most powerful tool for improving SQL query performance. Understanding when and how to use them is essential for building applications that remain fast as data grows from thousands to millions of rows.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Introduction to SQL Indexes.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL Complete Guide topic.
Search Terms
sql-complete-guide, sql complete guide, sql, complete, guide, indexing, indexes, introduction to sql indexes
Related SQL Complete Guide Topics