-- ============================================
-- ADDITIONAL INDEXES - Apply on Production
-- (You already have the base indexes, these complete the optimization)
-- ============================================

-- 1. Odds validation index (speeds up the EXISTS check with odd filters)
ALTER TABLE odds 
ADD INDEX idx_odds_valid (fixture_id, odd, market_id);

-- 2. Market name lookup (speeds up JOIN with soccer_markets table)
ALTER TABLE soccer_markets 
ADD INDEX idx_market_name (name);

-- 3. League composite (speeds up country filtering + ordering)
ALTER TABLE soccer_leagues 
ADD INDEX idx_country_name (country, name);

-- ============================================
-- VERIFY NEW INDEXES
-- ============================================
SELECT 
    INDEX_NAME,
    GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) as COLUMNS
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
  AND INDEX_NAME IN ('idx_odds_valid', 'idx_market_name', 'idx_country_name')
GROUP BY INDEX_NAME;

-- ============================================
-- ANALYZE TABLES (helps query optimizer)
-- ============================================
ANALYZE TABLE fixtures;
ANALYZE TABLE odds;
ANALYZE TABLE soccer_markets;
ANALYZE TABLE soccer_leagues;

-- Done! These 3 additional indexes complete the optimization.
