⚡ Indeksi un optimizācija

-- Izveidot indeksu
CREATE INDEX idx_category ON products(category);
CREATE INDEX idx_price    ON products(price);

-- Indekss, veidojot tabulu
CREATE TABLE orders (
    id      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    status  ENUM('gaida','apstrādē','nosūtīts'),
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id   (user_id),        -- Ātrs meklēšana pēc user_id
    INDEX idx_status    (status),
    INDEX idx_created   (created_at)
);

-- Unikāls indekss (nevienam dublikātam)
CREATE UNIQUE INDEX idx_email ON users(email);

-- Kompozīts indekss (vairākas kolonnas)
-- Efektīvs, ja WHERE izmanto ABAS vai tikai PIRMO kolonnu
CREATE INDEX idx_user_status ON orders(user_id, status);

-- Pilna teksta meklēšana
CREATE FULLTEXT INDEX idx_search ON products(name, description);
SELECT * FROM products
WHERE MATCH(name, description) AGAINST('telefons' IN BOOLEAN MODE);

-- Pievienot indeksu esošai tabulai
ALTER TABLE products ADD INDEX idx_in_stock (in_stock);
ALTER TABLE users    ADD UNIQUE INDEX idx_username (username);

-- Dzēst indeksu
DROP INDEX idx_category ON products;
ALTER TABLE products DROP INDEX idx_category;

-- Apskatīt esošos indeksus
SHOW INDEX FROM products;

-- EXPLAIN rāda, kā MySQL izpildīs vaicājumu
EXPLAIN SELECT * FROM orders WHERE user_id = 5;

-- Svarīgākās EXPLAIN kolonnas:
-- type:
--   ALL              – pilna tabulas skenēšana (LĒNS!)
--   index            – skenē indeksu
--   range            – izmanto indeksa diapazonu
--   ref              – izmanto neuniquālu indeksu
--   eq_ref           – unikāls indekss (1 rinda)
--   const/system     – konstante (ĀTRS)
-- possible_keys – indeksi, ko varētu izmantot
-- key          – indekss, ko faktiski izmanto
-- rows         – aptuvens skenēto rindu skaits
-- Extra:
--   Using where      – filtrē ar WHERE pēc lasīšanas
--   Using index      – atbild no indeksa (ļoti ātrs!)
--   Using filesort   – papildu kārtošana (lēnāks)
--   Using temporary  – pagaidu tabula

-- Piemērs: lēns vaicājums
EXPLAIN SELECT * FROM products WHERE YEAR(created_at) = 2025;
-- type: ALL (MySQL neizmanto indeksu, jo funkcija uz kolonnas)

-- Labojums: izmantot diapazonu
EXPLAIN SELECT * FROM products
WHERE created_at BETWEEN '2025-01-01' AND '2025-12-31 23:59:59';
-- type: range (izmanto indeksu!)

-- EXPLAIN ANALYZE (MySQL 8.0+) – reālie izpildes laiki
EXPLAIN ANALYZE
SELECT u.username, COUNT(o.id) AS orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id;

-- View ir saglabāts SELECT – darbojas kā tabula
CREATE VIEW v_products_with_category AS
    SELECT
        p.id,
        p.name,
        p.price,
        p.in_stock,
        c.name AS category
    FROM products p
    LEFT JOIN categories c ON p.category_id = c.id;

-- Izmantot view kā tabulu
SELECT * FROM v_products_with_category WHERE price > 50;
SELECT category, COUNT(*) FROM v_products_with_category GROUP BY category;

-- Pasūtījumu kopsavilkums
CREATE VIEW v_order_summary AS
    SELECT
        o.id,
        u.username,
        u.email,
        o.total,
        o.status,
        o.created_at,
        COUNT(oi.id) AS poziciju_skaits
    FROM orders o
    INNER JOIN users       u  ON o.user_id   = u.id
    INNER JOIN order_items oi ON oi.order_id = o.id
    GROUP BY o.id, u.username, u.email, o.total, o.status, o.created_at;

-- Vaicāt view
SELECT * FROM v_order_summary WHERE status = 'nosūtīts' ORDER BY created_at DESC;

-- Nomainīt view
CREATE OR REPLACE VIEW v_products_with_category AS
    SELECT p.*, c.name AS category
    FROM products p
    LEFT JOIN categories c ON p.category_id = c.id;

-- Dzēst view
DROP VIEW IF EXISTS v_products_with_category;

-- Apskatīt tabulas/skatus
SHOW FULL TABLES WHERE Table_type = 'VIEW';

-- 1. Indeksē kolonnas, ko izmanto WHERE, JOIN, ORDER BY

-- 2. Izvairīties no funkcijām uz indeksētām kolonnām WHERE
-- SLIKTI (MySQL nevar izmantot indeksu):
SELECT * FROM users WHERE YEAR(created_at) = 2025;
SELECT * FROM users WHERE LOWER(email) = 'anna@example.lv';

-- LABI:
SELECT * FROM users WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';
SELECT * FROM users WHERE email = 'anna@example.lv';   -- Glabāt e-pastus lowercase!

-- 3. Izmantot LIMIT vienmēr, kad vajag tikai dažas rindas
SELECT * FROM logs ORDER BY id DESC LIMIT 100;   -- Ne SELECT * bez LIMIT!

-- 4. SELECT tikai vajadzīgās kolonnas (ne SELECT *)
-- SLIKTI:
SELECT * FROM products JOIN categories ON ...;
-- LABI:
SELECT p.name, p.price, c.name AS category FROM products p JOIN ...;

-- 5. Izvairīties no SELECT * N+1 problēmas (PHP/ORM)
-- Nevis: viena query za users, tad viena query katram user orders
-- Bet: viens JOIN vai eager load

-- 6. Pārbaudīt lēnos vaicājumus
-- iespējot slow query log (my.cnf):
-- slow_query_log = 1
-- slow_query_log_file = /var/log/mysql/slow.log
-- long_query_time = 1   (sekundes)

-- 7. ANALYZE TABLE – atjaunot statistiku
ANALYZE TABLE products;
OPTIMIZE TABLE products;   -- Defragmentēt (tikai MyISAM vai ARCHIVE)