This article explains how MySQL calculates execution costs for different indexes using IO and CPU metrics, demonstrates with a sample table and queries, compares the optimizer's choices with actual performance, and shows how to use EXPLAIN and optimizer trace to fine‑tune query plans.
The article introduces sql_helper, an open-source command-line and web tool for MySQL 5.7/8.0 and MariaDB that automatically analyzes SQL statements to suggest index creation, explains its six-step workflow, provides usage examples, command-line parameters, and notes its assumptions and limitations.
This article presents twenty essential principles for optimizing MySQL indexes, covering topics such as the limitations of leading wildcard LIKE queries, the advantages of IN over UNION, handling negative conditions, composite index ordering, range queries, covering indexes, prefix indexes, and common pitfalls to avoid for better query performance.
This comprehensive guide explains 20 practical MySQL index optimization rules—including when NOT to use indexes, how to choose the right index type, and techniques such as prefix indexes, covering indexes, and query rewriting—to dramatically improve SQL query efficiency and reduce database load.
This article examines the most frequent reasons MySQL queries become slow—including missing or ineffective indexes, deep pagination, massive tables, excessive joins, IN‑list overload, dirty pages, ORDER BY file‑sort, lock contention, and hardware limits—while offering concrete optimization techniques and best‑practice recommendations.
This article explains how MySQL estimates the I/O and CPU costs of different indexes when executing COUNT(*) and other queries, demonstrates the calculations with a concrete example on a 100k‑row table, and shows why the optimizer sometimes picks a full‑table scan over a seemingly better index.
This article demonstrates how to generate millions of test rows in MySQL, analyzes the performance impact of deep pagination using LIMIT, explains why non‑clustered index lookups cause costly table scans, and presents two optimization approaches—sub‑query ID filtering and key‑set pagination—to dramatically reduce query latency.
This article demonstrates how to generate two million MySQL rows, examines the performance impact of large OFFSET values in LIMIT clauses, explains why non‑clustered indexes cause back‑table lookups, and presents two optimization strategies—using sub‑queries to fetch IDs and employing key‑based pagination—to dramatically speed up deep page queries.
This article walks through a practical SQL optimization workflow, showing how to detect problematic queries via system metrics and execution plans, analyze table structures and indexes, apply concrete ALTER statements and index changes, and iteratively verify performance improvements, illustrated with a detailed MySQL case study that achieved a twenty‑fold speed increase.
This article walks through a MySQL 5.6 scenario where a nested sub‑query to find students scoring 100 in Chinese took over 30,000 seconds, then demonstrates how adding single‑column and composite indexes, rewriting the query with EXISTS or JOIN, and analyzing execution plans dramatically reduces execution time to milliseconds while explaining index merge, covering indexes, left‑most prefix, and sorting optimizations.
This article walks through a hands‑on process for detecting problematic SQL statements, interpreting execution plans, and applying concrete optimizations such as index adjustments and data‑type fixes to dramatically improve query performance, illustrated with a real MySQL case study.
This article details a comprehensive approach to optimizing a large MySQL table, including slow‑query analysis with pt‑query‑digest, index redesign, backup and restore using mydumper, online DDL versus pt‑osc, and batch deletion techniques to reduce latency and storage fragmentation.
iOS WeChat’s full‑text search was upgraded by selecting SQLite FTS5, creating a VerbatimTokenizer with multi‑level delimiter support, optimizing table formats to cut index size by 30 %, improving batch index updates and parallel search logic, resulting in 40‑60 % faster query latency.
This article explains how to determine the optimal order of columns in MySQL composite indexes by analyzing B+Tree structure, index selectivity, and real‑world query performance, providing practical examples and SQL demonstrations to help developers create more efficient indexes.
This article explains why database performance monitoring becomes critical as data grows, outlines common optimization techniques, dives into MySQL's storage architecture and index structures, and shares three real‑world cases that illustrate how proper indexing and execution‑plan analysis can prevent severe latency and CPU spikes.
This article examines how MySQL uses secondary indexes to find the K‑th largest value, analyzes the O(N) time complexity caused by offset pagination, explains the underlying B+‑tree double‑linked list behavior, and presents three optimization strategies—including index covering, pagination redesign, and boundary prediction—to dramatically improve performance.
This article analyzes performance problems of a massive MySQL table, demonstrates how to use pt‑query‑digest and EXPLAIN to identify inefficient composite indexes, proposes index redesign, compares online DDL with pt‑osc for index rebuilding, and presents batch‑delete techniques to reduce SLA‑impacting latency.
This article walks through a real‑world MySQL slow‑query case, detailing analysis, index checks, character‑set alignment, forced index usage, and IN‑clause considerations that together reduced a 5‑second query to under one second.
This article investigates a recurring MySQL CPU‑100% alarm caused by a slow SELECT with ORDER BY id ASC LIMIT, explains why the optimizer mistakenly chooses the primary‑key index over a suitable composite index, and presents six practical experiments—including force‑index, ORDER BY tweaks, and LIMIT adjustments—that reliably restore optimal index usage.
Facing a surge in MySQL slow queries, Ctrip’s senior database engineers detail a comprehensive approach—including refined SQL release processes, deep execution‑plan analysis, index tuning, query rewriting, and resource management—to systematically identify, diagnose, and eliminate performance bottlenecks in large‑scale hotel services.
This article analyzes a large MySQL table with frequent SLA alerts, demonstrates how to identify slow queries using pt‑query‑digest, proposes index redesign and online DDL versus pt‑osc methods, and shows testing, implementation, and batch‑delete techniques to dramatically reduce query time and maintenance overhead.
This article walks through diagnosing slow‑query problems on a massive MySQL table, reveals index misuse, proposes dropping the old composite index, adding targeted indexes, using online DDL or pt‑osc for schema changes, and applying batch‑delete techniques to reduce latency and storage fragmentation.
This article details a comprehensive performance investigation of a massive MySQL table, using pt‑query‑digest to analyze slow queries, exposing index inefficiencies, proposing index redesign, demonstrating backup/restore with mydumper, comparing online DDL and pt‑osc for index rebuilding, and recommending small‑batch deletions to reduce latency and maintenance overhead.
This article analyzes a MySQL large‑table performance issue caused by slow queries and full‑table scans, demonstrates using pt‑query‑digest, explains execution plans, proposes index redesign, shows backup and restore with mydumper, compares online DDL and pt‑osc, and presents batch delete strategies to reduce latency.
This article details a comprehensive analysis of a large MySQL table's performance issues, including slow query diagnostics with pt‑query‑digest, index evaluation, backup and restore procedures, online DDL versus pt‑osc, and practical strategies for index redesign and batch deletions to reduce latency and maintenance overhead.
This article walks through a real‑world incident caused by deep pagination in a MySQL‑based system, detailing the timeline of symptoms, root‑cause analysis, and a series of optimizations—including ID‑based queries, range scans, and rolling cursor techniques—that ultimately restored stable performance.
This article explains why using auto‑increment primary keys in a distributed database leads to collisions and performance issues, and presents practical strategies such as ordered UUIDs, embedding shard keys, index tables, and global table replication to achieve efficient, scalable index design.
This article presents a comprehensive guide to optimizing Elasticsearch for massive datasets, covering Lucene fundamentals, index and shard architecture, practical performance‑tuning techniques, and real‑world case studies that achieve sub‑second query responses on billions of records.
This article explains why a MySQL UNIQUE index does not prevent duplicate rows when indexed columns contain NULL, explores the complications of logical‑delete tables, and presents practical solutions such as incrementing delete status, adding timestamps, delete‑id keys, hash fields, and proper batch inserts.
This article walks through a MySQL scenario where a nested sub‑query runs for hours, demonstrates how adding single‑column and multi‑column indexes, rewriting the query as a join, and analyzing execution plans can reduce execution time from thousands of seconds to a few milliseconds.
A backend engineer shares a step‑by‑step case study of reducing a batch‑score query API from 20 seconds to under 500 ms by analyzing the problem, adding a composite index, introducing multithreaded CompletableFuture calls, and limiting batch size with pagination and thread‑pool tuning.
This article explains Bilibili's lake‑warehouse integrated architecture, describing how Iceberg, MagnuS, Trino, and Alluxio are used to achieve flexible data storage, high‑performance query acceleration, and automated indexing through Z‑Order, Hilbert curve, Bloom filter, and advanced BitMap techniques.
This article explains why MySQL queries can become sluggish, covering the full query execution flow, profiling techniques, index pitfalls, connection‑pool limits, buffer‑pool sizing, and additional performance tricks, while providing concrete commands and code examples for each optimization step.
This article walks through a MySQL 5.6 scenario where a subquery that originally took over eight hours is accelerated by orders of magnitude through strategic index creation, query rewriting, and join optimization, illustrating the impact of single‑column, composite, and covering indexes on execution plans.
This article walks through a MySQL scenario with large tables, demonstrates why a sub‑query that took over eight hours runs in just a second after adding appropriate single‑column and composite indexes, and explains how to read and interpret execution plans for further tuning.
This article enumerates twelve typical reasons why MySQL queries become slow—such as missing or ineffective indexes, deep pagination, massive tables, excessive joins, large IN lists, dirty pages, file‑based ORDER BY, and hardware limits—and provides concrete SQL examples and step‑by‑step optimization techniques to resolve each issue.
This article explores the various reasons MySQL queries become slow—including index misuse, insufficient connection pools, small InnoDB buffer pools, and query cache limitations—while providing practical solutions such as optimizing indexes, adjusting max_connections, enlarging buffer pools, and configuring connection pools for better performance.
This article walks through the complete lifecycle of a MySQL SELECT statement, from establishing the TCP connection and authentication, through query cache, parsing, optimization, and execution, detailing how the server and storage engine collaborate and explaining concepts like index usage, connection handling, and memory management.
This article recounts a production incident caused by a MySQL query with a huge OFFSET, explains why large offsets lead to full‑table scans and severe latency, and presents three practical solutions—including index‑covering subqueries, start‑position redefinition, and offset throttling—along with data‑generation scripts and performance test results.
This article explains typical MySQL index failure cases such as left‑most matching truncation, implicit conversion, IN + ORDER BY, range queries, leading wildcard, OR conditions, function usage, NOT IN/!=, low selectivity, and index fragmentation, and provides practical optimization strategies including index redesign, covering indexes, and pagination improvements.
This article shares hands‑on experience with MySQL 5.7 slow‑query problems, explaining common causes, proper index design, pitfalls that invalidate indexes, efficient SQL writing, deep‑pagination avoidance, and how to use EXPLAIN to verify that queries leverage the right indexes.
This article walks through ten typical scenarios where MySQL indexes fail—covering left‑most prefix violations, SELECT *, calculations, functions, type mismatches, LIKE patterns, column comparisons, OR, NOT IN/NOT EXISTS, and ORDER BY—showing why each occurs and how to keep indexes effective.
This comprehensive guide walks through MySQL performance fundamentals, real‑world testing results, slow‑query detection, index impact, pagination pitfalls, count optimizations, sharding techniques, and step‑by‑step migration plans for large tables, equipping engineers with actionable tuning insights.
This article explains why SQL queries become slow in MySQL, describes the server architecture, details how to use the EXPLAIN statement to interpret execution plans, and outlines how to read and optimize key fields such as type, key, and extra for better performance.
This article explains why MySQL queries become slow, how index design and common pitfalls affect performance, introduces MDL locks and large‑table strategies, and then compares Elasticsearch and HBase as complementary solutions for fast search and massive data storage.
This article demonstrates how to create and use a sample employees table, explains composite index structures, and provides ten concrete MySQL index best‑practice guidelines—covering full‑value matches, leftmost‑prefix rules, avoiding functions, range conditions, covering indexes, inequality, NULL checks, leading wildcards, type conversions, and OR usage—each illustrated with EXPLAIN output and code examples.
This article explains MySQL index fundamentals, the B+Tree structure, various index types, the concept of covering indexes to avoid row look‑ups, the left‑most prefix rule, and presents three real‑world optimization cases that illustrate how to design effective composite indexes, reduce index size, and improve query performance while avoiding costly table scans and sorts.
This article explains MySQL index fundamentals, B+‑tree implementation, the left‑most prefix rule, back‑to‑table issues, and provides three practical optimization cases with code examples to help developers design efficient composite indexes and avoid performance pitfalls.
This article explains MySQL index fundamentals, different index structures, the concept of covering indexes and the left‑most prefix rule, and presents three real‑world optimization cases that illustrate how to design effective composite and partial indexes to avoid table scans and improve query performance.
This article describes how a top architect optimized Elasticsearch for handling billions of records, covering Lucene fundamentals, index and shard design, DocValues, query performance tuning, bulk indexing strategies, hardware considerations, and testing methods to achieve sub‑second query responses across multi‑year data ranges.
This article presents a comprehensive guide on optimizing Elasticsearch for large‑scale data platforms, covering Lucene fundamentals, index and shard architecture, doc‑values usage, routing strategies, practical performance‑tuning techniques, and real‑world testing results to achieve sub‑second query responses on billions of records.
This article explains what constitutes a MySQL slow query, why they cause failures, defines quantitative metrics such as micro‑average and macro‑average to assess severity, outlines target goals, presents concrete optimization examples, and shares operational practices for ongoing slow‑SQL governance.
This article explains why a query that uses an index can still be slow, demonstrates how to diagnose index usage and row scans with EXPLAIN and the slow query log, and presents optimization techniques such as composite indexes, index condition pushdown, and virtual columns.
This article explains how MySQL processes ORDER BY clauses, illustrates the execution steps with a full‑field index, discusses the impact of sort_buffer and max_length_for_sort_data, and shows how adding composite and covering indexes can eliminate filesort and improve query performance.
This article analyzes common causes of slow MySQL queries such as index misuse, MDL locks, and large‑table bottlenecks, then presents practical solutions like proper indexing, sharding, read/write splitting, and evaluates when to complement MySQL with ElasticSearch or HBase for better performance.
This article explains why using SELECT * in MySQL queries hurts performance, explores the underlying reasons such as extra data transfer, I/O overhead, and loss of covering index optimization, and provides practical guidance on index usage and best practices.
The article explores why MySQL pagination with large offsets is slow, explains index structures such as clustered and secondary indexes, describes logical query operators, and presents two practical optimization strategies to improve query performance.
This article explains MySQL's ORDER BY execution process, comparing full-field sorting with rowid sorting, detailing how sort_buffer, temporary files, and index usage affect performance, and showing how composite and covering indexes can eliminate sorting altogether.
This article explains why defining columns as NOT NULL in MySQL improves query optimization, reduces index complexity and storage overhead, and avoids unexpected behavior in aggregates and calculations, while also covering default values, row formats, and practical testing results.
This article explores why MySQL queries with large LIMIT‑OFFSET pagination become painfully slow, explains the underlying B+‑tree index mechanics and MySQL's logical operator layers, and presents two practical solutions—key‑based pagination and index‑covering queries—to dramatically improve performance.
This article presents a comprehensive set of mandatory and recommended MySQL database standards covering table creation, SQL writing, and index design, explaining the rationale behind each rule to improve performance, maintainability, and consistency across projects.
A developer discovers that massive OFFSET values in MySQL pagination cause full‑table scans and severe latency, then reproduces the issue with simulated data, benchmarks the slowdown, and presents three practical solutions—including index‑covering subqueries, cursor‑based pagination, and offset limits—to restore query performance.
This article explains the architecture and core concepts of Elasticsearch and Lucene, outlines the requirements for cross‑month and high‑speed queries on massive datasets, and provides detailed index and search performance tuning techniques—including bulk writes, shard routing, doc‑values management, and pagination strategies—to achieve sub‑second response times on billions of records.
This article examines a slow MySQL query that scans millions of rows, explains why the optimizer chose a low‑selectivity index, demonstrates how forcing the correct index dramatically improves performance, and shows how to create a covering composite index to resolve the issue permanently.
The article explains why SELECT * slows down MySQL queries by increasing parsing cost, network traffic, and preventing index covering, and demonstrates how explicit column selection and well‑designed composite indexes can dramatically reduce I/O and improve query speed.
This article walks through a MySQL 5.6 case study where a nested sub‑query for finding students scoring 100 in a specific subject runs for hours, then demonstrates how adding single‑column and composite indexes, rewriting the query as a join, and analyzing execution plans can shrink the runtime from tens of thousands of seconds to under a second.
This article walks through a MySQL 5.6 scenario where a query that originally took over 30,000 seconds is optimized using proper indexing, sub‑query to join conversion, and composite indexes, demonstrating dramatic performance gains and offering practical tips for future query tuning.
This article explains common misconceptions about index selectivity and filtering, demonstrates how composite indexes can avoid slow queries, and shows why order‑by may still trigger filesort, providing practical tips and SQL examples for better MySQL index design.
This article explains the severe impact of MySQL slow queries, shows how to enable and configure the slow query log, demonstrates using EXPLAIN to analyze query execution, and provides concrete optimization techniques for indexes, pagination, and common pitfalls.
This guide explains the conditions under which MySQL can use indexes for range scans, LIKE patterns, BETWEEN clauses, and sorting, detailing key_len calculation, index key vs. filter vs. table filter, and practical examples to demystify index utilization.
This article presents twenty practical rules for optimizing MySQL indexes—including when to use or avoid indexes, how to write queries that can leverage them, the importance of index selectivity, prefix indexes, covering indexes, and common pitfalls—to help developers write faster, more efficient SQL statements.
This article outlines twenty practical principles for optimizing MySQL indexes, covering topics such as LIKE wildcards, UNION/IN/OR usage, negative conditions, composite index prefix rules, range queries, function avoidance, covering indexes, prefix indexes, pagination strategies, and common misconceptions, all illustrated with SQL examples and performance tips.
The article explains why using NULL columns in MySQL can lead to extra storage, index complications, query errors, and performance degradation, and provides concrete examples and best‑practice recommendations to prefer NOT NULL constraints whenever possible.
This article explores key database design decisions—including the benefits and drawbacks of auto‑increment primary keys, the trade‑offs of redundant columns, optimal index counts, and when to choose VARCHAR versus CHAR—offering practical guidelines to improve performance, storage efficiency, and maintainability.
This article explains common MySQL performance problems such as missing indexes and lack of pagination, and provides practical guidance on data size limits, concurrency settings, query time targets, table design, index types, and a variety of SQL optimizations with concrete code examples.
This article walks through diagnosing severe SLA alerts caused by slow queries on a massive MySQL table, analyzes query patterns with pt‑query‑digest, redesigns indexes, applies online DDL and pt‑osc for index changes, and demonstrates batch‑delete techniques that dramatically reduce execution time and replication lag.
This guide outlines comprehensive MySQL best practices covering naming conventions, table engine and charset choices, field type selection, index design principles, common index column recommendations, ordering of composite indexes, avoidance of redundant and foreign‑key constraints, SQL development rules, and operational guidelines for large‑scale data handling.
This article details the evolution and optimization of Ctrip’s vacation search engine, covering business challenges, indexing redesign, data collection pipelines, write‑path improvements, compression techniques, query performance enhancements, deployment strategies, and the resulting gains in storage, latency, and stability.
This article explains why using SELECT * in MySQL queries reduces performance, detailing increased parsing cost, network overhead, I/O for large columns, and loss of covering index optimization, and also expands on index concepts such as composite indexes and their impact on query efficiency.
Although many developers hear the advice to avoid using SELECT * in SQL, this article delves into the underlying reasons—such as increased parsing cost, unnecessary data transfer, extra I/O, and loss of covering-index optimization—while also explaining index concepts and best practices for MySQL performance.
This article examines how MySQL estimates execution costs for queries, explains why COUNT(*) can be optimal, demonstrates index selection with real‑world examples, and shows how optimizer_trace can reveal mismatches between estimated and actual performance.
This article presents a comprehensive guide to MySQL performance improvement, covering maximum data volume, connection limits, query latency targets, practical implementation principles, optimal table design, index classification and tuning, as well as detailed SQL optimizations such as batch processing, operator rewriting, and pagination techniques.
This article analyzes a MySQL master‑slave instance with a massive arrival_record table, identifies slow‑query causes using pt‑query‑digest and tcpdump, proposes index redesign, demonstrates backup/restore with mydumper, compares online DDL and pt‑osc, and presents batch‑delete strategies that dramatically reduce SLA‑triggering latency.
The article examines why MySQL queries become slow, detailing system‑level issues such as network latency, I/O bottlenecks, and CPU settings, alongside database‑level problems like missing indexes, type conversion, poor execution plans, lock contention, skewed data, and inefficient SQL patterns, and offers optimization strategies.
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 MySQL index design principles, demonstrates the execution steps of a range query on a sample table, and discusses common index optimizations such as covering indexes, the left‑most prefix rule, index condition push‑down, implicit type conversion, as well as why the optimizer may choose a wrong index and how to correct it.
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 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.
An in‑depth guide walks through Elasticsearch’s underlying Lucene architecture, explains shard routing and DocValues, then presents concrete index‑ and search‑performance tweaks—bulk writes, refresh intervals, memory allocation, SSD usage, field mapping, pagination strategies—and shows benchmark results that reduce query latency to seconds for billions of records.
This article explains how MySQL composite (multi‑column) indexes work, the left‑most prefix rule, demonstrates queries where the index is used or ignored, and lists common situations that cause index failure along with practical optimization tips.
This article presents a comprehensive guide to optimizing Elasticsearch for massive data volumes, covering Lucene fundamentals, index and shard design, practical performance‑tuning techniques, and real‑world testing results that enable cross‑month queries and sub‑second response times.
This article analyzes the performance bottlenecks of a MongoDB‑based CMDB under high write concurrency, explains index structures and storage, demonstrates how to identify and eliminate full‑table scans and redundant multikey indexes, and shows the resulting reduction in slow‑log volume and CPU usage.
The author describes a sudden 30,000‑second slowdown in a simple MySQL join query, details systematic diagnostics—including network, server load, and query testing—and ultimately discovers a missing index on the user_info table, restoring it to bring response times back to sub‑second levels.
This article investigates Alibaba's rule against joining more than three tables by designing MySQL and Oracle experiments that generate massive student‑teacher‑course data, run multi‑table join queries, compare indexed versus non‑indexed scenarios, and reveal the performance limits that drive the guideline.
This article walks through a real‑world MySQL scenario, showing how a slow sub‑query can be accelerated by adding single‑column indexes, converting the query to a join, and finally creating a composite index, with performance numbers and execution‑plan analysis to illustrate each optimization step.
This article analyzes why MySQL LIMIT pagination becomes increasingly slow with larger offsets, presents six optimization techniques—including using primary keys, covering indexes, prepared statements, subqueries, and composite indexes—and provides experimental results that demonstrate dramatic performance improvements.
This article examines why MySQL LIMIT pagination becomes slower as the offset grows, presents six practical methods—including direct LIMIT, primary‑key indexing, ordered index scans, prepared statements, sub‑queries, and joins—provides benchmark results on large tables, and distills actionable guidelines for designing covering and composite indexes to achieve fast, stable pagination.
This article walks through MySQL's ORDER BY execution, detailing the full‑field sorting process, when MySQL switches to row‑id sorting due to large row sizes, and how composite and covering indexes can optimize or eliminate the sorting step.
This article demonstrates how to dramatically improve MySQL query performance by analyzing execution plans, adding appropriate single‑column and composite indexes, converting subqueries to EXISTS or JOINs, and leveraging index coverage and ordering, with detailed examples and timing results.
