SQL Index: Syntax, Usage, and Examples

An SQL index is a database structure that improves query performance by enabling faster data retrieval. Instead of scanning every row in a table, the database engine uses an index to locate records quickly. This makes indexes essential for optimizing SQL queries, especially when working with large datasets.

How to Use an SQL Index

An index is created using the CREATE INDEX statement. It can be applied to a single column or multiple columns. The right indexing strategy can dramatically improve query speed.

Creating an Index

To create a basic index on a column:

CREATE INDEX idx_column
ON table_name (column_name);

For multiple columns, use a composite index:

CREATE INDEX idx_multiple
ON table_name (column1, column2);

To prevent duplicate values, create a unique index:

CREATE UNIQUE INDEX idx_unique
ON table_name (column_name);

Indexes can also be created when defining a table:

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE
);

Dropping an Index

If an index is no longer needed, you can remove it:

DROP INDEX idx_column;

In SQL Server, specify the table:

DROP INDEX table_name.idx_column;

Checking Existing Indexes

To see the indexes on a table, use:

SHOW INDEX FROM table_name;

This helps verify whether the database is using indexes efficiently.

When to Use an SQL Index

Indexes improve performance, but they aren't always necessary. They work best when:

Speeding Up SELECT Queries

Whenever you use WHERE, ORDER BY, or JOIN, an index makes lookups significantly faster.

SELECT * FROM users WHERE email = 'user@example.com';

Without an index on email, this query scans the entire users table. With an index, the database finds the record instantly.

Reducing Table Scans

Without an index, the database engine must check every row to find matching results. This is slow for large tables. Indexes enable direct lookups, reducing the workload.

Enforcing Uniqueness

A unique index ensures no duplicate values exist in a column, preventing data integrity issues. This is commonly used for emails, usernames, and other unique identifiers.

Examples of SQL Indexes

Indexing a Single Column

To speed up searches on a users table:

CREATE INDEX idx_email
ON users (email);

Now, queries like this execute faster:

SELECT * FROM users WHERE email = 'jane.doe@example.com';

Indexing Multiple Columns

A composite index is useful when filtering by more than one column:

CREATE INDEX idx_order_customer
ON orders (customer_id, order_date);

This benefits queries that filter by both columns:

SELECT * FROM orders
WHERE customer_id = 10 AND order_date > '2024-01-01';

Unique Index for Data Integrity

To prevent duplicate values in the username column:

CREATE UNIQUE INDEX idx_username
ON users (username);

Indexing a Foreign Key

Foreign keys often benefit from indexing since they’re used in joins:

CREATE INDEX idx_order_user
ON orders (user_id);

This improves performance when retrieving orders for a specific user:

SELECT * FROM orders WHERE user_id = 42;

Dropping an Index

To remove an index that’s no longer needed:

DROP INDEX idx_order_user;

Learn More About SQL Indexes

Clustered vs. Non-Clustered Indexes

Indexes come in two main types:

• Clustered Index – Determines the physical order of data in a table. Each table can have only one clustered index.
• Non-Clustered Index – Stores a separate structure for lookups without affecting table order. A table can have multiple non-clustered indexes.

Creating a clustered index:

CREATE CLUSTERED INDEX idx_order_id
ON orders (order_id);

Creating a non-clustered index:

CREATE NONCLUSTERED INDEX idx_lastname
ON employees (last_name);

Indexing and Query Optimization

Indexes shine in queries with large datasets. To check if an index is being used, run:

EXPLAIN SELECT * FROM users WHERE email = 'jane.doe@example.com';

This shows whether the database engine is using an index or scanning the entire table.

Trade-offs of Using Indexes

Indexes speed up SELECT queries but can slow down INSERT, UPDATE, and DELETE operations. Whenever data is modified, indexes must also be updated, which adds overhead.

Use indexes wisely, focusing on frequently queried columns while avoiding excessive indexing that could slow down data changes.

Partial Indexes

Some databases, like PostgreSQL, allow partial indexes to store only certain rows, reducing storage overhead.

CREATE INDEX idx_active_users
ON users (email)
WHERE is_active = TRUE;

This is useful when queries primarily target active users.

Full-Text Indexes

For searching text fields, full-text indexes improve speed and accuracy:

CREATE FULLTEXT INDEX idx_description
ON products (description);

This makes searching text-heavy columns faster than using LIKE.

Covering Indexes

A covering index includes all columns required for a query, eliminating the need to access the actual table.

CREATE INDEX idx_customer_orders
ON orders (customer_id, order_date, total_amount);

Now, queries like this run faster:

SELECT customer_id, order_date, total_amount
FROM orders WHERE customer_id = 5;

Indexes are powerful tools that enhance SQL performance when used correctly. They help speed up lookups, enforce uniqueness, and optimize complex queries. However, they also come with trade-offs, as they add storage and maintenance overhead.