GaussDB for MySQL introduces a Limit‑Offset pushdown optimization that moves offset calculation to the InnoDB storage engine, eliminating unnecessary row transfers and index‑back‑table lookups, thereby dramatically reducing query latency for large offsets as demonstrated by TPC‑H benchmarks.
This article explains the MySQL Index Merge access method, details the underlying merge‑sort algorithm, enumerates the three index‑merge strategies, analyzes source‑code snippets, presents a real‑world case where index merge hurts performance, and offers practical optimization recommendations.
This article presents a typical multi‑layer system architecture, examines the theoretical performance limits of each component—including load balancers, HTTP servers, Redis, message queues, relational databases and network bandwidth—and evaluates whether the combined stack can sustain one million concurrent requests.
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 analyzes why a scalar subquery in an OceanBase 3.2.3.3 SQL statement runs over 1000 seconds, explains the execution plan details, and presents a rewrite using WITH and LEFT JOIN to replace SUBPLAN FILTER, reducing cost from 788 million to 3.6 million and execution time to 10 seconds.
GaiaDB 4.0 introduces a zero‑ETL parallel query engine that leverages multi‑core CPUs to run OLTP and OLAP workloads on a single cluster, dramatically improving complex query performance while offering configurable parallelism and a rich set of parallel operators.
This article explains why slow queries occur in high‑traffic applications, outlines effective index‑optimization steps, and demonstrates how to use MySQL's EXPLAIN command to analyze execution plans, interpret each column, and improve database performance.
This article compares MongoDB’s document‑oriented storage with PostgreSQL’s relational model for generative AI workloads, detailing how JSON, JSONB, and BSON affect insert and read performance across a series of benchmarks on identical hardware.
During a load‑test where QPS and TPS appear normal, the CPU usage climbs unexpectedly, leading to a step‑by‑step investigation that reveals excessive row reads caused by accumulating task records, and presents practical fixes and deeper insights into database read behavior.
CPU utilizationDatabase PerformanceSQL Optimization
This article explains nine common reasons why MySQL indexes become ineffective and offers concrete solutions—including query rewrites, data type alignment, index redesign, and maintenance tips—to help developers improve database query performance.
This article recounts a costly MySQL data‑migration failure caused by an unchecked INSERT INTO SELECT operation, analyzes why full‑table scans and transaction isolation led to payment‑record loss, and offers practical safeguards such as proper indexing and careful use of the statement.
This article recounts a real-world MySQL data migration incident where using INSERT INTO SELECT caused out‑of‑memory errors and data loss, analyzes why full table scans and transaction locking led to failures, and offers practical recommendations such as indexing and avoiding bulk inserts for large tables.
Database PerformanceINSERT INTO SELECTdata migration
A developer’s costly mistake using MySQL’s INSERT INTO SELECT for large data migration led to OOM, full-table scans, and lost payments, revealing how transaction locks and missing indexes can cause production failures and how to prevent them.
A large‑scale MySQL table migration using INSERT INTO SELECT caused hidden full‑table scans and lock contention, leading to data loss and a costly OOM incident, but adding the proper index and understanding transaction isolation prevented the failure.
Optimizing SQL query performance hinges on confirming that the database engine fully leverages indexes, and the EXPLAIN statement provides a detailed execution plan—including key_len values—that shows whether each index field is completely used, enabling developers to identify and fix inefficient index usage.
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 traces the evolution of tree‑based index structures—from simple binary search trees through AVL and red‑black trees to B‑trees and B+‑trees—explaining their strengths and limitations and why MySQL adopts B+‑trees to minimize disk I/O and boost query performance.
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.
This guide explains the step‑by‑step SQL execution sequence, offers practical tips for optimizing multi‑table joins, and provides comprehensive indexing recommendations to improve query performance and reduce resource consumption.
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 guide explains why batch processing is essential for API tuning and provides step‑by‑step techniques—including bulk database operations, request merging, pagination, parallel execution, caching, and monitoring—backed by concrete Java code samples and SQL queries to help engineers dramatically improve throughput and latency.
This article presents nine practical MySQL index optimization techniques—from selecting high‑selectivity columns and avoiding functions on indexed fields to applying the leftmost‑prefix rule, handling NULLs, using proper LIKE patterns, and leveraging EXPLAIN and LIMIT 1 for faster queries.
This article introduces MySQL and PostgreSQL, compares their architectures, presents benchmark results showing PostgreSQL's superior SELECT, INSERT, and UPDATE performance, discusses suitable application scenarios, and summarizes the strengths and weaknesses of each database for informed selection.
The article examines why MySQL performance drops sharply when a single table exceeds tens of millions of rows, debunks the index‑depth myth, explains SMO concurrency limits in InnoDB, and shows how B‑Link Tree indexes and heap‑organized tables can overcome these bottlenecks.
The article demonstrates through a practical experiment that MySQL will use indexes for an OR condition only when both operands are indexed, otherwise it falls back to a full table scan, and it suggests using UNION as an optimization when one side lacks an index.
This article analyzes a slow Join query on OceanBase 3.2.3.3, examines execution plans and table statistics, proposes two rewrite methods—adding a filter condition and using a hash‑join hint—and demonstrates how these changes reduce execution time from over 500 seconds to just a few seconds.
This article examines the technical reasons why MySQL single tables struggle with massive data volumes, debunks the index‑depth myth, explains InnoDB's SMO concurrency bottlenecks, and shows how B‑Link Tree indexes and heap‑organized tables in GaussDB provide superior performance for large‑scale workloads.
This article compares MySQL and PostgreSQL, covering their histories, architecture differences, benchmark results, suitable application scenarios, and a concise summary of PostgreSQL's advantages and disadvantages relative to MySQL.
The article explains Java's PreparedStatement interface, highlighting its performance, security, and batch processing benefits, and provides a complete example of inserting multiple user records efficiently, while also discussing practical considerations such as batch size, transaction handling, error management, and optimization tips.
This article explains why slow queries occur in high‑traffic MySQL databases, introduces the EXPLAIN statement, walks through its syntax and output columns, demonstrates how to interpret each field with concrete examples, and provides a practical workflow for optimizing indexes to eliminate performance bottlenecks.
This article explains the concept, principles, performance benefits, and trade‑offs of MySQL indexes, illustrating with a 200‑million‑row example that shows how indexing reduces query time from days to under a second compared to full table scans.
This article compares the performance of JOIN and IN in MySQL queries, explains their basic syntax, provides example code, discusses scenarios where each is preferable, and offers additional optimization tips such as indexing, schema design, and engine selection.
Oracle’s MySQL 8.2 introduces built‑in read/write splitting via MySQL Router, allowing write traffic to target primary instances while reads are automatically routed to secondary replicas, with configuration examples, command‑line bootstrap, routing settings, and practical observations on transaction behavior.
This article explains the characteristics of various MySQL join algorithms—including Simple Nested‑Loop, Index Nested‑Loop, Block Nested‑Loop, Hash Join, and Batched Key Access—demonstrates how to examine execution plans, configure optimizer settings, and apply best‑practice optimizations such as indexing, choosing the small driver table, and upgrading MySQL versions.
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 provides an in‑depth tutorial on MySQL performance tuning, covering basic architecture, various index types, the EXPLAIN execution plan, common pitfalls, and practical optimization techniques with numerous SQL examples and code snippets.
This article explains MySQL InnoDB's lock mechanisms and index structures, covering primary and secondary indexes, record, gap, and next‑key locks, and how they affect query performance and transaction isolation in high‑concurrency environments.
Running ANALYZE TABLE on MySQL, Percona Server, or MariaDB can block queries in older versions, but from MySQL 8.0.24, Percona Server 5.6.38+, 5.7.20+, and MariaDB 10.5.4+, the operation is safe and no longer causes disruptive locks.
This article details a multi‑phase slow‑SQL governance initiative that defines clear goals, metrics, and operational steps—from data preparation and ticket flow to trend analysis and process tracking—culminating in measurable improvements in system stability and performance.
Hot keys in Redis—high‑frequency accessed keys—can overload the cache and downstream databases, causing crashes; this article explains what hot keys are, why they arise, their risks, how to detect them, and practical mitigation strategies such as scaling clusters, using secondary caches, monitoring commands, and traffic analysis.
This article explains how using larger Linux page sizes—especially 2 MB hugepages—dramatically improves database throughput on Kubernetes nodes by reducing TLB cache misses, and provides practical guidance on configuring hugepages, disabling transparent hugepages, and sizing resources for optimal performance.
This comprehensive guide explores MySQL’s architecture, explains how queries are parsed and executed, and provides detailed techniques for optimizing SQL performance through effective indexing, understanding EXPLAIN output, and avoiding common pitfalls that cause index misuse and slow query execution.
The article explains that while MySQL foreign keys ensure data consistency and integrity, they introduce performance overhead, lock contention, and scalability issues—especially in high‑concurrency and sharded environments—leading many large internet firms to forego them in favor of application‑level solutions.
This article explains the fundamentals of MySQL locking—including explicit and implicit locks, transaction isolation levels, next‑key and gap locks, and practical session‑based examples—so developers can decide when to apply pessimistic locks and when to prefer safer alternatives.
This article explains MySQL's basic architecture, demonstrates how to view and set storage engines, and provides comprehensive guidance on SQL optimization methods, index types, creation, usage, and performance analysis using EXPLAIN, helping readers improve query efficiency and avoid common pitfalls.
The article explains MySQL’s explicit and implicit lock mechanisms—including SELECT … FOR UPDATE, metadata, row, gap, and next‑key locks—how isolation levels affect them, and offers practical guidelines such as using optimistic locking, indexing locked columns, and avoiding long transactions to prevent phantom reads, deadlocks, and performance issues.
This article explains MySQL InnoDB's various buffers—including the buffer pool, change buffer, log buffer, and doublewrite buffer—detailing their purposes, internal mechanisms such as LRU variants, configuration options, and how they improve performance and ensure data integrity.
This article provides an in‑depth overview of MySQL’s core architecture, storage engines, index types, and practical SQL optimization methods, followed by detailed explanations of the EXPLAIN plan keywords and numerous real‑world examples to help developers improve query performance and avoid common indexing pitfalls.
This article explains MySQL InnoDB’s key buffering mechanisms—buffer pool, change buffer, log buffer, and doublewrite buffer—detailing their roles, internal structures, eviction policies, and how they improve performance and data durability while handling page reads, writes, and crash recovery.
This article explains how Linux page size—from the default 4 KB to 2 MB or 1 GB huge pages—affects database performance, details the role of TLB cache hits and misses, presents benchmark results showing up to an eight‑fold throughput increase, and offers practical guidance for configuring huge pages on Kubernetes nodes.
The article explains why Doris needed a new optimizer, describes its architecture—including rule‑based and cost‑based stages, early data‑size reduction techniques, dynamic‑programming join‑reorder methods, and practical challenges such as statistics errors and runtime filters—while sharing performance results and a Q&A session.
This article explains MySQL's logical architecture, query execution flow, caching mechanisms, and detailed index design techniques, providing practical tips and examples so developers can understand the underlying principles and apply effective performance optimizations in real‑world applications.
This article discusses common MySQL performance pitfalls such as inefficient LIMIT usage, implicit type conversion, subquery updates, mixed ordering, EXISTS clauses, condition pushdown, and intermediate result handling, and provides optimized SQL rewrites and best practices to dramatically improve query execution speed.
This article walks through a real‑world incident where a pagination‑related slow‑query caused massive CPU spikes, details the step‑by‑step investigation, and presents several MySQL optimization techniques—including ID‑based queries, sub‑queries, and cursor‑based pagination—that ultimately resolved the performance crisis.
This comprehensive guide explains MySQL’s architecture, index fundamentals, the EXPLAIN plan keywords, step‑by‑step index creation and deletion commands, and numerous real‑world optimization examples, helping developers dramatically improve query execution speed while avoiding common pitfalls.
This article examines how Linux page size, especially the use of huge pages (2 MB and 1 GB), influences database throughput by reducing TLB misses, presents benchmark results, and offers practical guidance for configuring Kubernetes nodes to leverage large pages for optimal database performance.
The article examines why PostgreSQL still falls short of being a core enterprise database by analyzing eight technical gaps—such as XID64 support, LWLOCK efficiency, shared buffer performance, recovery, high availability, zero data loss, resource management, and CBO optimizer maturity—while reflecting on industry trends and Oracle's dominance.
This article experimentally verifies how SELECT FOR UPDATE behaves under MySQL 5.7 and 8.0 with both REPEATABLE‑READ and READ‑COMMITTED isolation levels, covering primary keys, unique indexes, ordinary indexes, no indexes and range queries, and summarizes when row‑level, gap‑level or table‑level locks are taken.
This article investigates a MySQL 8.0 TDSQL client experiencing connection interruptions during batch streaming, analyzes error 1161 (ER_NET_WRITE_INTERRUPTED) root causes in the MySQL source code, presents debugging steps with log and packet captures, and offers practical mitigation recommendations.
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.
Apache Doris 2.0-beta, released on July 3, 2023, introduces a new Cascades‑based query optimizer, adaptive pipeline execution engine, workload‑aware resource isolation, enhanced memory management, partial column updates, multi‑catalog support, and numerous performance gains across real‑time analytics, ETL, and high‑concurrency point queries.
This guide explains MySQL indexes—what they are, their data structures, advantages and drawbacks, when to create them, various index types, how to interpret EXPLAIN output, common pitfalls that cause index loss, and practical optimization techniques for queries, joins, ordering, grouping, and slow‑query logging.
This article explains the definition, data structures, advantages, disadvantages, and appropriate usage scenarios of MySQL indexes, details primary, unique, single‑column and composite index types, analyzes common performance bottlenecks with EXPLAIN, demonstrates index creation, invalidation cases, query and join optimizations, and shows how to configure and use the slow‑query log.
This article explains MySQL index fundamentals—including classification, B+‑tree and hash structures, clustered and secondary indexes, single‑ and composite‑column indexes, covering indexes, index condition pushdown, index merge strategies, cost‑based index selection, common index‑invalidating scenarios, and practical guidelines for creating effective indexes.
This article explains what indexes are, when they should be applied, and provides step‑by‑step MySQL commands—including single‑column, composite, and index‑hint examples—to improve query performance on large datasets.
The article explains why MySQL 8.x may ignore a primary‑key index and perform a full table scan for an IN subquery, describes the optimizer’s cost‑model reasoning, and shows how rewriting the query with an INNER JOIN restores index usage and dramatically improves performance.
This article explains how MySQL's lock_time metric is calculated by summing table‑level and row‑level lock wait times, details the InnoDB implementation, shows source‑code paths and functions involved, and clarifies why lock_time may appear small even when query execution is long.
A detailed postmortem shows how a poorly optimized SQL query, triggered by a cache‑removal update, saturated MySQL's CPU and connection pool, causing cascading failures across a sales‑critical system despite low overall traffic.
This article explains why certain Redis commands, persistence mechanisms, and large keys can block the single‑threaded server, detailing the underlying causes, how to detect big keys, and practical ways to mitigate blocking in production environments.
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 explains MySQL master‑slave replication fundamentals, the binlog‑based data flow, common consistency and latency problems, practical solutions such as row or mixed binlog formats, caching and query routing, and the trade‑offs of one‑master‑one‑slave versus one‑master‑many‑slaves architectures.
This article demonstrates MySQL query performance issues with large tables, shows how a subquery without indexes can take hours, and walks through step‑by‑step optimizations including adding single‑column indexes, converting to EXISTS and JOIN queries, creating composite indexes, covering indexes, and sorting improvements, achieving speedups of thousands of times.
This article summarizes Guo Bin's DTCC2022 talk on Google Cloud Bigtable, detailing its architecture, performance characteristics, scaling strategies, and how the fully managed NoSQL service powers low‑latency real‑time bidding and user profiling in modern advertising technology.
While containers and Docker dominate modern infrastructure, this article critically examines whether MySQL should be containerized, highlighting data safety, performance, statefulness, and resource isolation concerns, and offering practical strategies such as separating data from the program, using lightweight or distributed databases, and appropriate deployment choices.
The article explains how Oracle's built‑in auto‑task jobs run during specific weekday and weekend windows, why they can cause performance degradation by changing execution plans or consuming resources, and provides practical steps to diagnose, adjust windows, and selectively disable problematic tasks.
This article explains how to prepare and tune MySQL SQL statements before execution, covering logical and physical optimization, table and view analysis, join‑key checks, query rewriting, and execution‑plan evaluation to achieve faster, more efficient database queries.
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.
The article advises against using MySQL subqueries and joins for large datasets, recommending single‑table queries merged in application code, and outlines the benefits, scenarios, drawbacks, and alternatives for application‑level data association while also noting when joins can still be useful.
This article explains how DBAs can improve MySQL query performance by applying logical and physical optimizations, analyzing table structures, indexes, and execution plans, and provides a step‑by‑step checklist for diagnosing and rewriting slow or poorly optimized SQL statements.
The article analyzes why an identical MySQL query runs faster in a QA environment than in DEV, examines execution‑plan differences caused by table driving choices, optimizer settings, and unnecessary conditions, and demonstrates how rewriting the SQL and adjusting hints can achieve a stable, efficient plan.
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 typical MySQL out‑of‑memory (OOM) situations in production, explains how to diagnose memory usage with Performance Schema and other tools, and presents a series of TDSQL‑C‑specific optimization techniques—including server‑parameter tuning, process‑list monitoring, cold‑page detection, buffer‑pool limits, and dynamic resizing—to mitigate OOM risks.
This article recounts a real‑world MySQL pagination outage, detailing how deep‑page queries caused massive CPU spikes, the step‑by‑step investigation, and the series of SQL redesigns—including ID‑based range queries, sub‑queries, scroll queries and join‑based solutions—that finally restored stable performance.
This article provides a comprehensive technical walkthrough of MySQL‑8.0.22 InnoDB's buffer pool, detailing its physical and logical structures, page management algorithms, fast read/write optimizations, change buffer, adaptive hash index, and the complete page‑read call stack, complete with code excerpts and diagrams.
This article explains how ByteHouse, an enhanced version of ClickHouse used at ByteDance, adds full upsert support, multi‑table joins, high‑availability features, and, most importantly, a Resource Group mechanism that provides fine‑grained CPU, memory, and concurrency isolation to improve query performance and stability.
This article explains the process by which MySQL transforms uncorrelated IN subqueries into correlated subqueries, details the differences in EXPLAIN output columns, explores optimizer trace information, compares materialization versus correlated execution costs, and provides best‑practice recommendations for writing efficient queries.
This article explains MySQL 5.7’s enhanced multi‑threaded slave (MTS) parallel replication, contrasting it with the schema‑based approach of MySQL 5.6, detailing its group‑commit mechanism, configuration parameters, GTID handling, performance testing, and practical tuning tips for optimal replication throughput.
This article explains how ClickHouse achieves high query performance by leveraging storage‑engine designs such as pre‑sorting, columnar layout, and block‑level compression, and by exploiting a vectorized compute engine while avoiding joins and using built‑in functions.
An in‑depth look at BitalosDB, the home‑grown NoSQL storage engine behind Zuoyebang’s massive KV traffic, covering its novel IO architecture, KV‑separation design, Raft‑based consistency, multi‑cloud CRDT replication, and benchmark results that show up to 400% cost savings versus standard Redis.
This article explains the fundamentals of MySQL indexing, detailing how InnoDB pages, B+‑tree structures, clustered and non‑clustered indexes, and disk prefetching work together to reduce I/O operations and dramatically improve query performance.
This article presents a comprehensive set of MySQL optimization techniques, covering everything from avoiding SELECT * and OR conditions to proper index usage, join strategies, batch operations, and query planning, all aimed at reducing resource consumption, improving execution speed, and ensuring reliable database design.
This article explains the fundamental differences between row‑based and column‑based storage in databases, compares their write and read performance, lists pros and cons, presents an openGauss experiment with sample tables, and offers practical guidance on when to choose each storage model.
This article explains how MySQL’s InnoDB storage engine, page size, B+‑tree index structure, and primary‑key limits combine to make roughly 20 million rows the practical ceiling for a single table, and shows how to generate massive test data and tune server settings.
This article provides a systematic deep‑dive into MySQL table‑lock mechanics, comparing storage engines, explaining the advantages of table locks, detailing lock acquisition and release rules, showing how to monitor lock statistics, and revealing why MyISAM can deliver superior performance for heavy concurrent insert and select workloads despite using only table‑level locking.
The article reviews OceanBase's distributed relational database design, its integrated architecture, high‑compression LSM‑tree storage engine, Paxos‑enhanced two‑phase commit protocol, and how these innovations enabled the system to set successive world records in the TPC‑C benchmark, illustrating China's growing database capabilities.
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.
A comprehensive TPC‑H benchmark compares ClickHouse, Oracle, and the open‑source esProc SPL across simple and complex queries, showing ClickHouse excels at single‑table scans, while SPL consistently outperforms both in complex calculations and offers more concise code.
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 comprehensive guide walks you through MySQL's core architecture, explains why SQL optimization matters, details index types and creation methods, demystifies EXPLAIN output, and provides practical examples and best‑practice tips to dramatically improve query performance.