MySQL + SQL · Lesson 92

MySQL में EXPLAIN Query Plan

MySQL EXPLAIN क्या बताता है?

EXPLAIN MySQL optimizer का chosen execution plan दिखाता है: table access order, methods, candidate/selected indexes, estimated rows, filtering और extra operations। यह बताता है MySQL result कैसे पाएगा; SQL result logically correct है या नहीं, यह नहीं।

Faculty rule: Plan evidence है, scorecard नहीं। Full scan correct हो सकता है; index inefficient; low estimated cost elapsed time नहीं। Representative data और measurements से alternatives compare करें।

EXPLAIN SELECT और several data-changing forms support करता है। यहाँ safe SELECT examples हैं। Ordinary EXPLAIN estimated plan देता है; EXPLAIN ANALYZE SELECT execute करके measurements देता है।

Verified Query-Plan Lab

DROP TABLE IF EXISTS orders_plan_lab;
CREATE TABLE orders_plan_lab (
  order_id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  status VARCHAR(20) NOT NULL,
  order_date DATE NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL,
  notes VARCHAR(100)
) ENGINE = InnoDB;

INSERT INTO orders_plan_lab VALUES
(1,101,'PAID',     '2026-08-01',1200.00,'School books'),
(2,101,'PENDING',  '2026-08-05', 500.00,'Stationery'),
(3,102,'PAID',     '2026-08-03', 750.00,'Uniform'),
(4,103,'CANCELLED','2026-08-04', 300.00,'Art kit'),
(5,101,'PAID',     '2026-08-10',1500.00,'Lab equipment'),
(6,102,'PENDING',  '2026-08-11', 900.00,'Sports kit'),
(7,104,'PAID',     '2026-08-12',2200.00,'Computer accessory'),
(8,101,'PAID',     '2026-08-14', 650.00,'Notebooks');

SELECT order_id, order_date, total_amount
FROM orders_plan_lab
WHERE customer_id = 101 AND status = 'PAID'
ORDER BY order_date;

1 | 2026-08-01 | 1200.00 5 | 2026-08-10 | 1500.00 8 | 2026-08-14 | 650.00

पहले result verify, फिर access path study करें। Tiny lab में index add होने के बाद भी scan chosen हो सकता है। यह error नहीं; scale और distribution cost तय करते हैं।

TRADITIONAL, TREE और JSON Formats

EXPLAIN SELECT order_id, order_date
FROM orders_plan_lab
WHERE customer_id = 101 AND status = 'PAID';

EXPLAIN FORMAT=TREE SELECT order_id, order_date
FROM orders_plan_lab
WHERE customer_id = 101 AND status = 'PAID';

EXPLAIN FORMAT=JSON SELECT order_id, order_date
FROM orders_plan_lab
WHERE customer_id = 101 AND status = 'PAID';

Format	Best use
TRADITIONAL	Compact table; type, key, rows, filtered, Extra
TREE	Iterator hierarchy; hash joins भी
JSON	Detailed properties, costs, conditions, key parts, tooling

MySQL 8.4 में server-level explain_format default हो सकता है, इसलिए reproducible output के लिए FORMAT specify करें। EXPLAIN ANALYZE always TREE use करता है; TRADITIONAL/JSON supported नहीं।

Traditional EXPLAIN Columns पढ़ें

Column	Question
id	कौन SELECT/query block?
select_type	SIMPLE, PRIMARY, subquery या derived?
table	कौन table/result?
partitions	Matching partitions?
type	Access/join method?
possible_keys	Candidate indexes?
key	Chosen index?
key_len	Chosen key का कितना भाग?
ref	Key से कौन constants/columns compare?
rows	Estimated examined rows?
filtered	Estimated pass percentage?
Extra	Additional operations?

InnoDB में rows estimate है। Joins में rough fan-out rows और filtering effects से explore कर सकते हैं, पर TREE/JSON और actual measurements अधिक complete हैं।

possible_keys recommendation list नहीं। key=NULL automatically bug नहीं। Required data और lower estimated-cost कारण देखें।

Access Types बिना Hype के

type	Meaning	Interpretation
system / const	At most one matching row	Very narrow
eq_ref	Each previous row के लिए one unique NOT NULL row	Excellent unique join
ref	Nonunique key/prefix matches	Matches few हों तो good
range	Bounded index intervals	Dates, BETWEEN, IN, inequalities
index	Full index-tree scan	Narrow/covering हो तो table scan से cheaper
ALL	Full table scan	Large/frequent selective query में investigate

const से ALL वाली ranking केवल starting vocabulary है। Millions rows वाला ref small scan से costlier हो सकता है। Join order, rows, loops, page access, coverage और result size matter करते हैं।

CREATE INDEX idx_customer_status_date
ON orders_plan_lab (customer_id, status, order_date);

EXPLAIN FORMAT=JSON
SELECT order_id, order_date, total_amount
FROM orders_plan_lab
WHERE customer_id = 101 AND status = 'PAID'
ORDER BY order_date;

key और used_key_parts inspect करें। Eight rows पर output attractive बनाने के लिए index force न करें।

Important Extra Signals

Extra	सही अर्थ
Using where	Rows restrict करने को condition; अपने आप slow नहीं
Using index	Required information index tree से: covering access
Using index condition	Full row से पहले index tuple test
Using filesort	Extra sort; नाम disk file guarantee नहीं
Using temporary	Internal temporary table, often grouping/distinct/order pattern
Backward index scan	Index reverse direction में scan

Using filesort या Using temporary large rows/latency में attention मांगती हैं, automatic failure नहीं। Ten rows sort करना every write पर wide index maintain करने से cheaper हो सकता है।

EXPLAIN ANALYZE: Estimates vs Actuals

EXPLAIN ANALYZE FORMAT=TREE
SELECT order_id, order_date, total_amount
FROM orders_plan_lab
WHERE customer_id = 101 AND status = 'PAID'
ORDER BY order_date;

Each iterator में estimated cost/rows और measured first-row time, time range, actual rows, loops मिलते हैं। Compare करें:

estimated vs actual rows: large error join/access choice बिगाड़ सकता है;
loops: cheap inner operation thousands times dominant हो सकती है;
first-row vs total time: interactive latency और blocking sort;
node-to-node rows: late filtering पहचानें;
selected index/lookups: intended access confirm करें।

Warning: EXPLAIN ANALYZE statement run करता है। Safe SELECT लें। Expensive production query या data-changing statement casually analyze न करें; controlled environment और limits use करें।

Major data change के बाद estimates wrong हों तो skew inspect और ANALYZE TABLE orders_plan_lab; करें। Histograms suitable nonindexed columns की estimates help करती हैं, access index replace नहीं।

Evidence-Based Tuning Workflow

Exact SQL, parameters, frequency, latency, rows sent/examined capture करें।
Logical output verify और representative dataset बनाएँ।
TREE/JSON EXPLAIN से largest estimated work पहचानें।
Safely ANALYZE से actual rows, loops, timing लें।
Sargability, data types, join keys और selected columns check करें।
Smallest workload-aligned composite index या rewrite design करें।
Same before/after measurements और write/storage impact लें।
Edge parameters test करें; selectivity best plan बदल सकती है।
Safely deploy, regressions monitor और rollback रखें।

Common mistakes: every ALL पर index, rows को exact मानना, Using index को “index chosen” समझना, loops ignore करना, empty database tune करना और design/statistics से पहले FORCE INDEX लगाना।

आगे composite indexes और query optimization guide पढ़ें।

Official संदर्भ

Formats, fields, access methods, Extra signals और ANALYZE semantics official MySQL 8.4 manual से verify किए गए हैं।

अक्सर पूछे जाने वाले प्रश्न (FAQ)

क्या EXPLAIN SELECT query execute करता है?

Ordinary EXPLAIN normal SELECT result return किए बिना optimizer का planned execution दिखाता है। EXPLAIN ANALYZE अलग है: वह SELECT execute करके iterator behavior measure करता है।

क्या type=ALL हमेशा bad है?

नहीं। इसका अर्थ full table scan है, जो tiny tables या most rows वाली query में appropriate हो सकता है। Rows, frequency, filtering, joins और measured runtime देखें।

possible_keys और key में क्या अंतर है?

possible_keys relevant candidate indexes दिखाता है; key उस table access के लिए actually selected index है। Candidate का चुना जाना जरूरी नहीं।

क्या Using filesort का अर्थ disk file है?

जरूरी नहीं। इसका अर्थ required index order से rows पढ़ने की जगह extra sort है; sort memory में या temporary storage के साथ हो सकती है।

Estimated और actual rows अलग क्यों हो सकते हैं?

Estimates statistics और cost model से आते हैं। Skew, correlated columns, stale statistics और complex predicates error पैदा कर सकते हैं; EXPLAIN ANALYZE actual rows/loops दिखाता है।

💻 लाइव कोड एडिटर

इस पेज के प्रोग्राम यहीं तैयार हैं — चलाएँ, बदलें और सीखें। कुछ भी इंस्टॉल किए बिना।

OneCompiler द्वारा संचालित। कोड एडिटर में अपने आप आ जाता है — Run दबाकर आउटपुट देखें। अगर एडिटर न खुले तो नए टैब में खोलें.