Query Optimization in MySQL – Detect & Speed Up Slow Queries

If tutorials available on this website are helpful for you, please whitelist this website in your ad blocker😭 or Donate to help us ❤️ pay for the web hosting to keep the website running.

Query Optimization by Growtika on Unsplash

Heavy traffic और database size बढ़ जाने की वजह से कई बार database की read/write speed slow हो जाती है। इस blog में detail में हम पढ़ेंगे कि कैसे slow query को detect कर उन्हें optimize कर सकते हैं।

1. Slow Query Log Enable करें

MySQL में built-in slow_query_log feature होता है जो queries को log करता है जो specified threshold (long_query_time) से ज़्यादा time लेती हैं।

Default long_query_time 10 seconds होता है, लेकिन आप इसे customized कर सकते हैं।

Copy
SHOW GLOBAL VARIABLES LIKE 'slow_query_log';
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL slow_query_log_file = '/path/to/slow.log';

इस के बाद, जो queries 2 seconds से ज़्यादा तक run करती हैं वो log file में record होती हैं, आप mysqldumpslow या pt‑query‑digest जैसे tools use करके उन्हें summary view में देख सकते हैं।

2. Performance Schema और sys Schema Use करें

MySQL 5.6+ में performance_schema enable करके queries का detailed runtime metrics मिलता है. आप top slow queries का summary निकाल सकते हैं।

Copy
SELECT digest_text, AVG_TIMER_WAIT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC LIMIT 10;

इस से आपको पता चलता है कौन सी queries frequently slow run हो रही हैं और कितनी rows examine और return हो रही हैं।

3. Monitoring Tools का Use

Percona Monitoring and Management (PMM), pt‑query‑digest, Anemometer, या MySQL Enterprise Monitor जैसे tools real-time metrics देते हैं और top slow queries highlight करते हैं।

4. Log_queries_not_using_indexes Option

Enable -

Copy
SET GLOBAL log_queries_not_using_indexes = ON;

इस से queries जो indexes use नहीं कर रही वो भी log होती हैं, जो performance issues detect करने में बहुत helpful है।

●●●

MySQL Optimize Slow Query

1. Identify Problematic Queries

• Slow Query Log या Performance Schema reports से identify करो कि कौन सी queries सबसे ज़्यादा time लेती हैं और frequently run होती हैं।

• Rows_examined और rows_sent ratio देख कर inefficiency assess करो।
  

2. Use EXPLAIN and EXPLAIN ANALYZE

• EXPLAIN को query के साथ run करो तो देखो execution plan जैसे full table scan vs index access, join types, extra columns (Using index, filesort, temporary).

• MySQL 8.0+ में EXPLAIN ANALYZE runtime statistics देता है, जो actual time metrics show करता है।
  

3. Indexing Improvements

• अगर EXPLAIN में type = ALL दिख रहा है तो full table scan हो रहा है — construct index on columns in WHERE or JOIN conditions.

• Use composite indexes या covering indexes जिससे query table lookup skip कर सके। 

• Remove or avoid patterns like LIKE '%text' जो index usage inhibit करते हैं; instead use indexed prefix or FULLTEXT indexes.

4. Rewrite Query Structures

• SELECT * query को avoid करें। सिर्फ required columns को specify करें ताकि I/O reduce हो सके।

• Rewrite subqueries or correlated queries as JOINs or CTEs for better optimizer support.

• Optimize sorting : अगर Using filesort दिख रहा हो, ORDER BY columns पर indexes add करें।

• जहाँ possible हो वहाँ LIMIT and pagination का use करें जिससे result set size limited हो।
  

5. Schema Design and Partitioning

• Normalize appropriately; सिर्फ read-heavy workloads के लिए denormalize करें।

• large tables के लिए , partitioning (range, hash, list) use करें जिससे सिर्फ relevant partitions ही scan हो।
  

6. InnoDB Engine Tuning

• disk reads को reduce करने के लिए innodb_buffer_pool_size ≈ 70–80% RAM set करें।

• write performance के लिए innodb_log_file_size, adaptive_hash_index etc. Tune करें।
  

7. Hardware and Monitoring

• storage के लिए SSD का use करें , जिससे I/O latency minimize हो सके।

• CPU, memory, buffer pool reads/writes etc. का resource usage monitor करें।
  

8. Re‑test और Iterate करो

• Optimized queries पर दुबारा EXPLAIN या EXPLAIN ANALYZE run करो।

• देखो कि rows examined और execution time में कितना improvement हुआ है। 

• जब तक diminishing returns मिलते रहें (जैसे improvement minimal हो जाये), tuning continue करो।

●●●

Quick Query Optimization Guide

• Slow Query Log enable करो और अपना threshold (जैसे 2 sec) set करो।

• pt-query-digest या mysqldumpslow का use करो ताकि queries को summarize कर सको। 

• performance_schema enable करो और देखो top slow queries कौन सी हैं। 

• EXPLAIN या EXPLAIN ANALYZE run करो और query plan समझो। 

• सही index बनाओ – जैसे single, composite या covering index (as per query structure).

• SELECT * avoid करो – सिर्फ needed columns select करो, और subqueries को JOINs से replace करो। 

• LIMIT clause और ORDER BY के साथ index का use करो pagination के लिए। 

• अपने schema को सही तरीके से design करो – normalize करो या large tables को partition में divide करो। 

• InnoDB settings tune करो – जैसे buffer pool size, log file size etc.

• Hardware और system metrics monitor करो, और जब need लगे तो optimize करते रहो
  

●●●