SQL guide · Tables, views & databases (DDL)

SQL CREATE INDEX: Add an Index to Speed Up Queries

An index is a separate data structure that the database maintains alongside a table so it can find rows without reading every one of them. CREATE INDEX builds that structure on one or more columns. Most engines use a B-tree, which keeps the indexed values sorted and lets the database jump to a matching value in a handful of steps instead of scanning the whole table.

Indexes are worth adding for columns that appear often in WHERE filters, in join conditions (especially foreign key columns) and in ORDER BY or GROUP BY clauses. A query that filters a large orders table by customer_id can go from reading millions of rows to reading a few dozen once that column is indexed. The table data and its results do not change; only the speed of finding rows does.

Indexes are not free. Every INSERT, UPDATE and DELETE has to update each index on the table, and every index uses disk space and memory. Adding an index for every column slows down writes and bloats storage. Index the columns your real queries use, check the query plan to confirm the index is picked, and drop indexes that are never used.

Basic syntax

CREATE INDEX index_name
ON table_name (column1 [ASC | DESC], column2, ...);

index_name must be unique within the table (PostgreSQL and SQLite require it to be unique within the schema). Listing more than one column creates a composite index, where column order matters: the index helps queries that filter on the leading column or columns. ASC and DESC set the sort direction and are mostly useful in composite indexes that match an ORDER BY.

SQL CREATE INDEX examples

Index a single column used in filters

Searching orders by customer is a very common pattern. Without an index the database reads the whole table; with one it can go straight to the matching rows. The index name is chosen by you, and a common convention is idx_table_column.

CREATE INDEX idx_orders_customer_id
ON orders (customer_id);

SELECT order_id, order_date, total
FROM orders
WHERE customer_id = 1042;

Create a composite index on two columns

A composite index covers queries that filter on both columns together, and also queries that filter only on the first column. Put the column you filter by equality first, then the column used for ranges or sorting. Here the index serves a lookup by status that is sorted by date.

CREATE INDEX idx_orders_status_date
ON orders (status, order_date DESC);

SELECT order_id, order_date
FROM orders
WHERE status = 'shipped'
ORDER BY order_date DESC;

Create the index only when it does not exist

In scripts that may run more than once, guard the statement so a repeat run does not fail. PostgreSQL, SQLite and recent MariaDB versions support IF NOT EXISTS directly. This example indexes email addresses for faster login lookups.

CREATE INDEX IF NOT EXISTS idx_customers_email
ON customers (email);

Build an index without blocking writes (PostgreSQL)

A normal index build locks the table against writes while it runs. On a busy production table, PostgreSQL offers CONCURRENTLY, which takes longer but lets inserts and updates continue. It cannot run inside a transaction block.

CREATE INDEX CONCURRENTLY idx_orders_created_at
ON orders (created_at);

Where SQL dialects differ

PostgreSQL: Supports IF NOT EXISTS, CONCURRENTLY, partial indexes with a WHERE clause, expression indexes such as lower(email), and index types beyond B-tree (GIN, GiST, BRIN, hash). Use INCLUDE (col) from version 11 to add non-key columns for index-only scans.

MySQL: Indexes are created with CREATE INDEX or inside CREATE TABLE and ALTER TABLE. IF NOT EXISTS is not supported by MySQL itself (MariaDB does support it). Since version 8.0.13 you can index an expression, and since 8.0 DESC indexes are real descending indexes. InnoDB tables always have a clustered primary key index.

SQL Server: Offers CLUSTERED and NONCLUSTERED indexes, with a maximum of one clustered index per table. Use INCLUDE (col) to add covering columns, a WHERE clause for filtered indexes, and WITH (ONLINE = ON) on Enterprise edition to avoid blocking. IF NOT EXISTS is not available in CREATE INDEX, so check sys.indexes first.

SQLite: Supports CREATE INDEX with IF NOT EXISTS, partial indexes with WHERE, and expression indexes since version 3.9. Index names live in the database-wide namespace, so two tables cannot share an index name.

Oracle: Uses B-tree indexes by default and supports bitmap and function-based indexes. Index names must be unique within the schema. Add ONLINE to allow concurrent DML while the index builds. IF NOT EXISTS is available from Oracle 23ai.

Common mistakes

  • Indexing every column. Each extra index slows down writes and uses storage, so only index columns that real queries filter, join or sort on.
  • Getting composite column order wrong. An index on (status, order_date) does not help a query that filters only on order_date, because the leading column is missing.
  • Wrapping the indexed column in a function. A filter like WHERE LOWER(email) = 'a@b.com' cannot use a plain index on email, so create an expression index or store the value normalised.
  • Indexing low-selectivity columns such as a boolean flag on a large table. The database often ignores the index because it would still have to read a large share of the rows.
  • Assuming the index is used without checking. Run EXPLAIN (or the execution plan in SQL Server) to confirm the optimiser actually chooses it.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator