MySQL में Query Optimization

Query Optimization: पहले Measure करें

Query optimization correct results, concurrency और maintainability बचाते हुए total database work घटाने की disciplined process है। इसमें SQL shape, schema, indexes, statistics, application calls, transactions, memory और storage आते हैं—सिर्फ “index add” नहीं।

1. Latency, frequency, rows examined, lock time या resource से high-impact queries खोजें।
2. Exact SQL, bound values और result requirements capture करें।
3. Representative volume, distribution और concurrency reproduce करें।
4. EXPLAIN पढ़ें और safely EXPLAIN ANALYZE measure करें।
5. One justified change करके same metrics compare करें।

Slow query log configured threshold से ऊपर statements identify करती है। Application traces और business context भी लें क्योंकि average rare bad parameters छिपा सकता है।

Verified Orders Optimization Lab

DROP TABLE IF EXISTS orders_opt_lab;
DROP TABLE IF EXISTS customers_opt_lab;

CREATE TABLE customers_opt_lab (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR(80) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE orders_opt_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),
  CONSTRAINT fk_opt_customer
    FOREIGN KEY (customer_id) REFERENCES customers_opt_lab(customer_id)
) ENGINE = InnoDB;

INSERT INTO customers_opt_lab VALUES
(101,'Asha'),(102,'Bilal'),(103,'Charu'),(104,'Deepak');

INSERT INTO orders_opt_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 COUNT(*) AS paid_orders,
       SUM(total_amount) AS paid_total
FROM orders_opt_lab
WHERE customer_id = 101 AND status = 'PAID';

Lab known result पर rewrites verify कराती है, eight rows पर speed prove नहीं। Benchmark के लिए separate test database में realistic distributions बनाएँ।

Sargable, Type-Correct Predicates

Predicate sargable है जब suitable index search range define कर सके। Compare करें:

Join-key data types/collations compatible रखें। Implicit conversions lookup रोक या comparison rule बदल सकती हैं। Expression search real requirement हो तो functional key part या indexed generated column consider और plan verify करें।

Query Shapes के लिए Index Design

CREATE INDEX idx_customer_status_date_amount
ON orders_opt_lab
  (customer_id, status, order_date, total_amount);

यह customer/status equality, date range/order के साथ align और suitable queries में total_amount cover कर सकती है। Leftmost prefixes customer_id से शुरू होते हैं; status-only report को same direct lookup नहीं मिलता।

EXPLAIN FORMAT=TREE
SELECT order_id, order_date, total_amount
FROM orders_opt_lab
WHERE customer_id = 101
  AND status = 'PAID'
  AND order_date >= '2026-08-01'
  AND order_date <  '2026-09-01'
ORDER BY order_date;

• Equality/join patterns deliberately range/order parts से पहले रखें।
• Every important query पर leftmost-prefix rule apply करें।
• Coverage तभी जब measured saved lookups width justify करें।
• Redundant indexes cautiously हटाएँ; invisible test reversible है।
• Every index का write, buffer-pool और storage cost count करें।
• InnoDB primary key short/stable रखें।

Every WHERE column independently index न करें। Few aligned composites अक्सर overlapping indexes से better हैं।

Joins, Aggregation और Application Calls

SELECT c.customer_id, c.customer_name,
       COUNT(*) AS paid_orders,
       SUM(o.total_amount) AS paid_total
FROM customers_opt_lab AS c
JOIN orders_opt_lab AS o
  ON o.customer_id = c.customer_id
WHERE o.status = 'PAID'
GROUP BY c.customer_id, c.customer_name
ORDER BY paid_total DESC, c.customer_id;

• Join direction और filtering के अनुसार foreign/join keys index करें।
• Correct filtering के बाद aggregate; useful हो तो large many-side pre-aggregate करें।
• Accidental Cartesian products और mismatched types avoid करें।
• N+1 application queries को set query, batch fetch या justified cache से replace करें।
• SELECT * की जगह required columns लें।
• Related row existence के लिए EXISTS लें; unused detail fetch न करें।

MySQL cost से join order चुनता है। Written table order normally execution order नहीं; textual “filter early” assume करने की जगह plan पढ़ें।

ORDER BY, LIMIT और Pagination

Matching index separate sort avoid कर सकती है; अन्यथा Using filesort reasonable हो सकती है। Stable pages के लिए deterministic order लें।

SELECT order_id, order_date, total_amount
FROM orders_opt_lab
ORDER BY order_date DESC, order_id DESC
LIMIT 20 OFFSET 40;

Deep OFFSET earlier rows find और discard करती है। Sequential next page के लिए keyset last seen key के बाद resume करती है:

CREATE INDEX idx_date_id
ON orders_opt_lab (order_date DESC, order_id DESC);

SELECT order_id, order_date, total_amount
FROM orders_opt_lab
WHERE (order_date, order_id) < ('2026-08-10', 5)
ORDER BY order_date DESC, order_id DESC
LIMIT 20;

Keyset next/previous में fast/stable है, arbitrary page 500 jump naturally नहीं। Product need के अनुसार चुनें। Ties में unique tie-breaker add करें।

Statistics, Estimates और Safe Controls

ANALYZE TABLE orders_opt_lab;
SHOW INDEX FROM orders_opt_lab;

EXPLAIN FORMAT=JSON
SELECT order_id, total_amount
FROM orders_opt_lab
WHERE status = 'PAID';

Cost-based optimizer statistics से selectivity/cost estimate करता है। Major changes, skew या correlated columns से estimate wrong हो सकती है। ANALYZE TABLE key distribution refresh करता; optional histograms suitable columns, खासकर nonindexed, की estimates improve कर सकती हैं।

ANALYZE TABLE orders_opt_lab
UPDATE HISTOGRAM ON status WITH 16 BUCKETS;

ANALYZE TABLE orders_opt_lab
DROP HISTOGRAM ON status;

Histograms estimation help करती हैं, index जैसा row access नहीं। EXPLAIN estimates को ANALYZE actuals से compare करें। Data types, shape, index और statistics के बाद ही hints use करें।

Production Optimization Checklist

1. Service goal define: percentile latency, throughput, freshness, load।
2. Total impact से top SQL capture करें।
3. Exact parameters और expected result preserve करें।
4. Representative rows, skew, cache और concurrency reproduce करें।
5. TREE/JSON EXPLAIN और safely ANALYZE लें।
6. N+1, unused columns, duplicate work, late filtering fix करें।
7. Semantics बचाकर non-sargable predicates rewrite करें।
8. Minimal composite indexes; redundancy/write cost check करें।
9. Justified statistics refresh; estimate vs actual compare करें।
10. Repeated before/after benchmark; cold/warm और tail latency लें।
11. Gradual deploy, regressions monitor, rollback SQL रखें।
12. Growth के बाद revisit करें; optimal plan workload-dependent है।

Myths पर optimize न करें: “indexes always win,” “joins slow,” “subqueries slow,” “CTEs faster,” और “filesort means disk” reliable rules नहीं। Concrete plan measure करें।

Related: indexes, composite indexes, EXPLAIN और locks/deadlocks।

Official संदर्भ

• MySQL 8.4: Optimization
• MySQL 8.4: ANALYZE TABLE
• MySQL 8.4: LIMIT Query Optimization

Plan measurement, statistics, index trade-offs और LIMIT behavior official MySQL 8.4 manual से verify किए गए हैं।