SQL Indexes

What is an Index?

An index in SQL is a performance-optimization feature that allows the database to find rows faster, without scanning the entire table. It works like an index in a book — instead of flipping through every page, you can jump directly to the page number listed in the index.

Indexes are created on one or more columns of a table and store a sorted representation of the column values, along with pointers to the actual data rows.

Why are indexes used?

To speed up SELECT queries, especially when using WHERE, JOIN, ORDER BY, and GROUP BY clauses
To enforce uniqueness (e.g., with PRIMARY KEY or UNIQUE constraints)
To optimize filtering and sorting of large datasets
To reduce query execution time and improve database scalability

However, indexes come with trade-offs: they consume extra storage and slightly slow down INSERT, UPDATE, and DELETE operations because the index must be updated as well.

Creating an Index

CREATE INDEX idx_name
ON customers (last_name);

This creates an index on the last_name column of the customers table.

Unique Index

CREATE UNIQUE INDEX idx_email
ON users (email);

This ensures that each email in the users table is unique.

Composite Index

CREATE INDEX idx_name_city
ON customers (last_name, city);

Creates an index using both last_name and city.

Dropping an Index

DROP INDEX idx_email;

Removes the index named idx_email.

When to Use Indexes

Use on columns in WHERE, JOIN, and ORDER BY clauses
Avoid on frequently updated or inserted columns in high-write environments
Ideal for large tables with many reads

Best Practices

Name indexes clearly (e.g., idx_table_column)
Test performance before and after adding indexes
Use tools like EXPLAIN to understand index usage

Need Help?

Ask the AI if you need help understanding or want to dive deeper in any topic

Menu