Tagged articles
SQL Performance
74 articles · Page 1 of 1
This guide details a systematic approach to MySQL index optimization: verify slow queries using performance_schema and system metrics, prove access paths with EXPLAIN ANALYZE, create indexes with rollback strategies using ALGORITHM=INPLACE and LOCK=NONE, address deep pagination with keyset pagination and N+1 queries with batch loading, and validate improvements with latency percentiles and lock metrics.
EXPLAIN ANALYZEIndex OptimizationInnoDB
0 likes · 12 min read

The article explains why OFFSET/LIMIT pagination causes full table scans and linear performance degradation with large offsets, demonstrates a 100k-row benchmark where cursor-based pagination using primary keys runs in 0.01s versus 12.80s for OFFSET, and recommends keyset pagination for scalable systems while noting limitations for tables without unique sequential columns.
Keyset PaginationLIMITOFFSET
0 likes · 7 min read

This article provides a comprehensive guide to MySQL's EXPLAIN command, detailing each column in the execution plan output with practical examples to help developers analyze and optimize SQL query performance.
Database IndexingEXPLAINMySQL
0 likes · 8 min read


liandkAug 23, 2026 · Databases
The article explains why MySQL back‑table lookups (回表) double I/O and degrade performance, defines covering indexes that retrieve all needed columns from a secondary index, shows how to identify back‑table cases via EXPLAIN, and provides step‑by‑step commands to create, test, and verify covering indexes for high‑frequency queries.
Covering IndexEXPLAINIndex Optimization
0 likes · 11 min read

This article examines ten typical situations where MySQL indexes become ineffective, explains the underlying reasons such as function usage, implicit type conversion, wildcard LIKE patterns, OR conditions, left‑most prefix violations, small tables, stale statistics, NULL handling, GROUP BY/DISTINCT, and ORDER BY, and provides concrete SQL examples and step‑by‑step remediation techniques to restore optimal query performance.
EXPLAINIndex OptimizationMySQL
0 likes · 32 min read

The article walks through a systematic approach to identify why a slow MySQL query can cause CPU usage to soar, covering observability, analysis with SHOW PROCESSLIST and EXPLAIN, and practical mitigation steps suitable for interview discussions.
CPU usageEXPLAINMySQL
0 likes · 7 min read

This article systematically examines why MySQL indexes can become ineffective—covering function calls, implicit casts, LIKE patterns, OR conditions, composite‑index left‑most rules, data distribution, and ORDER/GROUP mismatches—while providing concrete code examples, production case studies, and actionable optimization techniques.
B+ TreeIndex OptimizationMySQL
0 likes · 26 min read

This guide explains the six common reasons why MySQL indexes may fail to improve query speed, shows how interviewers evaluate index knowledge, and provides concrete SQL examples, EXPLAIN analysis, and practical optimization techniques such as redesigning indexes, using covering indexes, avoiding implicit type conversion, and tuning database configuration.
Covering IndexData SkewDatabase Interview
0 likes · 15 min read

A real‑world incident of soaring QPS reveals six common MySQL indexing pitfalls—type mismatches, function usage, left‑most prefix violations, implicit charset conversion, range query side effects, and optimizer mis‑selection—and provides concrete SQL fixes and verification tools to keep queries fast and reliable.
Index OptimizationMySQLQuery Pitfalls
0 likes · 6 min read

This article explains how MySQL indexes work, enumerates eight common reasons they become ineffective, adds six advanced pitfalls, and provides concrete diagnostic steps and production‑grade optimization techniques to keep queries fast and reliable.
Index OptimizationMySQLQuery Planning
0 likes · 9 min read

This article provides a systematic, step‑by‑step approach for identifying the root causes of slow SQL statements and sluggish API responses, covering monitoring, query analysis, index tuning, N+1 detection, caching, async processing, and architectural best practices.
API latencyAsynchronous ProcessingCaching
0 likes · 24 min read

This guide presents 30 practical MySQL index optimization techniques, covering creation principles, usage tips, performance enhancements, and maintenance strategies, with concrete SQL examples for each rule to help developers improve query efficiency and reduce write overhead.
Index OptimizationMySQLQuery Planning
0 likes · 9 min read

This article explains MySQL index fundamentals, covering what indexes are, their advantages and drawbacks, underlying data structures such as hash tables and B‑/B+‑trees, and the classification of clustered, non‑clustered, primary, secondary, unique, prefix, and full‑text indexes, with practical SQL examples and diagrams.
B-TreeHash TableMySQL
0 likes · 9 min read

This article examines a puzzling MySQL case where the optimizer selects a full‑table scan despite suitable indexes, explains the role of cost constants, execution‑plan analysis, and demonstrates how to force the optimizer to use the optimal index for faster queries.
Index SelectionMySQLQuery Optimization
0 likes · 12 min read

This article explains why MySQL uses B+‑tree indexes—highlighting disk‑IO efficiency, dense storage, superior range queries, and stable performance—while comparing B+‑trees to other structures, detailing their implementation in InnoDB, and providing interview‑style Q&A and optimization tips.
B+ TreeDatabase IndexingInnoDB
0 likes · 21 min read

This article compares MySQL’s SELECT DISTINCT and GROUP BY clauses, explaining their syntax, functional differences, performance implications, and ideal scenarios through detailed examples, index usage analysis, and a feature comparison table, helping developers choose the right approach for deduplication or aggregation tasks.
MySQLSELECT DISTINCTSQL Performance
0 likes · 10 min read

This article explains the concept of table lookups in MySQL, shows how they arise from using secondary indexes, provides concrete query examples, and offers practical optimization techniques such as creating covering indexes, reducing selected columns, and analyzing execution plans to improve performance.
Covering IndexInnoDBMySQL
0 likes · 9 min read

This article explains how databases store data on various storage devices, why indexes dramatically improve query performance through sorted structures and binary search, and outlines practical SQL optimization techniques while warning about the trade‑offs of excessive indexing.
Query OptimizationSQL Performancebinary search
0 likes · 11 min read

This article explains the JOIN order optimization technique known as the ‘small table drives large table’ principle, demonstrates its impact with a real e‑commerce query case, and reviews various MySQL join algorithms, their trade‑offs, and practical tuning tips.
Hash JoinIndex Nested LoopMySQL
0 likes · 28 min read

Index Condition Pushdown (ICP) is a MySQL 5.6 feature that pushes part of WHERE filtering to the storage engine, reducing row reads and I/O; this article explains its principles, activation, usage examples, performance testing, and conditions under which ICP can be applied.
ICPMySQLSQL Performance
0 likes · 10 min read

This article explains how to use Flame Graphs to visualize MySQL execution plans, discusses the limitations of traditional EXPLAIN output, introduces the EXPLAIN ANALYZE feature in MySQL 8.0, provides sample SQL and command‑line usage, and shows how the visual tool helps quickly identify performance bottlenecks.
EXPLAIN ANALYZEFlame GraphMySQL
0 likes · 11 min read

This article discusses solutions to system bottlenecks in supply chain planning business development, focusing on ClickHouse ReplacingMergeTree table creation, local join optimization, and real-time data synchronization between TiDB and ClickHouse to improve query performance and system stability.
ClickHouseLocal JoinReplacingMergeTree
0 likes · 10 min read

This article explains why massive tables become slow, identifies common bottlenecks such as disk I/O, missing indexes, deep pagination and lock contention, and provides a step‑by‑step guide covering table design, indexing, SQL tuning, sharding, caching and a real‑world case study to dramatically improve performance.
CachingLarge TablesSQL Performance
0 likes · 13 min read

This article explains what the rows column in MySQL EXPLAIN means, demonstrates three situations—when a small rows value indicates good performance, when it does not, and when rows is misleading—using concrete SQL examples, force‑index tricks, and multi‑table join analyses to show why rows alone cannot reliably judge query efficiency.
EXPLAINMySQLSQL Performance
0 likes · 10 min read

This article explains Spark SQL's window function fundamentals, introduces two key optimizations—Offset Window Frame and Infer Window Group Limit—and provides a detailed Q&A covering implementation details, execution plan impacts, and underlying architecture.
Apache SparkOptimizationSQL Performance
0 likes · 13 min read

This article explains why MySQL subqueries can hurt performance, outlines common pitfalls such as temporary tables, index loss, and optimizer complexity, and presents ten concrete examples that replace subqueries with IN, EXISTS, JOIN, indexes, temporary tables, window functions, and LIMIT to achieve faster, more maintainable queries.
MySQLSQL Performancedatabase tuning
0 likes · 10 min read

This article details the performance challenges of Ctrip's Nova data reporting platform and describes a series of governance measures—including multi‑dimensional data caching, materialized view acceleration, query strategy optimization, and SQL quality improvements—that collectively reduced average query latency by over 50% and stabilized the system.
CachingMaterialized ViewQuery Optimization
0 likes · 26 min read

COUNT(*), COUNT(1) and COUNT(column) may look similar, but they differ in how MySQL processes them, the rows they count, and performance implications, especially with NULL values and index usage; this article explains each form, cites official InnoDB behavior, and guides proper usage.
COUNTInnoDBMySQL
0 likes · 6 min read

This article compares MySQL's DISTINCT and GROUP BY clauses, detailing when they deliver identical performance with indexed columns, why DISTINCT can be faster without indexes, how each handles NULLs and multi‑column deduplication, and provides practical syntax examples and optimization guidance.
DISTINCTIndex OptimizationMySQL
0 likes · 10 min read

Learn eight practical MySQL optimization strategies—including smarter LIMIT usage, avoiding implicit type conversion, rewriting subqueries with JOINs, handling mixed sorting, eliminating costly EXISTS clauses, pushing conditions, pre‑filtering data, and leveraging intermediate result sets—to dramatically reduce query execution time from seconds to milliseconds.
LIMITMySQLQuery Optimization
0 likes · 15 min read


ITPUBJul 3, 2024 · Databases
A client complained about a 35‑second MySQL query that ignored indexes; by analyzing column selectivity and rewriting the condition to use the auto‑increment primary key instead of a secondary index, the execution time dropped to 2.55 seconds, illustrating why primary‑key access is often faster.
Index OptimizationMySQLSQL Performance
0 likes · 5 min read

This article walks through generating tens of millions of rows in MySQL, then demonstrates a series of optimizations—including simple and covering indexes, data reduction, small‑table‑driven joins, and forced indexes—showing how each technique impacts query performance and EXPLAIN output.
EXPLAINMySQLSQL Performance
0 likes · 14 min read


ITPUBJan 3, 2024 · Databases
This article walks through a MySQL 5.6 scenario where a sub‑query that scans 700,000 rows takes over 30,000 seconds, then demonstrates how adding single‑column and composite indexes, rewriting the query as a join, and analyzing execution plans reduces the runtime to milliseconds, highlighting best‑practice indexing techniques.
Index OptimizationMySQLQuery Tuning
0 likes · 12 min read

This announcement introduces Huawei Cloud’s DTT live session on August 29, where expert Lv Pengbo will explain GaussDB(DWS) resource control principles and demonstrate practical techniques—such as CPU usage analysis, memory tuning, and queue issue resolution—to help developers efficiently manage data‑warehouse resources and boost performance.
GaussDBHuawei CloudSQL Performance
0 likes · 3 min read

This article explores how bank DBAs continuously improve their capabilities through standardized daily inspections, automated environment provisioning, careful upgrade planning, development support, and disciplined incident response, while also encouraging a broader understanding of diverse database technologies and core relational concepts.
SQL PerformanceUpgrade Strategiesautomation
0 likes · 9 min read

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.
Index OptimizationMySQLQuery Optimization
0 likes · 19 min read

This article explains why MyBatis foreach‑based batch inserts become extremely slow with thousands of rows, demonstrates how the generated giant SQL statement hurts performance, and shows how switching to ExecutorType.BATCH or limiting rows per INSERT dramatically reduces insertion time.
ExecutorType.BATCHJavaMyBatis
0 likes · 9 min read

This article examines how MySQL decides whether to use an index or perform a full table scan for COUNT(*) and other queries, explains the IO and CPU cost model, demonstrates the calculations with a sample table, and shows how optimizer trace can reveal mismatches between estimated and actual performance.
EXPLAINMySQLSQL Performance
0 likes · 10 min read

This article explains the differences between DISTINCT and GROUP BY in MySQL, showing that with an index they have similar efficiency, while without an index DISTINCT is usually faster because GROUP BY may trigger sorting and temporary tables, and it provides syntax, examples, and recommendations.
DISTINCTIndexMySQL
0 likes · 8 min read

This article explains how DISTINCT and GROUP BY differ in MySQL, comparing their index usage, execution speed with and without indexes, the impact of implicit sorting in older versions, and why GROUP BY is often preferred for clearer semantics and advanced aggregations.
DISTINCTMySQLSQL Performance
0 likes · 10 min read


ITPUBDec 29, 2022 · Databases
This transcript details the design, implementation, and performance evaluation of TDSQL‑C's parallel query feature, covering product background, workload challenges, execution plans, Amdahl's law analysis, task scheduling, data partitioning, and future enhancements for cloud‑native database systems.
Amdahl's LawCloud-Native DatabaseData Partitioning
0 likes · 17 min read

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.
Index OptimizationMySQLSQL Performance
0 likes · 14 min read

This article describes Ctrip's practical approach to identifying and optimizing MySQL slow queries, covering background, improved SQL review workflow, execution plan analysis, common index problems, query rewriting, pagination issues, resource contention, and best‑practice recommendations for long‑term performance.
MySQLQuery PlanningSQL Performance
0 likes · 10 min read

This article presents a detailed overview of Apache Spark's Adaptive Query Execution evolution, its optimization techniques, and performance gains, followed by an in‑depth discussion of Apache Kyuubi's architecture, security integrations, cloud‑native capabilities, and practical Rebalance + Z‑Order strategies that enhance data‑warehouse task efficiency and query performance.
Apache SparkBig Data OptimizationKyuubi
0 likes · 19 min read

This article outlines critical database governance scenarios in cloud‑native microservice architectures, explains why database performance is a top priority, and details practical solutions such as slow‑SQL insight, flow‑control, connection‑pool management, and gray‑release techniques using MSE and OpenSergo standards.
Cloud NativeDatabase GovernanceMSE
0 likes · 12 min read

This guide compiles practical MySQL performance techniques—including proper indexing, avoiding costly operators, rewriting OR conditions, using EXISTS, limiting SELECT *, managing temporary tables, configuring threads, choosing storage engines, and optimizing data types—to dramatically reduce full‑table scans and improve query speed.
MySQLQuery OptimizationSQL Performance
0 likes · 21 min read

This article compares MySQL's COUNT(*), COUNT(1) and COUNT(column) functions, explains how each handles NULL values, analyzes performance differences on MyISAM and InnoDB engines, and provides practical recommendations for writing efficient count queries and existence checks.
COUNTCOUNT(1)COUNT(column)
0 likes · 9 min read

This article explains why and when to use table partitioning in PostgreSQL, describes inheritance‑based and declarative partitioning methods (list, range, hash), provides complete SQL and C code examples for creating and maintaining partitions, compares version differences, and shows a real‑world performance improvement case from Qunar.
C extensionsSQL PerformanceTable Partitioning
0 likes · 24 min read


ITPUBJul 2, 2021 · Databases
This article presents practical MySQL optimization techniques—including disabling keys for MyISAM, sorting InnoDB input, adjusting UNIQUE_CHECKS and AUTOCOMMIT, using multi‑row INSERT, LOAD DATA INFILE, ORDER BY NULL for GROUP BY, index‑aware ORDER BY, OR condition handling, and SQL hints—to dramatically speed up bulk data loads and common queries.
Bulk InsertMySQLSQL Performance
0 likes · 13 min read

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.
Index OptimizationMySQLORDER BY
0 likes · 6 min read

A production MySQL server suffered CPU overload and half‑hour alerts because a high‑volume query skipped a crucial composite index field, leading to full‑table scans; the post explains the root cause, the temporary index fix, the left‑most index principle, code validation oversights, and lessons learned.
Composite IndexDatabase DebuggingJava Validation
0 likes · 8 min read

This article explains how cloud databases use cost‑based parallel query engines to overcome I/O bottlenecks, select tables for parallel scans, implement parallel joins and aggregations, and achieve linear speedups on TPCH benchmarks, highlighting the key techniques and architectural components.
Cloud DatabaseParallel QuerySQL Performance
0 likes · 13 min read

This article compiles essential database optimization strategies—including proper index creation, composite and short indexes, avoiding full‑table scans, efficient use of temporary tables and cursors, and best practices for query writing—to dramatically improve query speed and overall system throughput.
SQL Performancecursorsindexes
0 likes · 11 min read

A production MySQL database suffered CPU overload due to a high‑volume query that bypassed an index because a leftmost column was missing, leading to full‑table scans, and the issue was resolved by adding a new composite index and correcting validation annotations in the Java code.
JavaMySQLSQL Performance
0 likes · 8 min read


ITPUBJan 16, 2021 · Databases
During a production outage, a high‑volume SQL query without a required leftmost index field caused full table scans that saturated CPU, prompting a temporary index fix and revealing a deeper bug involving missing @NotNull validation and improper parameter handling, highlighting MySQL’s left‑most index rule.
JavaMySQLSQL Performance
0 likes · 9 min read

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.
Index OptimizationMySQLQuery Planning
0 likes · 11 min read

This article explains the fundamentals of database indexing, detailing B‑Tree structures, the differences between clustered and non‑clustered indexes, their storage formats, and how they affect query, insert, and delete operations, including the concept of index covering.
B+ TreeIndex CoveringNon-clustered Index
0 likes · 15 min read

This article presents three HiveSQL optimization case studies—restructuring a large‑scale query with partitioned tables, breaking a complex window‑function query into smaller subsets with joins, and refactoring excessive PERCENTILE_APPROX calls—demonstrating how each change reduces execution time from hours to minutes and improves overall performance.
HiveHiveSQLOptimization
0 likes · 9 min read

The article explains MySQL 8.0's new statement_digest() and statement_digest_text() functions, showing how they generate normalized SQL texts and SHA2 hashes to group similar queries, and demonstrates practical applications in performance schema analysis and query‑rewrite plugins with concrete code examples.
MySQLQuery RewriteSQL Performance
0 likes · 8 min read


ITPUBSep 8, 2020 · Databases
A production MySQL server was overwhelmed by a high‑volume query that ignored a crucial composite index because the leftmost key was missing, leading to full‑table scans, CPU saturation, and a half‑hour outage, and the post explains how the issue was diagnosed and fixed.
Composite IndexJava ValidationLeftmost Matching
0 likes · 9 min read


ITPUBSep 7, 2020 · Databases
A production MySQL server was overwhelmed by a full‑table‑scan query that omitted the leftmost index column, causing CPU saturation and read‑only alerts; the article explains the root cause, temporary mitigation, the leftmost‑match principle, code‑level bug, and key lessons learned.
Database IndexingJava ValidationMySQL
0 likes · 9 min read

This article analyzes a real‑world MySQL incident where using INSERT INTO SELECT to migrate billions of rows caused table‑wide locking and payment failures, explains the underlying locking behavior, and presents a solution using proper indexing and FORCE INDEX to avoid full‑table scans.
INSERT INTO SELECTMySQLSQL Performance
0 likes · 7 min read

An investigation of a DBLE performance issue revealed that identical sharding keys with the same stringhash rule failed to improve query speed because differing hashSlice configurations prevented proper partitioning, and after correcting the rule.xml function settings, the SQL executed efficiently with sub‑second response times.
DBLEDatabase ConfigurationMySQL
0 likes · 4 min read

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.
EXPLAINIndex OptimizationMySQL
0 likes · 10 min read

This article introduces Oracle's SQLT tool, explains its seven diagnostic methods, demonstrates how to generate and analyze diagnostic files, shows direct and replace plan binding with SQL PROFILE, and details using the XPLORE feature to pinpoint parameter or bug issues affecting SQL performance.
OracleSQL PerformanceSQL profiling
0 likes · 14 min read

This article walks through a MySQL 5.6 scenario where a nested subquery to find students scoring 100 in Chinese runs for over 30,000 seconds, then demonstrates how adding single‑column and multi‑column indexes, analyzing execution plans, and applying join optimizations can reduce the runtime to milliseconds, while also covering best practices for index design and query tuning.
Database IndexingMySQLQuery Plan
0 likes · 9 min read

This article demonstrates how to efficiently remove duplicate rows from a million‑record MySQL table by comparing created_time and item_name, exploring various approaches such as correlated subqueries, joins, user‑defined variables, index optimization, window functions, and parallel execution with shell scripts and MySQL events to achieve significant performance gains.
MySQLSQL PerformanceWindow Functions
0 likes · 21 min read


ITPUBJul 8, 2019 · Databases
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.
Index OptimizationMySQLSQL Performance
0 likes · 11 min read

This guide compiles essential MySQL 5.5 techniques for teams without a DBA, covering storage engine characteristics, data type selection, index design—including composite, prefix, and covering indexes—and practical query optimizations such as reducing scanned rows, avoiding redundant indexes, and improving execution plans.
MySQLSQL Performancedata types
0 likes · 22 min read

This article explains MySQL index fundamentals, demonstrates performance issues with slow queries, and walks through practical optimization cases using EXPLAIN, covering index creation, covering indexes, and composite indexes, and best practices for when to add or avoid indexes.
EXPLAINIndex OptimizationMySQL
0 likes · 17 min read


ITPUBJan 2, 2018 · Databases
Learn ten practical strategies to accelerate database queries—including proper schema design, using EXPLAIN, caching, selective column retrieval, LIMIT clauses, avoiding loops, replacing subqueries with joins, careful wildcard use, UNION over OR, and indexing—to reduce server load and improve application responsiveness.
Query TuningSQL Performanceindexing
0 likes · 9 min read

A senior SQL specialist recounts a high‑pressure production incident where a seemingly trivial INSERT statement caused a timeout, analyzes the flawed database design, compares alternative architectures, and argues for handling bulk data processing directly in the database.
OracleSQL Performanceagile pitfalls
0 likes · 10 min read


ITPUBSep 23, 2016 · Databases
This article presents practical MySQL optimization techniques covering table schema design, character set choices, primary key strategies, field definitions, index creation, SQL query tuning, pagination tricks, join handling, connection‑pool parameter tuning, and historical data archiving for large‑scale databases.
Connection PoolMySQLSQL Performance
0 likes · 13 min read


ITPUBNov 19, 2015 · Databases
This guide presents practical recommendations for MySQL and Oracle development, covering table design, appropriate data types, indexing best practices, and efficient SQL patterns to improve performance, maintainability, and security.
Index OptimizationMySQLSQL Performance
0 likes · 7 min read
