High-leverage index patterns for subsets of data and computed predicates across PostgreSQL, MySQL, and SQLite.
Advanced patterns that often beat naive full-table indexes.
Index the normalized expression you actually query.
CREATE INDEX idx_users_lower_email ON users ((lower(email)));
SELECT * FROM users WHERE lower(email) = lower('a@example.com');If the predicate calls a function, a plain index on the raw column may not help enough.
CREATE INDEX idx_tickets_open_priority_created_at
ON tickets (priority, created_at DESC)
WHERE closed_at IS NULL;Great for dashboards or queues that mostly show open work items.
Make a JSON path searchable with an index.
ALTER TABLE events
ADD COLUMN event_type VARCHAR(64) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(payload, '$.type'))) STORED,
ADD INDEX idx_events_event_type (event_type);Generated columns are a common way to index computed values in MySQL.
Shrink the index to the rows that matter most.
CREATE INDEX idx_subscriptions_active_renewal_date
ON subscriptions (renewal_date)
WHERE canceled_at IS NULL;This is especially attractive in lightweight local databases where space matters.