Fara da shirin bugun gida, sannan gyara mafi girman costs. Tambayi EXPLAIN/ don ganin abin da jiya (database) ke bukin ke yi na gaskiya, ƙara daidai , cire bugunin, da zaɓi daidai bayanai kake buƙata.
EXPLAIN ANALYZEEXPLAIN ANALYZE yana nuna shirin aiki da na hakika lokuta. A Seq Scan akan babbar tebur shine klasika red flag:
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;
-- Seq Scan on orders (cost=0.00..18500 rows=120)
-- Filter: (customer_id = 42)
-- rows removed by filter: 999880 ← scanned the whole table!
Index yana juyar da cikakken scan zuwa sauri kallo:
CREATE INDEX idx_orders_customer ON orders (customer_id);
-- Now: Index Scan using idx_orders_customer (cost=0.42..8.5 rows=120)
-- 1,000,000-row scan → ~120-row lookup
Indexes suna da costs akan rubuta: kowane INSERT/UPDATE dole ne ya bugi kuma saita index, don haka index columnsunka da kake taka/haɗa/jajere su — ba kowane column ba. A composite index (customer_id, created_at) yana aiki WHERE customer_id = ? ORDER BY created_at a gida gida.
N+1 matsalar — ɗaya bugun jiya, sannan ɗaya per row — shine babbar dalilin saurin saurin. Sauya shi da allunar jiya, JOIN, ko ɓataye IN (...) :
-- N+1: 1 + 100 queries
SELECT * FROM orders; -- then per order: SELECT * FROM users WHERE id = ?
-- Fixed: one JOIN, only needed columns
SELECT o.id, o.total, u.name
FROM orders o JOIN users u ON u.id = o.user_id;
Hakanan zaɓi daidai columns kake buƙata (kaucewa SELECT *) da paginate tare da LIMIT/keyset pagination don kada ka jamo miliyoyin jeri.
Jiya (database) shine mafi gajeren abin taba. Karantaccen shiri yana gaya maka yaya jiya ta saurin saurin da gaji; daidai index, sauya N+1, da taɓawa sakamakon jeri sun sauka kawai saurin-tsawon bugun zuwa millisecond daya — ba tare da miƙa hardware ba.