आप slow SQL queries को कैसे optimize करते हैं?

Question

Accepted Answer

Slow queries को optimize करना **execution plan को analyze करने** (`EXPLAIN`) से शुरू होता है ताकि bottleneck खोजा जा सके, फिर targeted fixes लागू करना — सबसे आम तौर पर **indexes जोड़ना**, query को rewrite करना, और common anti-patterns से बचना। सबसे बड़ा फायदा आमतौर पर full table scans को index lookups में बदलना है।

## Step 1: EXPLAIN के साथ analyze करें

```sql
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 5;
-- shows the EXECUTION PLAN: scan type (Seq Scan = full scan, Index Scan = using an index),
-- estimated/actual rows, cost, and timing → reveals WHERE the time goes
```

`EXPLAIN` (या `EXPLAIN ANALYZE`) दिखाता है कि database query को कैसे execute करता है — महत्वपूर्ण रूप से, क्या यह एक **full table scan** (slow) कर रहा है या एक **index** (fast) का उपयोग कर रहा है। optimize करने से पहले हमेशा profile करें।

## सबसे बड़ा फायदा: indexes जोड़ें

```sql
-- if EXPLAIN shows a Seq Scan on a filtered/joined column → add an index
CREATE INDEX idx_orders_customer ON orders(customer_id);
-- turns a full scan into a fast index lookup
```

सबसे आम और प्रभावशाली fix: `WHERE`, `JOIN`, और `ORDER BY` में उपयोग किए जाने वाले columns पर **indexes जोड़ें**। यह full table scans को fast lookups में बदल देता है।

## ठीक करने के लिए common anti-patterns

```sql
-- ❌ functions on a column prevent index use
WHERE UPPER(email) = 'X@Y.COM'        -- can't use an index on email
WHERE email = 'x@y.com'               -- ✅ can use the index

-- ❌ leading wildcard LIKE can't use an index
WHERE name LIKE '%smith'              -- ✅ 'smith%' (prefix) can use an index

-- ❌ SELECT * fetches unneeded columns
SELECT * → SELECT only_needed_columns  -- less data, may use a covering index

-- ❌ N+1 queries (querying in a loop) → use a single JOIN instead
```

## अन्य optimization techniques

```text
✓ Fix N+1 queries (one query per row) → use JOINs / batch into one query
✓ Add appropriate indexes (including COMPOSITE for multi-column filters)
✓ Reduce data scanned — filter early, select only needed columns, paginate
✓ Rewrite subqueries as JOINs where more efficient
✓ Use EXISTS instead of IN for existence checks on large subqueries
✓ Keep statistics updated (ANALYZE) so the planner makes good choices
✓ Consider denormalization / materialized views for expensive repeated queries
✓ Watch for missing indexes on FOREIGN KEYS (joins scan without them)
```

## यह क्यों महत्वपूर्ण है

Query optimization database performance के लिए critical ज्ञान है — slow queries poor application performance का एक प्रमुख कारण हैं, और उन्हें खोजना और ठीक करना कैसे है यह समझना scale पर databases के साथ काम करने वाले किसी भी व्यक्ति के लिए आवश्यक senior-level skill है।

मौलिक अनुशासन है optimize करने से पहले **`EXPLAIN` के साथ analyze करना** (वास्तविक bottleneck खोजने के लिए execution plan की जाँच करना — महत्वपूर्ण रूप से क्या query एक slow full table scan करती है या एक fast index lookup) बजाय अनुमान लगाने के — यह measurement-driven approach ही optimization को प्रभावी बनाता है।

**सबसे बड़ा और सबसे आम फायदा filtered, joined, और sorted columns पर indexes जोड़ना है** (full table scans को fast lookups में बदलना — अक्सर query time को orders of magnitude तक सुधारना), जिससे index ज्ञान optimization का केंद्र बन जाता है।

**Common anti-patterns** को समझना समान रूप से महत्वपूर्ण है: columns पर functions और leading-wildcard `LIKE` index उपयोग को रोकते हैं, `SELECT *` अनावश्यक data fetch करता है, और विशेष रूप से **N+1 queries** (single JOIN के बजाय एक loop में query करना — application code में एक बार-बार होने वाला, गंभीर performance killer) — इन्हें पहचानना और ठीक करना slow queries के सबसे आम कारणों को संबोधित करता है।

अन्य techniques (composite indexes, scan किए गए data को कम करना, subqueries को JOINs के रूप में rewrite करना, statistics को updated रखना, महंगी queries के लिए materialized views) toolkit को पूरा करती हैं।

चूंकि query performance सीधे application responsiveness और scalability को प्रभावित करती है, और चूंकि systematic approach (EXPLAIN के साथ analyze करें, फिर ठीक करें — आमतौर पर indexes जोड़कर और N+1 जैसे anti-patterns को समाप्त करके) ही databases को load के तहत fast रखता है, query optimization में महारत हासिल करना महत्वपूर्ण, उच्च-मूल्य का senior ज्ञान है जो उन developers को अलग करता है जो performant, scalable database-backed applications बना सकते हैं — एक बार-बार प्रासंगिक, critical skill और database performance के लिए एक common interview topic।