-- ============================================================
-- OHLCV Table Optimization Script
-- Database: cornbyt1_invkamu_market.ohlcv_data
-- Current: 75M rows, 15.6 GB (data=8.9GB, index=6.7GB)
-- ============================================================

-- ────────────────────────────────────────────────────────────
-- STEP 1: Drop redundant index (saves ~3 GB)
-- ────────────────────────────────────────────────────────────
-- idx_coin_tf_dt (asset_id, timeframe, datetime_wib) = 3,088 MB
-- uq_ohlcv       (asset_id, timeframe, source, datetime_wib) = 3,642 MB
--
-- uq_ohlcv sudah cover semua query pattern:
--   ✓ WHERE asset_id=X AND timeframe=Y AND source=Z ORDER BY datetime_wib
--   ✓ WHERE asset_id=X GROUP BY timeframe (prefix match)
--   ✓ Uniqueness enforcement untuk UPSERT
--
-- EXPLAIN confirmed: uq_ohlcv bekerja sama baiknya untuk semua query

ALTER TABLE cornbyt1_invkamu_market.ohlcv_data
  DROP INDEX idx_coin_tf_dt;

-- Expected savings: ~3 GB
-- Duration: ~1-2 menit (metadata-only di MariaDB 10.4+ untuk DROP INDEX)

-- ────────────────────────────────────────────────────────────
-- STEP 2: ROW_FORMAT=COMPRESSED (saves ~40-50% total size)
-- ────────────────────────────────────────────────────────────
-- ⚠️ WARNING: Operasi ini REBUILD seluruh tabel!
-- Estimasi durasi: 20-60 menit pada 75M rows
-- Selama proses, tabel tetap bisa dibaca (online DDL)
-- Jalankan saat tidak ada sync berjalan
--
-- Before: ~12.5 GB (setelah drop index)
-- After:  ~6-7 GB (compressed)

ALTER TABLE cornbyt1_invkamu_market.ohlcv_data
  ROW_FORMAT=COMPRESSED
  KEY_BLOCK_SIZE=8;

-- ────────────────────────────────────────────────────────────
-- STEP 3: Optimize table (reclaim space after changes)
-- ────────────────────────────────────────────────────────────

OPTIMIZE TABLE cornbyt1_invkamu_market.ohlcv_data;

-- ────────────────────────────────────────────────────────────
-- VERIFY: Check results
-- ────────────────────────────────────────────────────────────

SELECT
  TABLE_NAME, ENGINE, ROW_FORMAT, TABLE_ROWS,
  ROUND(DATA_LENGTH/1024/1024, 1) AS data_mb,
  ROUND(INDEX_LENGTH/1024/1024, 1) AS idx_mb,
  ROUND((DATA_LENGTH + INDEX_LENGTH)/1024/1024, 1) AS total_mb,
  AVG_ROW_LENGTH
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='cornbyt1_invkamu_market'
  AND TABLE_NAME='ohlcv_data';

-- Show remaining indexes
SHOW INDEX FROM cornbyt1_invkamu_market.ohlcv_data;