Builds an index on a table to improve query performance for lookups, joins, and ordering. The query planner automatically uses indexes when they can speed up a query — you do not need to reference an index explicitly in SQL statements.
Syntax
CREATE [UNIQUE] INDEX [IF NOT EXISTS] [schema_name.]index_name
ON table_name (column_or_expr [ASC | DESC] [, ...])
[WHERE filter_expression];
Custom index method (Turso extension)
CREATE INDEX index_name ON table_name USING method_name (columns...);
Parameters
| Parameter | Description |
|---|
UNIQUE | Enforces a uniqueness constraint. Turso rejects any INSERT or UPDATE that would create duplicate values in the indexed columns. |
IF NOT EXISTS | Prevents an error if an index with the same name already exists. The statement is a no-op when the index is present. |
schema_name | The name of the attached database containing the table. Defaults to the main database if omitted. |
index_name | A unique name for the index within the database. |
table_name | The table to index. |
column_or_expr | A column name or expression to include in the index. Multiple entries are separated by commas. |
ASC / DESC | Sort direction for the indexed column. Default is ASC. |
WHERE filter_expression | An optional filter that creates a partial index. Only rows matching the expression are included. |
Column indexes
The most common form indexes one or more columns by name.
-- Single-column index
CREATE INDEX idx_users_email ON users (email);
-- Multi-column (composite) index
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date DESC);
(a, b) can accelerate queries filtering on a alone, but not queries filtering only on b.
UNIQUE indexes
A UNIQUE index enforces that no two rows contain the same combination of values in the indexed columns. NULL values are considered distinct from each other, so a UNIQUE index permits multiple rows with NULL in the indexed columns.
CREATE UNIQUE INDEX idx_users_email_unique ON users (email);
-- This succeeds:
INSERT INTO users (email) VALUES ('alice@example.com');
-- This fails with a UNIQUE constraint violation:
INSERT INTO users (email) VALUES ('alice@example.com');
Partial indexes
A partial index includes only rows that satisfy the WHERE clause. Partial indexes are smaller than full indexes and are more efficient for queries that always include the same filter condition.
-- Index only active orders
CREATE INDEX idx_active_orders ON orders (customer_id)
WHERE status = 'active';
-- The query planner uses this index when the WHERE clause matches
SELECT * FROM orders WHERE status = 'active' AND customer_id = 42;
WHERE clause of a partial index can reference any column of the table and may use operators, literal values, and built-in functions. Subqueries are not allowed.
Expression indexes
An expression index stores the result of an expression rather than a raw column value. Use expression indexes when queries frequently filter or sort by a computed value.
-- Index on lowercase email for case-insensitive lookups
CREATE INDEX idx_users_email_lower ON users (lower(email));
-- The query planner can use this index for:
SELECT * FROM users WHERE lower(email) = 'alice@example.com';
-- Index on an arithmetic expression
CREATE INDEX idx_items_total ON items (quantity * unit_price);
Full-text search indexes
Turso extension: Custom index methods extend indexing beyond B-trees. This feature is experimental and must be enabled before use.
USING clause to specify an alternative index method. The fts method creates a full-text search index powered by Tantivy and supports tokenizer configuration through the WITH clause.
-- Basic FTS index
CREATE INDEX idx_articles_search ON articles USING fts (title, body);
-- FTS index with custom tokenizers
CREATE INDEX idx_articles_search ON articles USING fts (
title WITH tokenizer=simple,
body WITH tokenizer=ngram
);
search() function to query it:
SELECT * FROM articles WHERE search(articles, 'database performance');
Examples
Index for a common lookup pattern
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
category TEXT,
price REAL,
in_stock INTEGER DEFAULT 1
);
-- Speed up lookups by category
CREATE INDEX idx_products_category ON products (category);
-- Speed up price range queries within a category
CREATE INDEX idx_products_cat_price ON products (category, price);
Unique index to enforce a business rule
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
email TEXT NOT NULL,
department TEXT
);
CREATE UNIQUE INDEX idx_employees_email ON employees (email);
Partial index for a status filter
CREATE TABLE tasks (
id INTEGER PRIMARY KEY,
assignee TEXT,
status TEXT DEFAULT 'pending',
due_date TEXT
);
-- Only index pending tasks — completed tasks are rarely queried
CREATE INDEX idx_pending_tasks ON tasks (assignee, due_date)
WHERE status = 'pending';
IF NOT EXISTS
CREATE INDEX IF NOT EXISTS idx_orders_customer ON orders (customer_id);
See also