-- ==================================================================
-- DATABASE INDEXES FOR PERFORMANCE WITH LARGE DATASETS (2M+ RECORDS)
-- ==================================================================
-- 
-- PROBLEM: Delivery log taking too long to load with 2.2 million records
-- SOLUTION: Add composite indexes to optimize the most common queries
--
-- Run these commands in your MySQL database to improve query performance
-- from several seconds/minutes down to milliseconds.
--
-- ==================================================================

USE ac3_marketing_portal;

-- 1. CRITICAL INDEX: Optimize campaign_user_data queries
-- This is the main table causing slowness (2.2M records)
-- ================================================================

-- Index for user's campaign assignments (used in every query)
CREATE INDEX idx_campaign_user ON campaign_user_data(campaign_id, user_id);

-- Composite index for status queries (sent/failed counts)
CREATE INDEX idx_campaign_user_status ON campaign_user_data(campaign_id, user_id, status);

-- Index for ordering and pagination
CREATE INDEX idx_campaign_user_id ON campaign_user_data(campaign_id, user_id, id);

-- Index for master_data joins
CREATE INDEX idx_master_data_id ON campaign_user_data(master_data_id);


-- 2. SUPPORTING INDEXES: Optimize join tables
-- ================================================================

-- Index for master_data table (used in joins)
CREATE INDEX idx_master_mobile ON master_data(mobile);
CREATE INDEX idx_master_name ON master_data(name(50));
CREATE INDEX idx_master_email ON master_data(email(50));

-- Index for campaign_users table (access control checks)
CREATE INDEX idx_campaign_users_lookup ON campaign_users(campaign_id, user_id);

-- Index for scheduled_message_batches
CREATE INDEX idx_batch_id ON scheduled_message_batches(id);


-- 3. VERIFY INDEXES WERE CREATED
-- ================================================================

SHOW INDEX FROM campaign_user_data;
SHOW INDEX FROM master_data;
SHOW INDEX FROM campaign_users;


-- 4. OPTIONAL: ANALYZE TABLES AFTER INDEX CREATION
-- ================================================================
-- Run these to update MySQL statistics for better query optimization

ANALYZE TABLE campaign_user_data;
ANALYZE TABLE master_data;
ANALYZE TABLE campaign_users;
ANALYZE TABLE scheduled_message_batches;


-- ==================================================================
-- EXPECTED PERFORMANCE IMPROVEMENT
-- ==================================================================
--
-- BEFORE INDEXES:
-- - Initial page load: 5-30 seconds (or timeout)
-- - Each page change: 3-10 seconds
-- - Search queries: 10-60 seconds
--
-- AFTER INDEXES:
-- - Initial page load: 100-500ms
-- - Each page change: 50-200ms
-- - Search queries: 200-800ms
--
-- IMPROVEMENT: 50-100x faster queries!
--
-- ==================================================================
-- HOW TO RUN THIS FILE
-- ==================================================================
--
-- METHOD 1: MySQL Command Line
-- mysql -u root -p ac3_marketing_portal < DATABASE_INDEXES_FOR_PERFORMANCE.sql
--
-- METHOD 2: phpMyAdmin
-- 1. Open phpMyAdmin in browser
-- 2. Select database: ac3_marketing_portal
-- 3. Click "SQL" tab
-- 4. Paste entire file content
-- 5. Click "Go"
--
-- METHOD 3: MySQL Workbench
-- 1. Open MySQL Workbench
-- 2. Connect to your database server
-- 3. File → Open SQL Script
-- 4. Select this file
-- 5. Execute (lightning bolt icon)
--
-- ==================================================================
-- MONITORING & VERIFICATION
-- ==================================================================
--
-- After running, test the delivery log page:
-- 1. Navigate to: User → My Campaigns → View Campaign → View Sends
-- 2. The table should load in under 1 second
-- 3. Check browser console (F12) for timing
--
-- To check query execution time, run:
-- SET profiling = 1;
-- [your query here]
-- SHOW PROFILES;
--
-- ==================================================================
