This article explains how to identify slow‑query symptoms, gather problematic SQL statements, apply common writing and indexing tips, read execution plans, and walk through a complete MySQL optimization case, enabling readers to become proficient database performance engineers.
This article details how Beike's engineering team scaled Apache Kylin to handle tens of millions of daily queries by optimizing HBase configurations, reducing region count, improving data locality, addressing IO and JVM GC bottlenecks, and implementing comprehensive slow‑query detection and active‑defense mechanisms.
This article explains why a MySQL query that uses an index can still become a slow query, explores the difference between index usage and execution time, and presents practical optimization techniques such as improving selectivity, reducing back‑table lookups, using index condition push‑down, and leveraging virtual columns.
This article explains why using indexes does not guarantee fast queries, analyzes slow‑query causes such as full index scans and poor filter selectivity, and demonstrates advanced MySQL optimizations including virtual columns and index condition push‑down to reduce row scans and costly table lookups.
This article explains why using an index does not guarantee fast MySQL queries, examines how execution time, index selectivity, full index scans, and costly back‑to‑table lookups affect slow‑query logs, and presents practical optimization techniques such as virtual columns and index condition push‑down.
A production MySQL query that should have returned instantly took 44 seconds because the optimizer selected the primary key index instead of a suitable composite index, and the article explains the root cause, the optimizer's decision process, and multiple practical solutions to prevent such slow‑query incidents.
This article explains why using indexes does not guarantee fast queries in MySQL, analyzes full index scans, index selectivity, the cost of row look‑ups (back‑to‑table), and presents optimization techniques such as index condition pushdown and virtual columns to improve performance.
This guide compiles essential MySQL optimization techniques—including index cardinality, function indexes, EXPLAIN usage, covering indexes, ICP, slow‑query log analysis, online schema changes, and common SQL pitfalls—to help developers efficiently tune database performance.
This article analyzes the various system‑level and database‑level reasons why SELECT, UPDATE, INSERT, or DELETE statements become slow, covering network issues, OS and hardware constraints, MySQL configuration, indexing, execution plans, and query design, and offers practical mitigation strategies.
This article introduces Percona QAN, explains its three‑component architecture (QAN‑Agent, QAN‑API, QAN‑APP), describes the MySQL slow‑log format and key fields, and details the web UI metrics such as Load, Count, Latency, and additional diagnostic information for database performance analysis.
This article explains why using indexes does not guarantee fast queries, explores the relationship between index usage and slow‑query logs, and presents practical MySQL optimization techniques such as improving index selectivity, reducing table‑lookup costs, and leveraging virtual columns.
This article explains how to identify slow MySQL queries using the slow‑query log and SHOW PROCESSLIST, configure logging parameters, and analyze query performance with EXPLAIN, covering key output fields, common values, and practical code examples for effective SQL optimization.
This guide shows how to generate hourly slow‑query reports from MySQL slow‑log files by using pt‑query‑digest’s timeline feature, filtering with sed, importing the data into SQLite with termsql, and then querying the results to identify hotspot periods and recurring patterns.
A production MySQL query on a table with five million rows took over 30 seconds, and after reproducing the issue, examining execution plans, trying various index and query tweaks, discovering that SQLyog adds a hidden LIMIT, and finally forcing the correct index, the runtime dropped to under a second, illustrating the importance of proper index usage and tool‑specific quirks.
This article documents a MySQL slow‑query case where a 5‑million‑row table took over 30 seconds to run a GROUP BY query, explores several ineffective optimization attempts, reveals a client‑side limit issue, and finally solves the problem by forcing the correct index, cutting the runtime to under 0.2 seconds.
This article explains why using an index does not guarantee a fast query in MySQL, analyzes how slow‑query detection works, demonstrates full‑index scans, discusses index selectivity, the cost of row‑lookup (back‑table) operations, and shows how virtual columns and index condition push‑down can improve performance.
This article examines a MySQL instance that hit 100% CPU at 9 am on 2019‑01‑11, identifies the offending ORDER BY query through slow‑log and EXPLAIN analysis, and demonstrates how adding a composite index and correcting implicit type conversions reduced execution time from over 10 seconds to milliseconds.
A MongoDB shard node’s logs unexpectedly turned into PCX image files due to improper log rotation using echo, causing CPU spikes and slow‑query analysis failures; the investigation reproduces the issue, explains the header corruption, and presents a SIGUSR1‑based solution to restore proper ASCII logs and retrieve the needed time‑range data.
This article explains how to identify slow MySQL queries using the slow‑query log and SHOW PROCESSLIST, then demonstrates how to analyze their execution plans with EXPLAIN, covering key fields, common values, and practical examples for effective performance optimization.
This article presents a step‑by‑step analysis of several real‑world MySQL slow‑query problems, demonstrates how to reproduce the issues, examines execution plans, applies concrete optimizations such as removing function calls, adjusting date comparisons, adding bigint indexes, and explains the impact of different EXPLAIN types on performance.
This article investigates a MySQL slow query caused by a subquery that triggers a full‑table scan, explains MySQL's query rewrite behavior, and shows how Django's lazy ORM evaluation can worsen performance, offering practical fixes using explicit query execution or join rewrites.
This article examines a wide range of factors that can cause MySQL queries to run slowly—including network latency, OS and hardware bottlenecks, storage I/O scheduling, CPU power settings, RAID cache behavior, index design, implicit type conversion, execution plan errors, large data volumes, metadata locks, concurrent row updates, data skew, inefficient pagination, table schema choices, InnoDB dirty‑page flushing, and undo log retention—offering concrete examples and mitigation strategies for each scenario.
A detailed investigation reveals that a seemingly trivial SELECT * FROM sbtest1 LIMIT 1 becomes slow due to InnoDB's MVCC visibility checks, a growing History List length, and a large number of deleted rows that must be scanned before a visible row is found.
This article investigates why slow‑query logs show numerous slow queries without visible SQL statements, reproduces the problem using MySQL client, Perl and PHP simulations, compares local (emulated) and server‑side PDO prepare modes, and provides practical recommendations for configuring prepared statements to avoid hidden performance bottlenecks.
This guide walks through the background, installation, configuration, and usage of the open‑source Slowquery tool—integrating Percona pt‑query‑digest, Soar, and email alerts—to automate MySQL slow‑query monitoring and performance recommendations.
To resolve a MySQL single‑core CPU bottleneck, examine and tune slow queries and indexes, eliminate heavy lock contention, break large transactions into smaller ones, and employ tools like perf top to pinpoint performance hotspots, as recommended by Tencent Cloud Database expert Liu Di.
The article describes the architecture, core functions, and workflow of a MySQL slow‑query analysis system built for the CloudDB platform, covering both daily report generation and real‑time slow‑SQL monitoring using tools such as pt‑query‑digest, ELK, and Kafka.
This article explains how QA engineers can identify and analyze MySQL slow query logs, configure slow query settings, and apply index optimization principles to improve database performance, providing practical commands, guidelines, and tool recommendations.
This article explains the root causes of MySQL’s “Too many connections” error—including max_connections limits, sleep connections, and slow queries—provides detailed parameter descriptions, shares real‑world troubleshooting cases, and offers practical solutions such as killing idle sessions, adjusting wait_timeout, and tuning InnoDB concurrency settings.
This article walks through practical MySQL optimization techniques—including configuration file tuning, key InnoDB parameters, effective index design, and the use of Percona and MySQL Tuner tools—to help you speed up queries and eliminate performance bottlenecks in production environments.
The article investigates why a seemingly indexed MySQL DELETE query still performs a full‑table scan, analyzes execution plans and statistics, demonstrates forcing the index, and finally rewrites the query to limit the date range, achieving a much faster operation.
A recent order‑fulfillment outage was traced to a MySQL query that used the primary‑key index because an ORDER BY id ASC LIMIT 350 forced a full scan, but tweaking the ORDER BY or removing it reduced execution time from 20 seconds to under one second.
This guide explains how to analyze MySQL performance with EXPLAIN statements, composite indexes, slow‑query logging, storage engine locking differences, and transaction settings, providing practical examples and detailed descriptions of each EXPLAIN output column.
This article explains MySQL’s various log files—including error log, slow query log, binary log and general query log—detailing how to locate, configure, view, and analyze them using MySQL commands, and how to adjust settings such as long_query_time and log_output for optimal performance.
The article critiques a popular MySQL tuning list, explains why many of its suggestions are misguided, and presents a more practical, performance‑focused checklist that covers benchmarking, schema design, indexing, query rewriting, and engine‑specific tricks.
This article explains how to identify and analyze MySQL slow queries using tools like SHOW VARIABLES, EXPLAIN, mysqldumpslow, and profiling, and provides practical recommendations for configuring server variables, optimizing indexes, and improving query performance through systematic testing and best‑practice SQL design.
The article explains why a MySQL UPDATE statement took five seconds by examining binlog entries, distinguishing slow‑query scans from lock‑waits, and using a Bash script to identify long‑running transactions that reveal hidden lock contention.
A production MySQL 5.5 instance suffered intermittent latency and bursts of slow queries, which were traced to an oversized enabled query cache that caused mutex contention during frequent DML operations, and disabling the cache resolved the issue.
The article examines how unpredictable "black‑swan" incidents caused by slow, high‑frequency queries can destabilize database systems and presents concrete MySQL optimization techniques for large pagination, join operations, and concurrent count(*) queries that reduced slow‑query volume by about ninety percent.
A front‑end login slowdown was traced to a MySQL UPDATE that ran for 40 seconds because a varchar column’s charset conversion forced a full‑table scan, and the article walks through the logs, tests, and the fix of unifying the column charset to UTF‑8.
This article explains MySQL index fundamentals, B+‑tree structures, disk I/O considerations, and practical steps for diagnosing and optimizing slow queries, illustrated with multiple real‑world examples and concrete indexing guidelines.
This comprehensive guide explains MySQL's various log types, how to view and configure error, general, slow, and binary logs, and details essential server variables for effective database administration and troubleshooting.
