Indekslar va so'rov optimizatsiyasi

-- Indekssiz: butun jadval o'qiladi (full table scan)
SELECT * FROM talabalar WHERE email = '[email protected]';

-- Indeks yaratamiz
CREATE INDEX idx_email ON talabalar (email);

-- Endi: to'g'ridan-to'g'ri topiladi
SELECT * FROM talabalar WHERE email = '[email protected]';

-- 1 000 000 qatorli jadvalda
SELECT * FROM loglar WHERE foydalanuvchi_id = 12345;

-- Oddiy indeks
CREATE INDEX idx_shahar ON talabalar (shahar);

-- Noyob indeks (takrorlanishga yo'l qo'ymaydi)
CREATE UNIQUE INDEX uq_email ON talabalar (email);

-- Tarkibiy (bir necha ustun)
CREATE INDEX idx_shahar_baho ON talabalar (shahar, ortacha_baho);

-- To'liq matnli qidiruv
CREATE FULLTEXT INDEX ft_matn ON maqolalar (sarlavha, matn);

-- Jadval yaratishda
CREATE TABLE talabalar (
    id     INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email  VARCHAR(190) NOT NULL,
    shahar VARCHAR(60),

    UNIQUE KEY uq_email (email),
    KEY idx_shahar (shahar)
) ENGINE=InnoDB;

-- Ko'rish va o'chirish
SHOW INDEX FROM talabalar;
DROP INDEX idx_shahar ON talabalar;

CREATE INDEX idx_shahar_baho ON talabalar (shahar, ortacha_baho);

WHERE shahar = 'Toshkent'
WHERE shahar = 'Toshkent' AND ortacha_baho > 4.0
WHERE shahar = 'Toshkent' ORDER BY ortacha_baho

WHERE ortacha_baho > 4.0                    -- birinchi ustun yo'q
WHERE ortacha_baho > 4.0 AND shahar = 'X'   -- ishlaydi (tartib muhim emas)

EXPLAIN SELECT * FROM talabalar WHERE shahar = 'Toshkent';

-- Batafsil tahlil (MySQL 8.0+)
EXPLAIN ANALYZE SELECT ... ;

-- JSON formatida
EXPLAIN FORMAT=JSON SELECT ... ;

CREATE INDEX idx_shahar_ism ON talabalar (shahar, ism);

-- Bu so'rov faqat indeksdan o'qiydi - juda tez
SELECT ism FROM talabalar WHERE shahar = 'Toshkent';

ALTER TABLE maqolalar ADD FULLTEXT INDEX ft_qidiruv (sarlavha, matn);

SELECT sarlavha,
       MATCH(sarlavha, matn) AGAINST('python algoritm') AS ball
FROM maqolalar
WHERE MATCH(sarlavha, matn) AGAINST('python algoritm')
ORDER BY ball DESC;

-- Mantiqiy rejim
SELECT * FROM maqolalar
WHERE MATCH(sarlavha, matn)
      AGAINST('+python -java' IN BOOLEAN MODE);

-- Sekin so'rovlar jurnalini yoqish
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;          -- 1 soniyadan uzun

-- Hozir bajarilayotgan so'rovlar
SHOW PROCESSLIST;

-- Indekslar hajmi
SELECT
    table_name,
    ROUND(data_length / 1024 / 1024, 1)  AS malumot_mb,
    ROUND(index_length / 1024 / 1024, 1) AS indeks_mb
FROM information_schema.TABLES
WHERE table_schema = 'maktab'
ORDER BY index_length DESC;

-- Sekin so'rov
EXPLAIN SELECT * FROM buyurtmalar
WHERE YEAR(yaratilgan) = 2026 AND holat = 'yangi'
ORDER BY yaratilgan DESC;
-- type: ALL, rows: 500000, Extra: Using filesort

-- 1-tuzatish: funksiyani olib tashlash
-- 2-tuzatish: tarkibiy indeks
CREATE INDEX idx_holat_sana ON buyurtmalar (holat, yaratilgan);

EXPLAIN SELECT id, mijoz_id, summa, yaratilgan
FROM buyurtmalar
WHERE holat = 'yangi'
  AND yaratilgan >= '2026-01-01'
  AND yaratilgan <  '2027-01-01'
ORDER BY yaratilgan DESC
LIMIT 50;
-- type: range, rows: 1200, Extra: Using where