Partial, Expression, and Functional Indexes

CREATE INDEX idx_users_lower_email ON users ((lower(email)));

SELECT * FROM users WHERE lower(email) = lower('a@example.com');

# Index the normalized expression you actually query.

CREATE INDEX idx_tickets_open_priority_created_at
ON tickets (priority, created_at DESC)
WHERE closed_at IS NULL;

# Keep the hot subset small and efficient.

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

# Make a JSON path searchable with an index.

CREATE INDEX idx_subscriptions_active_renewal_date
ON subscriptions (renewal_date)
WHERE canceled_at IS NULL;

# Shrink the index to the rows that matter most.