-- CRITICAL PRODUCTION FIX
-- Apply this index immediately to fix the odds query timeout

-- This composite index covers ALL columns used in the odds subquery
-- It will dramatically improve the performance of the complex odds fetching query

-- NOTE: If you get "Duplicate key name" errors, the indexes already exist (safe to ignore)

-- Critical composite index for the subquery
ALTER TABLE odds 
ADD INDEX idx_fixture_market_label_bookmaker (fixture_id, market_id, label, bookmaker_id, odd);

-- Additional helpful index for the outer query
ALTER TABLE odds 
ADD INDEX idx_fixture_bookmaker_odd (fixture_id, bookmaker_id, odd);

-- Verify the indexes were created
SHOW INDEX FROM odds WHERE Key_name IN ('idx_fixture_market_label_bookmaker', 'idx_fixture_bookmaker_odd');

-- Test query performance (should be MUCH faster now)
EXPLAIN
SELECT 
    o1.fixture_id,
    o1.market_id,
    o1.label,
    o1.odd,
    o1.bookmaker_id
FROM odds o1
INNER JOIN (
    SELECT 
        fixture_id,
        market_id,
        label,
        MIN(CASE WHEN bookmaker_id = 8 THEN 0 ELSE 1 END) AS best_priority
    FROM odds
    WHERE fixture_id IN (1437407, 1488626, 1488625)
        AND odd IS NOT NULL
        AND odd > 0
    GROUP BY fixture_id, market_id, label
) best ON o1.fixture_id = best.fixture_id 
    AND o1.market_id = best.market_id 
    AND o1.label = best.label
    AND (CASE WHEN o1.bookmaker_id = 8 THEN 0 ELSE 1 END) = best.best_priority
WHERE o1.fixture_id IN (1437407, 1488626, 1488625);
