CREATE [UNIQUE] INDEX [IF NOT EXISTS] [schema-name.]index-name
    ON table-name (column-or-expr [COLLATE collation-name] [ASC|DESC], ...)
    [WHERE filter-expression];

-- 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);

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');

-- 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;

-- 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);

CREATE INDEX index-name ON table-name USING method-name (columns...);

-- 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
);

SELECT * FROM articles WHERE search(articles, 'database performance');

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);

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    department TEXT
);

CREATE UNIQUE INDEX idx_employees_email ON employees (email);

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';