SQL Index

SQL INDEX

An index is a data structure that improves the speed of data retrieval operations on a table. Indexes can make SELECT queries much faster, but they can slightly slow down INSERT, UPDATE, and DELETE because the index must be updated too.

Why Use Indexes?

• Faster searches using WHERE
• Faster sorting using ORDER BY
• Faster joins using JOIN columns
• Better performance for grouping in some cases

Create an Index

CREATE INDEX idx_customers_country
ON customers (country);

Create a UNIQUE Index

A UNIQUE index prevents duplicate values and also improves search performance.

CREATE UNIQUE INDEX idx_customers_email
ON customers (email);

Composite Index (Multiple Columns)

A composite index covers more than one column.

CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date);

When Composite Index Helps

The index above can help queries that filter by customer_id, and also queries that filter by customer_id and order_date together.

Show Indexes (MySQL / MariaDB)

SHOW INDEX FROM customers;

Drop an Index

DROP INDEX idx_customers_country ON customers;

Index Best Practices

• Index columns used often in WHERE, JOIN, and ORDER BY
• Avoid too many indexes (slows down writes)
• Use composite indexes for common multi-column filters
• Index foreign key columns for faster joins

Common Mistakes

• Indexing every column (over-indexing)
• Expecting an index to help queries that start with a wildcard (LIKE '%text')
• Using the wrong column order in composite indexes

Next Step

Continue with SQL Auto Increment to learn how to generate unique ids automatically.