Create Index

CREATE INDEX adds a non-unique index to one or more columns.

Use it when read performance on specific query patterns matters.

Core Concepts

flowchart TD
  A[Identify frequent query filter] --> B[Choose target column]
  B --> C[CREATE INDEX statement]
  C --> D[Test query performance behavior]

Step	Example
Pick target	orders.customer_id
Name index clearly	idx_orders_customer_id
Create index	CREATE INDEX ...

Code Examples

-- Setup table for index creation.
CREATE TABLE orders_idx (
  order_id     INTEGER PRIMARY KEY,
  customer_id  INTEGER NOT NULL,
  total        REAL NOT NULL
);

INSERT INTO orders_idx (customer_id, total)
VALUES (1, 40.0), (1, 60.0), (2, 90.0), (3, 20.0);

Expected output
Table created and sample rows inserted.

-- Create index on customer_id for customer-based lookups.
CREATE INDEX idx_orders_customer_id
ON orders_idx(customer_id);

Expected output
Index idx_orders_customer_id created.

-- Verify index exists.
PRAGMA index_list(orders_idx);

seq	name	unique	origin	partial
0	idx_orders_customer_id	0	c	0

SQLite-Specific Nuances

SQLite Nuance

Index names are schema objects in SQLite; choose explicit names to simplify maintenance and migrations.

Common Pitfalls / Best Practices

Pitfall

Creating indexes without confirming real queries use those columns.

Best Practice

Name indexes with a stable convention like idx_<table>_<column>.

Quick Challenge

Create an index on orders_idx(total) named idx_orders_total.

View Solution

CREATE INDEX idx_orders_total
ON orders_idx(total);

Expected output
New index appears in PRAGMA index_list(orders_idx).