Indekslar qanday ishlaydi

CREATE TABLE sonlar (
    n INT PRIMARY KEY
);

INSERT INTO sonlar (n)
WITH RECURSIVE ketma_ket AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM ketma_ket WHERE n < 20000
)
SELECT n FROM ketma_ket;

CREATE TABLE xodimlar (
    id       INT PRIMARY KEY,
    ism      VARCHAR(50)  NOT NULL,
    familiya VARCHAR(50)  NOT NULL,
    bolim    VARCHAR(30)  NOT NULL,
    maosh    INT          NOT NULL,
    shahar   VARCHAR(30)  NOT NULL
);

INSERT INTO xodimlar (id, ism, familiya, bolim, maosh, shahar)
SELECT
    n,
    CONCAT('Ism', n),
    CONCAT('Familiya', n),
    ELT(1 + (n % 4), 'IT', 'Hisobot', 'Marketing', 'Kadrlar'),
    3000000 + (n % 50) * 100000,
    ELT(1 + (n % 5), 'Namangan', 'Toshkent', 'Andijon', 'Farg''ona', 'Buxoro')
FROM sonlar;

SELECT COUNT(*) AS qatorlar FROM xodimlar;

-- Indekssiz ustun bo'yicha qidiruv - butun jadval o'qiladi
FLUSH STATUS;

SELECT COUNT(*) FROM xodimlar WHERE familiya = 'Familiya500';

SHOW SESSION STATUS LIKE 'Handler_read_rnd_next';

CREATE INDEX idx_familiya ON xodimlar (familiya);

FLUSH STATUS;

SELECT COUNT(*) FROM xodimlar WHERE familiya = 'Familiya500';

SHOW SESSION STATUS LIKE 'Handler_read_rnd_next';

SELECT INDEX_NAME AS indeks, SEQ_IN_INDEX AS tartib,
       COLUMN_NAME AS ustun, NON_UNIQUE AS takrorlanadi
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'xodimlar'
ORDER BY BINARY INDEX_NAME, SEQ_IN_INDEX;

CREATE INDEX idx_bolim_maosh ON xodimlar (bolim, maosh);

-- Birinchi ustun bo'yicha - indeks ISHLAYDI
FLUSH STATUS;
SELECT COUNT(*) FROM xodimlar WHERE bolim = 'IT';
SHOW SESSION STATUS LIKE 'Handler_read_rnd_next';

-- Ikkala ustun bo'yicha - indeks TO'LIQ ishlaydi
FLUSH STATUS;
SELECT COUNT(*) FROM xodimlar WHERE bolim = 'IT' AND maosh = 3000000;
SHOW SESSION STATUS LIKE 'Handler_read_rnd_next';

-- FAQAT ikkinchi ustun bo'yicha - indeks YARAMAYDI
FLUSH STATUS;
SELECT COUNT(*) FROM xodimlar WHERE maosh = 3000000;
SHOW SESSION STATUS LIKE 'Handler_read_rnd_next';

CREATE INDEX idx_shahar_ism ON xodimlar (shahar, ism);

-- Kerakli ustunlar indeksda BOR - jadvalga borish shart emas
EXPLAIN SELECT shahar, ism FROM xodimlar WHERE shahar = 'Andijon' LIMIT 5;

SELECT
    COUNT(DISTINCT INDEX_NAME) AS indekslar_soni
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'xodimlar';

SELECT
    ROUND(COUNT(DISTINCT bolim)   / COUNT(*), 6) AS bolim,
    ROUND(COUNT(DISTINCT shahar)  / COUNT(*), 6) AS shahar,
    ROUND(COUNT(DISTINCT maosh)   / COUNT(*), 6) AS maosh,
    ROUND(COUNT(DISTINCT familiya)/ COUNT(*), 6) AS familiya
FROM xodimlar;