MySQL Index Performans Sorguları

-- 1. Tablodaki index'leri listele
SHOW INDEX FROM products;

-- 2. EXPLAIN ile sorgu planını analiz et
EXPLAIN ANALYZE
SELECT p.*, c.name AS category_name
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE p.status = 'published'
  AND p.price > 100
ORDER BY p.created_at DESC
LIMIT 20;

-- 3. Kullanılmayan index'leri bul (MySQL 8.0+)
SELECT
    object_schema AS db_name,
    object_name AS table_name,
    index_name,
    count_star AS total_access
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND count_star = 0
  AND object_schema = 'myapp'
ORDER BY object_name;

-- 4. Yavaş sorguları bul
SELECT
    digest_text AS query_pattern,
    count_star AS exec_count,
    ROUND(avg_timer_wait / 1000000000, 2) AS avg_ms,
    ROUND(sum_timer_wait / 1000000000, 2) AS total_ms,
    sum_rows_examined AS rows_scanned
FROM performance_schema.events_statements_summary_by_digest
WHERE schema_name = 'myapp'
ORDER BY avg_timer_wait DESC
LIMIT 10;

-- 5. Composite index oluştur (sık kullanılan sorgu için)
CREATE INDEX idx_products_status_price_created
    ON products(status, price, created_at DESC);

-- 6. Covering index (sorgunun ihtiyaç duyduğu tüm kolonlar)
CREATE INDEX idx_products_listing
    ON products(status, created_at DESC, id, title, price, category_id);

-- 7. Full-text index (arama için)
ALTER TABLE products ADD FULLTEXT INDEX ft_products_search (title, description);

-- Kullanım:
SELECT * FROM products
WHERE MATCH(title, description) AGAINST('spring boot' IN BOOLEAN MODE);