20 lines
880 B
MySQL
20 lines
880 B
MySQL
|
-- Add performance indexes for user queries
|
||
|
|
-- Index for email lookup (already exists as unique, but let's ensure it's there)
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
|
||
|
|
|
||
|
|
-- Index for name searches
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_users_name ON users(name);
|
||
|
|
|
||
|
|
-- Index for active status filtering
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_users_is_active ON users(is_active);
|
||
|
|
|
||
|
|
-- Composite index for common query patterns
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_users_active_name ON users(is_active, name);
|
||
|
|
|
||
|
|
-- Index for created_at for sorting
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_users_created_at ON users(created_at DESC);
|
||
|
|
|
||
|
|
-- GIN index for full-text search on name and email
|
||
|
|
CREATE EXTENSION IF NOT EXISTS pg_trgm;
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_users_name_trgm ON users USING gin (name gin_trgm_ops);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_users_email_trgm ON users USING gin (email gin_trgm_ops);
|