Performance Testing After Migration

-- Benchmark queries to run BEFORE and AFTER migration

-- Query 1: Customer lookup by email (most common operation)
-- Before: ~5ms expected
SELECT * FROM customers WHERE email = 'rajesh@example.com';

-- Query 2: Account balance with customer details
-- Before: ~10ms expected
SELECT c.name, a.account_number, a.balance
FROM customers c
JOIN accounts a ON c.customer_id = a.customer_id
WHERE c.customer_id = 101;

-- Query 3: Transaction history (paginated)
-- Before: ~25ms expected
SELECT t.txn_date, t.amount, t.type, t.description
FROM transactions t
WHERE t.account_id = 'ACC001'
ORDER BY t.txn_date DESC
LIMIT 20;

-- Query 4: Monthly summary report (heavy aggregation)
-- Before: ~200ms expected
SELECT
  DATE_FORMAT(txn_date, '%Y-%m') AS month,
  COUNT(*) AS txn_count,
  SUM(CASE WHEN type = 'CREDIT' THEN amount ELSE 0 END) AS total_credits,
  SUM(CASE WHEN type = 'DEBIT' THEN amount ELSE 0 END) AS total_debits
FROM transactions
WHERE txn_date >= '2024-01-01'
GROUP BY DATE_FORMAT(txn_date, '%Y-%m')
ORDER BY month;

-- List all indexes in target database
SELECT
  TABLE_NAME,
  INDEX_NAME,
  GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS columns,
  INDEX_TYPE,
  NON_UNIQUE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'target_db'
GROUP BY TABLE_NAME, INDEX_NAME, INDEX_TYPE, NON_UNIQUE
ORDER BY TABLE_NAME;

-- Compare index count: source vs target
SELECT 'source' AS db,
       TABLE_NAME,
       COUNT(DISTINCT INDEX_NAME) AS index_count
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'source_db'
GROUP BY TABLE_NAME

UNION ALL

SELECT 'target',
       TABLE_NAME,
       COUNT(DISTINCT INDEX_NAME)
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'target_db'
GROUP BY TABLE_NAME
ORDER BY TABLE_NAME, db;

-- Check for missing indexes on foreign key columns
SELECT
  kcu.TABLE_NAME,
  kcu.COLUMN_NAME AS fk_column,
  CASE WHEN s.INDEX_NAME IS NULL
    THEN 'MISSING INDEX' ELSE s.INDEX_NAME END AS index_status
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
LEFT JOIN INFORMATION_SCHEMA.STATISTICS s
  ON kcu.TABLE_SCHEMA = s.TABLE_SCHEMA
  AND kcu.TABLE_NAME = s.TABLE_NAME
  AND kcu.COLUMN_NAME = s.COLUMN_NAME
WHERE kcu.TABLE_SCHEMA = 'target_db'
  AND kcu.REFERENCED_TABLE_NAME IS NOT NULL
  AND s.INDEX_NAME IS NULL;

-- Use EXPLAIN to check if indexes are being used
EXPLAIN SELECT * FROM customers WHERE email = 'rajesh@example.com';
-- Look for: type = ref (index used) vs type = ALL (full table scan)

EXPLAIN SELECT c.first_name, a.balance
FROM customers c
JOIN accounts a ON c.customer_id = a.customer_id
WHERE c.email = 'rajesh@example.com';
-- Look for: both tables should use indexes, not full scans

-- After identifying missing indexes, create them
-- CREATE INDEX idx_customers_email ON customers(email);
-- CREATE INDEX idx_accounts_user_id ON accounts(user_id);
-- CREATE INDEX idx_transactions_account_date
--   ON transactions(account_id, txn_date);