Indexes in SQL: CREATE INDEX

An index works like the index of a book: instead of reading every page to find a topic, you look it up in the index and go straight to the page. Without indexes, to answer WHERE customerId = 4 the database has to read the whole Orders table row by row. With an index on customerId, it goes straight to that customer’s rows.

With eight orders you won’t notice the difference. With eight million, it’s the difference between several seconds and a few milliseconds.

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX indexName
ON table (column1 [ASC | DESC], column2, ...);

Creating an index

The shop’s queries very often filter and join on customerId in the Orders table. It’s the perfect candidate:

The name is up to you, but a naming convention helps: ix_ (for index) followed by the table and the columns.

Once it’s created there’s nothing else to do: you never mention the index in your queries. The optimizer decides on its own whether to use it, and from then on keeps it up to date on every INSERT, UPDATE and DELETE.

Clustered and nonclustered

SQL Server has two kinds of index, and the difference matters:

When we defined customerId as PRIMARY KEY in the CREATE TABLE lesson, SQL Server created a clustered index on that column: that’s why looking up a customer by id was already fast.

-- SQL Server only: explicit type
CREATE NONCLUSTERED INDEX ix_orders_orderDate
ON Orders (orderDate DESC);

Unique indexes

A UNIQUE index, besides speeding up lookups, prevents duplicate values. Creating one on a column that already has duplicates fails. The Orders table has two products described as 'Alexa Speaker':

On Customers (lastName), however, it can be created, because no two customers share a last name. From then on, any INSERT that repeats a last name fails. Run the block and you’ll see the error from the second step:

A UNIQUE constraint in CREATE TABLE and a UNIQUE index do the same; in fact, SQL Server implements the constraint by creating an index. The constraint makes the intention clearer in the table design.

Composite indexes

An index can include several columns. Order matters: an index on (customerId, orderDate) helps when searching by customer, or by customer and date, but not when searching by date alone, just as a phone book sorted by last name and first name is no help for finding everyone called Anna.

Rule of thumb: put first the column you filter on most with equality (=), then the one used for ranges or sorting.

Included columns

In SQL Server (and in PostgreSQL since version 11) you can add columns that aren’t part of the key but are stored in the index with INCLUDE. If a query only asks for columns that are in the index, the engine doesn’t even need to touch the table:

CREATE INDEX ix_orders_customer_includes
ON Orders (customerId)
INCLUDE (product, totalPrice);

When not to create an index

Indexes aren’t free:

• They take up space, sometimes as much as the table itself.
• They slow down writes: every INSERT, UPDATE or DELETE also has to update every affected index.
• They add nothing on small tables: reading a hundred rows is faster than going through an index.
• On columns with few distinct values (a yes/no field, for example) the optimizer usually ignores them.

And some queries can’t use an index even when it exists. Applying a function to the indexed column, as in WHERE YEAR(orderDate) = 2022, forces the engine to compute it on every row. The version that can use the index is WHERE orderDate >= '2022-01-01' AND orderDate < '2023-01-01'.

Dropping an index

DROP INDEX ix_orders_customerId ON Orders;   -- SQL Server
DROP INDEX ix_orders_customerId;             -- PostgreSQL

Practice

1. Create an index on Customers (lastName, name).
2. Would that index help the query WHERE name = 'Peter'? Why?
3. Rewrite WHERE YEAR(orderDate) = 2022 AND MONTH(orderDate) = 9 so it can use an index on orderDate.