This article presents practical MySQL performance tuning methods, covering LIMIT pagination, implicit type conversion pitfalls, rewriting joins for updates/deletes, mixed-order sorting, replacing EXISTS with joins, predicate pushdown, early result limiting, and intermediate result pushdown, each illustrated with SQL examples and execution plan analyses.
This article provides a comprehensive technical guide on MySQL internals, covering the underlying data structures of indexes (B‑tree, B+‑tree, hash), how execution plans are generated and interpreted, the four transaction isolation levels, MVCC implementation, and the InnoDB buffer‑pool architecture.
This article explains how to clean up a rapidly growing MySQL log table by batching deletions, handling unindexed type columns, and using a start‑id based pagination strategy that scales from tens of millions to billions of rows without locking the database.
This article details a real-world performance optimization of a high‑concurrency Java backend interface, reducing response time from 30 seconds to under 0.8 seconds through SQL analysis, array aggregation, moving logic to PostgreSQL, and introducing Caffeine caching, with code examples and lessons learned.
This article explains how to troubleshoot a slow OceanBase stored procedure by locating its trace_id, analyzing audit logs to identify non‑cached UPDATE statements, revealing that preceding TRUNCATE operations invalidate the plan cache, and finally recommending replacing TRUNCATE with DELETE to restore performance.
This article details a real‑world performance case where a high‑concurrency interface processing over two million rows was diagnosed and optimized through SQL tuning, Java code refactoring, and caching, ultimately reducing response time from 30 seconds to under 0.8 seconds.
This article explains the challenges of high‑cardinality GroupBy operations, describes SLS's underlying implementation and session‑based optimizations, and presents three real‑world test cases that demonstrate up to an eight‑fold speed improvement for massive data aggregations.
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 guide covers practical MySQL performance improvements, including choosing proper data types, avoiding SELECT *, optimizing joins and UNION usage, batch inserts, query cache settings, configuration parameters like innodb_buffer_pool_size, and comprehensive index strategies to accelerate queries and reduce resource consumption.
This article presents a real‑world case study of a high‑concurrency data‑processing interface whose response time was reduced from 30 seconds to under one second by diagnosing SQL bottlenecks, refactoring MyBatis code, applying PostgreSQL array aggregation, and introducing a Caffeine cache, while also discussing the limits of relational databases and promoting related AI services.
This article provides a comprehensive overview of Apache Spark SQL, covering its fundamental concepts such as TreeNode, AST, and QueryPlan, the distinction between logical and physical plans, the rule‑execution framework, core components like SparkSqlParser and Analyzer, as well as the Spark Session, Dataset/DataFrame, and various writer APIs, supplemented by a detailed Q&A session.
This article provides a comprehensive guide to MySQL indexing, covering index data structures, maintenance mechanisms, query execution, optimization techniques such as left‑most prefix, index push‑down, unique indexes, index selection, and common pitfalls that cause index inefficiency.
This article presents a real‑world case study of a high‑concurrency transaction‑statistics API that originally took 30 seconds to respond, detailing problem diagnosis, SQL and Java code analysis, and a series of optimizations—including SQL rewrites, database‑side calculations, and Caffeine caching—that ultimately brought the response time below one second.
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.
To avoid common MySQL pitfalls, the article compiles practical SQL writing guidelines—selecting specific columns instead of *, using LIMIT 1, avoiding OR in WHERE, optimizing pagination, handling LIKE patterns, minimizing redundant indexes, preferring INNER JOIN, and leveraging covering indexes and EXPLAIN for efficient query execution.
This article presents a real‑world case study of a high‑concurrency transaction‑handling API that originally required 30 seconds to process over two million records, and details the step‑by‑step SQL, Java, and caching optimizations that brought the response time down to under one second.
This article introduces a collection of practical open‑source utilities—including SQLTXPLAIN, SOAR, SQLAdvisor, and Percona Toolkit tools—that streamline common MySQL DBA tasks such as schema changes, replication checks, slow‑query analysis, and data consistency verification, helping engineers work faster and more efficiently.
This article explains the concept, advantages, and drawbacks of materialized views, describes how MaxCompute implements them—including creation syntax, maintenance properties, automatic query rewrite, smart recommendation, and auto‑materialization—and shares performance results and future improvement plans.
This article explains how MySQL GROUP BY queries can be dramatically accelerated by using appropriate index scans, compares Loose Index Scan and Tight Index Scan, shows practical test data, execution plans, and provides guidelines for when each method is most effective.
This article analyzes a MySQL query that originally took nine seconds, identifies redundant joins and an EXISTS subquery on the same table, and demonstrates how restructuring the query eliminates duplicate scans, reducing execution time to 0.2 seconds.
WeTune 2.0, a revolutionary tool developed by Shanghai Jiao Tong University and GaussDB experts, automatically discovers and validates SQL rewrite rules, dramatically improving query performance in Huawei Cloud GaussDB by eliminating manual rule creation, supporting extensive rule enumeration, equivalence verification, and cost‑based evaluation, while addressing scalability challenges.
This article explains a distributed‑computing approach for generating a globally unique integer index from massive string datasets, replacing single‑reducer sorting with hash‑bucket partitioning and parallel processing to cut runtime from 30 minutes to just 2 minutes.
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 guide introduces a collection of practical MySQL DBA utilities—including SQLTXPLAIN, SOAR, SQLAdvisor, and Percona Toolkit commands such as pt‑duplicate-key-checker, pt‑kill, pt‑mysql-summary, pt‑query-digest, pt‑table-checksum, and pt‑table-sync—showing how to install them, run key commands, and interpret their outputs to streamline database administration tasks.
This article explains common MySQL performance pitfalls such as inefficient LIMIT pagination, implicit type conversion, sub‑query‑based updates, mixed ordering, misuse of EXISTS, and condition push‑down, and provides concrete rewrites and examples that reduce query times from seconds to milliseconds.
This article walks through how to analyze MySQL slow queries using EXPLAIN, explains key EXPLAIN fields, and presents practical optimization techniques such as proper indexing, pagination tricks, join improvements, and ORDER BY/GROUP BY tuning to dramatically boost query performance.
This guide outlines comprehensive database testing practices, covering relational and non‑relational types, CRUD validation, index and view checks, empty‑database scenarios, data integrity, design principles, stress testing, and detailed SQL optimization techniques with concrete examples and code snippets.
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 provides comprehensive guidance on effective database indexing, covering when and how to create various index types, practical SQL examples, strategies for equality, range, fuzzy, join, and composite queries, and tips for verifying index usage with EXPLAIN.
This article explores practical SQL optimization techniques for Alibaba's ODPS platform, covering fundamentals, common pitfalls like null handling and select *, advanced strategies such as multi‑insert, partition limiting, UDF placement, data‑skew mitigation, parameter tuning, and real‑world case studies that dramatically reduce query runtimes.
This guide covers practical MySQL performance improvements, detailing SQL-level optimizations such as limit pagination, proper LIKE usage, UNION ALL, EXISTS vs IN, TRUNCATE, batch inserts, early filtering, function placement, data type minimization, CHAR/VARCHAR choices, indexing strategies, force index, as well as server parameter tuning for buffer pool and redo log settings.
The article compares redundant and normalized storage, explains their trade‑offs, and introduces an in‑memory aggregation technique implemented via a repository "fill" method in Java, showing how to replace costly SQL joins with flexible, code‑driven data merging while preserving consistency and performance.
The article explains retention analysis models, their importance for user loyalty, outlines offline Hive architecture, then shows how ClickHouse’s retention() function and columnar storage dramatically speed up multi‑day retention calculations, providing SQL examples and practical guidance for product analytics.
This article explains how MaxCompute leverages materialized views as a query accelerator, covering their history, advantages and drawbacks, creation and maintenance details, automatic query rewriting, intelligent recommendation, auto‑materialization, and future enhancements for large‑scale data warehousing.
This article presents eight practical MySQL optimization methods—including smarter LIMIT usage, avoiding implicit type conversion, rewriting joins, handling mixed ordering, replacing EXISTS with joins, pushing down conditions, pre‑filtering data, and using WITH clauses—to dramatically reduce query execution time.
This article explores eight common MySQL performance pitfalls—from inefficient LIMIT usage and implicit type conversion to subquery joins, mixed ordering, EXISTS clauses, condition pushdown, early result limiting, and intermediate result set optimization—offering concrete rewrites and execution plan analyses to dramatically speed up queries.
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 comprehensive guide explains MySQL's architecture, the importance of SQL optimization, detailed index types and creation methods, how to interpret EXPLAIN output, and practical techniques to improve query performance while avoiding common pitfalls.
This article walks through a MySQL 5.6 scenario with course, student, and score tables, demonstrates why a naïve sub‑query is extremely slow, and shows step‑by‑step how adding single‑column, composite, and covering indexes, as well as rewriting the query to joins, can reduce execution time from hours to milliseconds.
Chat2DB is a free open‑source multi‑database client that adds AI‑driven natural‑language‑to‑SQL conversion, optimization suggestions, and team collaboration features, offering Windows, macOS, Linux, and web deployments along with Docker installation and detailed usage examples.
This article explains the differences between global and local indexes in OceanBase, analyzes why queries using global unique indexes run significantly faster than those using local indexes during Oracle migration, and provides detailed SQL examples, execution plans, and performance testing results to guide database optimization.
This article examines common MySQL performance pitfalls—such as large OFFSET limits, implicit type conversion, sub‑query updates, mixed sorting, EXISTS clauses, and condition push‑down—and demonstrates concise rewrites, including JOINs, UNION ALL, early range reduction, and WITH clauses that can reduce query execution from seconds to milliseconds.
This article introduces several MySQL performance‑tuning utilities—including mysqltuner.pl, tuning-primer.sh, and Percona Toolkit’s pt-variable-advisor and pt-query-digest—explaining how to download, run, and interpret their reports to optimize database configuration, memory usage, and query efficiency.
This article explains how to dramatically reduce the cost of deduplication‑Cube calculations in large‑scale data pipelines by replacing costly data‑expansion steps with a UID‑level tagging approach, detailing the scenario, common methods, performance analysis, a new solution, implementation steps, and experimental results.
When a system’s data grows, MySQL queries can become slow and cause latency; this article presents practical optimization strategies—including query tuning, pagination, join improvements, subquery replacement, order‑by and group‑by enhancements, and count() efficiency—illustrated with sample tables, code snippets, and performance screenshots.
This article explains how commercial banks can analyze MySQL lock‑wait events and transaction timeouts using MDL metadata lock monitoring, innodb_lock_wait_timeout, custom MyAWR collection, SQL de‑parameterization, and post‑mortem queries to pinpoint blocking SQL and its source.
This article reviews MySQL's execution process and presents a comprehensive list of practical SQL optimization strategies—including indexing, query rewriting, avoiding costly operators, using batch operations, and leveraging EXPLAIN—to improve database performance and maintainability.
This article explains why database sharding and partitioning become necessary, outlines performance bottlenecks, presents software and hardware optimization techniques, details horizontal and vertical splitting strategies, and discusses the added complexity such as cross‑database joins, distributed transactions, and ID generation.
This article demonstrates how to dramatically improve MySQL query performance by analyzing execution plans, adding appropriate single‑column and multi‑column indexes, rewriting subqueries as joins, and applying covering indexes, showing step‑by‑step measurements and visual explanations.
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.
This article explains how ByteHouse, a cloud‑native data warehouse, shifts traditional ETL to ELT, simplifies data pipelines, enhances scalability, and introduces advanced features such as stage‑by‑stage scheduling, adaptive resource management, async execution, and future roadmap for big‑data workloads.
The article walks through diagnosing a 10‑second backend loading delay, explains connection‑pool concepts, introduces Alibaba's Druid pool with its monitoring UI, shows how to identify and optimize slow SQL queries using DISTINCT and indexes, and provides step‑by‑step SpringBoot integration instructions.
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.
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 how computer storage works, why database indexes dramatically speed up queries, the mechanics of binary search, the differences between clustered and non‑clustered indexes, common pitfalls of over‑indexing, and practical SQL optimization strategies to avoid full table scans and index invalidation.
This article explains how to optimize OLAP workloads by balancing normalization and denormalization, applying data sharding, replication, indexing, partitioning, materialized views, columnar storage, compression, and lifecycle management, and provides practical ClickHouse SQL examples for index creation, partitioning, and query plan analysis.
This article explains how to use MySQL's EXPLAIN statement to analyze slow queries, detailing each output column, various select types, access methods, and common pitfalls, then provides practical optimization techniques such as proper indexing, query rewriting, index merging, handling COUNT, UNION, OFFSET, and table maintenance.
This article walks through the step‑by‑step JDBC query process, explains why wrapping JDBC in a framework like MyBatis is beneficial, analyzes MyBatis’s core components and execution flow, and presents five practical optimizations—from connection pooling to dynamic SQL and result caching—to improve Java backend performance.
Chat2DB is an open‑source, cross‑platform database client that integrates AI capabilities to translate natural language to SQL and vice versa, offers optimization suggestions, supports multiple databases, provides team collaboration features, and can be installed via native packages or Docker with detailed usage examples.
This article explains nine practical MySQL optimization methods—including smarter LIMIT usage, implicit conversion pitfalls, JOIN‑based updates, mixed ordering tricks, EXISTS rewrites, predicate pushdown, early range reduction, intermediate result pushdown, and a concise summary—showing how to transform slow queries into sub‑millisecond operations.
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 explains how ByteHouse leverages a native Bitmap data type and dictionary encoding to accelerate real‑time audience segmentation queries in advertising scenarios, achieving up to 50× performance gains over traditional array‑based models.
This article demystifies the actual execution sequence of SQL queries, detailing how FROM and JOIN establish table relationships, WHERE filters rows, GROUP BY groups data, HAVING applies aggregate conditions, SELECT retrieves fields, ORDER BY sorts results, and LIMIT restricts the final output.
This article presents practical MySQL optimization techniques—including proper index creation, avoiding index invalidation, choosing appropriate lock granularity, efficient pagination strategies, steering clear of SELECT *, and using EXPLAIN and SHOW PROFILE—to help developers write faster, more reliable SQL queries.
This article demonstrates how to accelerate a one‑million‑row database query by adding indexes and using a multithreaded Java Spring controller with MyBatis, showing code examples, performance tests, and suggestions for further optimization.
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.
The article explains why relying solely on SQL optimization often fails to resolve deep database performance problems, illustrating with real Oracle and domestic PostgreSQL‑compatible cases where I/O configuration, shared buffers, and system‑level metrics were essential to achieve stability.
This article analyzes a sluggish MySQL query, explains why the original plan performs a full table scan, and demonstrates how rewriting the EXISTS clause as an INNER JOIN and substituting the OR condition with a UNION dramatically reduces execution time from 42 seconds to 18 milliseconds.
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 article details a real‑world incident of severe pagination slow queries, walks through the timeline of detection, diagnosis, and mitigation steps, and presents multiple MySQL optimization techniques—including ID‑based queries, sub‑queries, scroll queries, and join‑based solutions—to resolve deep pagination performance issues.
This article introduces Chat2DB, an open‑source AI‑enhanced multi‑database client, walks through its download, installation, configuration, demonstrates natural‑language‑to‑SQL, SQL explanation and optimization features using sample MySQL tables, and outlines future roadmap for developers.
This article walks through nine common MySQL performance pitfalls—such as inefficient LIMIT usage, implicit type conversion, sub‑query updates, mixed sorting, EXISTS clauses, condition push‑down, early range reduction, intermediate result push‑down, and more—showing how to rewrite each query and its execution plan to achieve dramatic speed improvements.
This article shares practical methods, based on real‑world team experience, to identify and resolve data skew and data expansion issues in big data SQL queries, offering systematic investigation steps and optimization techniques for Map, Reduce, and Join stages.
This article details how JDQ, Flink, and ClickHouse were integrated to replace Elasticsearch for real‑time reporting, covering table‑engine selection, Flink sink implementation, performance bottlenecks, CPU hot‑spots, query optimization techniques, and strategies for handling high concurrency while ensuring data consistency and system stability.
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 details a November 2022 database outage caused by a sudden CPU and load surge, explains how the team diagnosed the issue, outlines the emergency steps taken, and shares practical SQL performance optimization recommendations to prevent similar incidents.
The article investigates why pure‑read QPS drops significantly after bulk updates in OceanBase, reproduces the issue with a sysbench workload, analyses flame‑graph and SQL audit data, explains the LSM‑Tree read‑amplification mechanism, and proposes practical mitigation steps such as major freeze, plan binding, index creation, and the queuing‑table feature.
The article presents Tencent's SuperSQL platform, detailing the big‑data challenges of heterogeneous data sources and fragmented SQL experiences, describing its multi‑layer adaptive architecture, core technologies such as unified SQL parsing, cost‑based and history‑based optimization, federated computation, materialized views and security, and summarizing its performance gains, industry impact and community contributions.
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 details Dingdong Maicai's adoption of Apache Doris as a real‑time OLAP engine, covering business requirements, comparative evaluation with ClickHouse, system architecture, practical applications such as real‑time analytics, B‑end queries, tag systems, and performance‑boosting techniques like Colocate Join, bitmap, prefix and Bloom‑filter indexes, materialized views, and streamlined Broker Load workflows.
This article explains the concept of data skew, its symptoms such as slow tasks and OOM errors, and provides comprehensive mitigation techniques and configuration examples for Hive and Spark, including custom partitioning, map joins, adaptive execution, and key detection methods.
This article presents practical MySQL query optimization techniques—including pagination, index‑based join improvements, subquery replacement with joins, order‑by and group‑by indexing, and count() efficiency—illustrated with real‑world examples, execution‑plan analysis, and ready‑to‑run SQL code.
This comprehensive guide walks you through practical MySQL optimization strategies—including slow‑query analysis, deep‑pagination fixes, explain plan interpretation, index tuning, large‑table design, and common pitfalls—so you can dramatically improve query speed and overall database performance.
This article walks through common ODPS SQL scenarios—union, count distinct, large‑table joins, mapjoin, and predicate placement—explains why naïve implementations can be inefficient, shows how to read and interpret execution plans, and provides concrete rewritten queries that dramatically improve performance and resource usage.
The article explains that MyBatis PageHelper performs an extra full‑table count query causing pagination to take over eight seconds on a 10,000‑row table, analyzes the root cause, and provides a concrete solution by customizing the count SQL with a _COUNT method and optimized mapper definitions.
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 explains why MyBatis foreach‑based batch inserts become extremely slow with thousands of rows, analyzes the underlying SQL generation and parsing costs, and demonstrates how using ExecutorType.BATCH or limiting rows per INSERT can reduce a 14‑minute operation to under two seconds.
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 comprehensive guide outlines 46 practical MySQL optimization rules—from understanding the query execution flow and naming conventions to index design, join strategies, pagination techniques, and safe use of data types—providing concrete examples and code snippets that help developers dramatically improve database performance and reliability.
ByteDance engineer Li Benchao shares his ten‑month evolution from a curious newcomer to a PMC member of Apache Calcite, describing how his work on Flink SQL led to deep involvement in the open‑source community, technical growth, and mentorship.
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 comprehensive guide explores MySQL architecture, index types, query execution plans, and practical optimization techniques, offering detailed examples of creating, modifying, and analyzing indexes, avoiding common pitfalls, and improving query performance through proper use of EXPLAIN, composite indexes, and query rewriting.
This article presents the design and implementation of Tencent Lighthouse's Fusion Analysis Engine, covering its background, challenges, fusion architecture, kernel optimizations, acceleration techniques, practical outcomes, and future evolution directions for high‑performance data access.
This article demonstrates how creating a covering composite index on MySQL tables can reduce a 55‑second aggregation query on five million rows to about two seconds by eliminating the need for row lookups and fully utilizing index data.
This article presents nine practical MySQL optimization methods—including smarter LIMIT usage, avoiding implicit type conversion, rewriting subqueries as joins, handling mixed sorting, replacing EXISTS with joins, pushing conditions into subqueries, early range reduction, and using CTEs—to dramatically improve query execution time across common scenarios.