EXPLAIN inaonyesha mpango ambao optimizer iliuchagua — jinsi itakavyofikia kila jedwali — bila kuendesha query. Unauisoma ili kuthibitisha kwamba indexes zinatumika na rows chache zinachunguzwa.
EXPLAIN inaonyesha mpango ambao optimizer iliuchagua — jinsi itakavyofikia kila jedwali — bila kuendesha query. Unauisoma ili kuthibitisha kwamba indexes zinatumika na rows chache zinachunguzwa.
const / eq_ref / ref (index lookups, nzuri) → range → index (full index scan) → ALL (full table scan, ishara ya hatari).NULL = hakuna).Using index (covering, nzuri sana), Using filesort / Using temporary (sorting/grouping kwenye memory au disk — mara nyingi inaweza kuboreshwa).EXPLAIN SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at;
-- +------+------+------+---------+--------+----------------+
-- | type | key | rows | Extra |
-- | ALL | NULL | 900k | Using where; Using filesort | <- scans everything, sorts
-- +------+------+------+---------+--------+----------------+
-- Add an index that covers the filter AND the sort order:
CREATE INDEX idx_cust_created ON orders (customer_id, created_at);
EXPLAIN SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at;
-- | type | key | rows | Extra |
-- | ref | idx_cust_created | 30 | Using where | <- seeks 30 rows, no filesort
Index iligeuza ALL scan ya rows 900k kuwa ref seek ya 30, na kwa sababu created_at ni column ya pili, rows zinatoka zikiwa tayari zimepangwa (pre-sorted) — filesort inatoweka.
Tumia EXPLAIN ANALYZE (MySQL 8) ili pia kuona muda halisi na idadi ya rows, ukikamata visa ambapo makadirio ni yasiyo sahihi.
EXPLAIN ndiyo jinsi unavyobadilisha kubahatisha kwa ushahidi. Kuona type: ALL, rows kubwa mno, au Using filesort kunakuambia hasa mahali index inakosekana — njia ya haraka zaidi kutoka "page ni polepole" hadi suluhisho lililolengwa.
Maktaba ya maswali ya mahojiano ya IT yenye majibu ya kina — kutoka Junior hadi Senior.
Changia