1 views collected around this technical thread.
This article explains how to locate and analyze MySQL slow queries, understand index structures and types, apply best practices for index creation, handle large pagination, manage transaction isolation and MVCC, and implement master‑slave replication and sharding strategies for high‑performance databases.
By dropping a low‑selectivity composite index, adding targeted indexes on product_id‑sequence‑station_no and receive_time, and using online DDL with pt‑osc, the team reduced row scans and replication lag, then implemented batch deletions by primary‑key ranges, cutting delete time from minutes to seconds on a 100‑million‑row table.
This article examines six common MySQL indexing pitfalls—type mismatches, function usage, left‑prefix violations, implicit charset conversions, range query issues, and optimizer mis‑choices—and provides concrete SQL fixes and verification tools to improve query performance.
MySQL's Multi-Range Read (MRR) optimization reduces random disk I/O by scanning indexes, sorting keys, and retrieving rows sequentially, offering advantages like sequential data access and batch key processing, while outlining its limitations, applicable scenarios, usage conditions, and configuration via optimizer_switch variables and buffer settings.
This article examines MySQL slow‑query troubleshooting, explaining how the optimizer estimates costs, why indexes may be ineffective even when present, the impact of memory fragmentation, pitfalls of prefix indexes, index merging techniques, and additional resource‑related factors that can cause seemingly healthy SQL statements to become slow.
This article examines MySQL's cost estimation process, explains why added indexes may still be ineffective, discusses the impact of memory fragmentation, and presents practical techniques such as prefix index handling, index merging, and resource monitoring to improve slow‑query performance.
This article walks through a MySQL performance case study, showing how a slow sub‑query can be accelerated by adding single‑column and composite indexes, rewriting the query with EXISTS or JOIN, and applying best‑practice indexing strategies such as covering indexes and left‑most prefix rules.
The article explains how Elasticsearch 8.16.1 stores dense and sparse vectors using various file extensions, compares flat and HNSW index formats, shows how disabling doc‑values removes redundant column‑store copies, and demonstrates scalar and binary quantization—including a quantization‑only mode—that can cut storage to roughly 9 percent while preserving search accuracy.
This article demonstrates how to use MySQL's EXPLAIN statement to examine execution plans, interpret key fields such as possible_keys, key, rows, and Extra, and apply index adjustments—including adding and forcing indexes—to improve query performance while weighing sorting costs.
This guide explains why slow queries occur in high‑traffic applications, outlines practical ways to monitor and fix them, and dives deep into using MySQL's EXPLAIN command to analyze index usage, interpret its output columns, and systematically improve query performance.
This article presents nine comprehensive performance‑optimization strategies—including load balancing, sharding, read/write separation, caching, indexing, CDN, asynchronous processing, code refinement, and algorithm improvement—aimed at boosting the efficiency and scalability of large‑scale backend systems.
This article walks through a practical experiment where the AI‑driven assistant ChatDBA diagnoses and resolves a MySQL “Too many open files” error by collecting system metrics, analyzing slow queries, identifying index misuse, and proposing concrete configuration and query adjustments.
This article explains MySQL's cost‑based optimizer, demonstrates how to calculate query costs for full‑table, covering, ref and range scans using actual source‑code constants, and applies the model to resolve index‑conflict cases in a store‑goods table, offering practical optimization guidelines and future tool ideas.
This article explains MySQL's architecture, how to view storage engines and variables, demonstrates creating various indexes, describes why SQL optimization is needed, details the EXPLAIN plan keywords and index types, and provides practical best‑practice tips for single‑ and multi‑table query performance improvements.
The article examines why MySQL LIMIT pagination becomes slower on deeper pages of a 500,000‑row table, analyzes query execution plans, and presents three optimization strategies—ordered index filtering, subquery pagination, and join‑based pagination—to improve performance in real‑world scenarios.
This article presents a detailed investigation of a MySQL master‑slave instance suffering from nightly SLA alerts due to slow‑query latency, analyzes the root causes with pt‑query‑digest and execution plans, proposes index redesign, demonstrates backup and restore with mydumper, compares online DDL and pt‑osc, and finally shows batch‑delete techniques to reduce load on large tables.
This article examines a MySQL slow‑query incident caused by the optimizer using the primary clustered index instead of an appropriate secondary index, explains the underlying index structures, and presents solutions such as FORCE INDEX and migrating complex queries to Elasticsearch for long‑term performance improvement.
This article explains how applying machine learning to database operations—specifically AIOps for MySQL—can automate index recommendation by parsing SQL, extracting semantic and statistical features, generating candidate index combinations, and training an XGBoost model to predict optimal indexes, reducing reliance on manual DBA work.
Effective index design, including single‑table, foreign‑key, composite, and covering indexes, along with optimizer hints, can dramatically improve the performance of multi‑table queries in PHP‑MySQL applications, and the article provides practical examples and SQL statements for each technique.
This article analyzes the performance problems of LIMIT‑based pagination on massive MySQL tables and presents three progressively more efficient solutions—including a simple LIMIT approach, a tag‑record method using the last primary key, and a range‑limit method with cached min‑ID—along with best‑practice indexing recommendations to keep query latency in the tens of milliseconds even for tables containing billions of rows.