SQL - Create Index

SQL Indexes: Accelerating Your Queries

An index in a database is a data structure that improves the speed of data retrieval operations on a table. Think of it like the index at the back of a book: instead of scanning every page to find a topic, you can look it up in the index and go directly to the right page. Similarly, a database index allows the query engine to find the rows that match your criteria quickly, without scanning the entire table.

The Trade-off: Faster Reads vs. Slower Writes

While indexes dramatically speed up SELECT queries, they come at a cost:

• Slower Write Operations: Every time you INSERT, UPDATE, or DELETE a row, the database must not only modify the table data but also update every index that includes that row. This adds overhead.
• Storage Space: Indexes are separate objects on disk and consume storage space.

The key to effective database performance is strategic indexing: creating indexes on the right columns to optimize common queries without unnecessarily slowing down write operations.

Creating an Index

The CREATE INDEX statement is used to create a new index on one or more columns of a table.

Syntax

CREATE INDEX index_name ON table_name (column_name1, column_name2, ...);

Example: Indexing a Foreign Key

Let’s consider customers and orders tables. Queries that join these tables on customer_id are very common. Indexing the customer_id column in the orders table is a classic optimization.

CREATE TABLE customers (
    id INT PRIMARY KEY,
    email VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE NOT NULL,
    amount DECIMAL(10, 2),
    customer_id INT REFERENCES customers(id)
);

-- Create an index on the foreign key column for faster joins
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

Pro Tip: Most database systems automatically create an index on PRIMARY KEY and UNIQUE constraints. It’s often beneficial to manually create indexes on foreign key columns.

Types of Indexes: A Brief Overview

• Single-Column Index: The basic index shown above, created on a single column.
• Composite (Multi-Column) Index: An index created on two or more columns. The order of columns in a composite index is crucial. An index on (last_name, first_name) can efficiently serve queries filtering on last_name or on last_name AND first_name, but it’s not as helpful for queries filtering only on first_name.
• Unique Index: Enforces that all values in the indexed column(s) must be unique. This is both a performance enhancement and a data integrity rule.
• Other Types: Modern databases offer specialized indexes like Full-Text (for searching text), GiST/SP-GiST (for geometric or complex data types in PostgreSQL), and Hash indexes (for exact equality checks).

Example: A Composite Index

If you frequently search for customers by their city and status, a composite index would be effective.

CREATE INDEX idx_customers_city_status ON customers (city, status);

Viewing and Managing Indexes

The command to list the indexes on a table is specific to each database system.

• MySQL: SHOW INDEX FROM table_name;
• PostgreSQL: In the psql command-line tool, use \d table_name.
• SQL Server: EXEC sp_helpindex 'table_name';

To remove an index that is no longer needed or is harming performance, use the DROP INDEX statement.

-- Syntax is generally standard across systems
DROP INDEX index_name;

Best Practices

• Index what you filter: Create indexes on columns that appear frequently in WHERE clauses and JOIN conditions.
• Don’t over-index: Avoid creating indexes on every column. Each index adds overhead to write operations.
• Use EXPLAIN: The EXPLAIN (or EXPLAIN ANALYZE) command is your best friend. It shows the query plan the database will use, allowing you to see if your indexes are being used effectively.
• Consider covering indexes: If a query’s SELECT, WHERE, and JOIN clauses all use columns that are part of a single index, the database can answer the query using only the index, which is extremely fast. This is called a covering index.