mirror of
https://github.com/pacnpal/thrilltrack-explorer.git
synced 2025-12-20 04:51:11 -05:00
94 lines
3.5 KiB
SQL
94 lines
3.5 KiB
SQL
-- P0 #7: Performance Optimization - Database Indexes
|
|
-- Creates indexes on confirmed high-frequency tables only
|
|
-- Focuses on moderation queue performance (most critical)
|
|
|
|
-- ============================================================================
|
|
-- CONTENT SUBMISSIONS (CRITICAL - Moderation Queue Performance)
|
|
-- ============================================================================
|
|
|
|
-- Moderation queue sorting (most critical for performance)
|
|
CREATE INDEX IF NOT EXISTS idx_submissions_queue
|
|
ON content_submissions(status, created_at DESC)
|
|
WHERE status IN ('pending', 'flagged');
|
|
|
|
-- Lock management queries
|
|
CREATE INDEX IF NOT EXISTS idx_submissions_locks
|
|
ON content_submissions(assigned_to, locked_until)
|
|
WHERE locked_until IS NOT NULL;
|
|
|
|
-- Moderator workload queries
|
|
CREATE INDEX IF NOT EXISTS idx_submissions_reviewer
|
|
ON content_submissions(reviewer_id, status, reviewed_at DESC)
|
|
WHERE reviewer_id IS NOT NULL;
|
|
|
|
-- Submission type filtering
|
|
CREATE INDEX IF NOT EXISTS idx_submissions_type_status
|
|
ON content_submissions(submission_type, status, created_at DESC);
|
|
|
|
-- User submissions lookup
|
|
CREATE INDEX IF NOT EXISTS idx_submissions_user
|
|
ON content_submissions(user_id, created_at DESC);
|
|
|
|
-- ============================================================================
|
|
-- SUBMISSION ITEMS (Dependency Resolution)
|
|
-- ============================================================================
|
|
|
|
-- Submission item lookups and status tracking
|
|
CREATE INDEX IF NOT EXISTS idx_submission_items_submission
|
|
ON submission_items(submission_id, status, order_index);
|
|
|
|
-- Dependency chain resolution
|
|
CREATE INDEX IF NOT EXISTS idx_submission_items_depends
|
|
ON submission_items(depends_on)
|
|
WHERE depends_on IS NOT NULL;
|
|
|
|
-- Item type filtering
|
|
CREATE INDEX IF NOT EXISTS idx_submission_items_type
|
|
ON submission_items(item_type, status);
|
|
|
|
-- ============================================================================
|
|
-- USER PROFILES (Profile Lookups)
|
|
-- ============================================================================
|
|
|
|
-- Username lookups (case-insensitive for search)
|
|
CREATE INDEX IF NOT EXISTS idx_profiles_username_lower
|
|
ON profiles(LOWER(username));
|
|
|
|
-- User ID lookup
|
|
CREATE INDEX IF NOT EXISTS idx_profiles_user_id
|
|
ON profiles(user_id);
|
|
|
|
-- ============================================================================
|
|
-- MODERATION AUDIT LOG (Admin Queries)
|
|
-- ============================================================================
|
|
|
|
-- Audit log by moderator
|
|
CREATE INDEX IF NOT EXISTS idx_audit_log_moderator
|
|
ON moderation_audit_log(moderator_id, created_at DESC);
|
|
|
|
-- Audit log by submission
|
|
CREATE INDEX IF NOT EXISTS idx_audit_log_submission
|
|
ON moderation_audit_log(submission_id, created_at DESC)
|
|
WHERE submission_id IS NOT NULL;
|
|
|
|
-- Audit log by action type
|
|
CREATE INDEX IF NOT EXISTS idx_audit_log_action
|
|
ON moderation_audit_log(action, created_at DESC);
|
|
|
|
-- ============================================================================
|
|
-- CONTACT SUBMISSIONS (Contact Form Performance)
|
|
-- ============================================================================
|
|
|
|
-- Contact queue sorting
|
|
CREATE INDEX IF NOT EXISTS idx_contact_status_created
|
|
ON contact_submissions(status, created_at DESC);
|
|
|
|
-- User contact history
|
|
CREATE INDEX IF NOT EXISTS idx_contact_user
|
|
ON contact_submissions(user_id, created_at DESC)
|
|
WHERE user_id IS NOT NULL;
|
|
|
|
-- Assigned tickets
|
|
CREATE INDEX IF NOT EXISTS idx_contact_assigned
|
|
ON contact_submissions(assigned_to, status)
|
|
WHERE assigned_to IS NOT NULL; |