This article explains why SQL performance becomes a bottleneck as data grows, outlines a systematic optimization workflow—including slow‑query detection, EXPLAIN analysis, profiling, tracing, and index tuning—and illustrates each step with concrete MySQL examples and best‑practice recommendations.
This article examines frequent MySQL performance pitfalls—such as misuse of LIMIT, implicit type conversion, sub‑query updates, mixed sorting, EXISTS clauses, and condition push‑down failures—and demonstrates how rewriting queries with proper indexes, JOINs, early range reduction, and WITH statements can dramatically reduce execution time.
The article explains the impact of slow SQL on system resources, outlines priorities for addressing performance bottlenecks, describes MySQL's execution process, shows how to enable and analyze slow‑query logs, and provides practical SQL optimization techniques such as avoiding subqueries, using IN, and eliminating unnecessary ORDER BY clauses.
The article analyzes a slow MySQL query that uses an IN subquery on a massive user table, explains how the optimizer’s semi‑join and materialized temporary tables cause full‑table scans, and demonstrates practical fixes such as disabling the semi‑join optimizer switch or rewriting the SQL to regain index usage.
This article examines frequent MySQL performance problems—such as inefficient LIMIT usage, implicit type conversion, suboptimal joins, mixed ordering, EXISTS subqueries, condition pushdown limitations, early range reduction, and intermediate result handling—and provides concrete rewrites and execution‑plan analyses that dramatically improve query speed.
This article explains how the default MySQL SHOW PROCESSLIST command can create a global mutex that slows down busy systems, demonstrates the problem with slow INSERTs reproduced via mysqlslap, and shows how enabling the performance_schema_show_processlist variable in MySQL 8.0.22+ eliminates the contention while providing best‑practice recommendations.
This article explains the two kinds of MySQL temporary tables—external and internal—covers the scenarios that trigger their creation, details how MySQL chooses MEMORY, MyISAM or InnoDB storage engines based on system variables and data size, and describes indexing, hash‑field fallbacks, and optimization hints such as SQL_SMALL_RESULT and SQL_BIG_RESULT.
The article explains how a pagination query on a 9.5‑million‑row MySQL table using a large OFFSET can take seconds, demonstrates an optimization that moves the condition into a subquery to fetch only primary keys before joining, and validates the speed gain with buffer‑pool measurements.
This article provides a comprehensive guide to optimizing MySQL queries and indexes, covering best practices such as avoiding subqueries, using IN instead of OR, proper LIMIT usage, eliminating unnecessary ORDER BY, leveraging UNION ALL, batch inserts, selective column retrieval, distinguishing IN and EXISTS, improving GROUP BY, using numeric fields, optimizing JOINs, and applying index rules like left‑most prefix, avoiding functions on indexed columns, handling type conversions, covering indexes, and effective use of composite and prefix indexes.
This guide walks through systematic MySQL query optimization—from identifying slow statements via logs and EXPLAIN analysis, to profiling, tracing, and applying targeted fixes such as index tuning, query rewrites, and handling special cases like left‑most matching, implicit conversion, large pagination, and IN‑order‑by pitfalls.
This guide presents a comprehensive set of MySQL optimization techniques—including proper use of EXPLAIN, limiting IN clause size, avoiding SELECT *, using LIMIT 1, minimizing sorting, replacing OR with UNION ALL, preferring UNION ALL over UNION, eliminating ORDER BY RAND(), distinguishing IN versus EXISTS, applying efficient pagination, segmenting large scans, avoiding NULL checks and leading wildcards, rewriting expressions, preventing implicit type conversion, respecting composite index leftmost rules, forcing indexes, handling range queries, and optimizing JOINs—to dramatically improve query performance.
This article walks through generating a 10‑million‑row MySQL table, inserting data with a fast batch script, measuring plain LIMIT pagination performance, and applying several optimization techniques—including sub‑queries, ID‑range filtering, and column selection—to dramatically reduce query time on large data sets.
This article walks through why SQL queries become bottlenecks as data grows and presents a step‑by‑step guide—including slow‑query analysis, EXPLAIN interpretation, profiling, optimizer tracing, index tuning, and scenario‑specific tricks—to dramatically improve MySQL performance.
This guide walks through essential MySQL performance tuning methods, from identifying slow queries via logs and EXPLAIN analysis to interpreting Extra information, using profiling and optimizer trace, and applying concrete index, query, and schema adjustments for common scenarios such as left‑most matching, implicit conversion, large pagination, IN‑order‑by, and complex queries.
This article provides a comprehensive guide to MySQL performance improvement, covering pagination, index, join, sorting, UNION, slow‑query‑log, schema design, and hardware optimizations with concrete SQL examples, best‑practice tips, and cautionary notes for real‑world deployments.
By migrating a latency‑heavy MySQL query to AnalyticDB, employing binlog‑based real‑time sync, refining composite indexes, adding clustering and filter hints, and batching inserts, the team cut execution time from about two seconds to roughly one hundred milliseconds, achieving over 99 % of queries under one second.
By redesigning Hologres tables with column orientation, shard‑controlled Table Groups, distribution and clustering keys, adding bitmap indexes, refreshing statistics, caching external data, and tuning optimizer join order and resource scaling, Alibaba’s Mom advertising inventory system cut query latency by up to 35 % and memory use by 98 %, achieving a 5‑10× performance boost.
This article extends the previous discussion on MySQL time‑based partition implementation by detailing how to write efficient SQL queries, demonstrating partition pruning with supported functions, and offering practical optimization techniques for tables that use unsupported partition functions.
This article explains TiDB's SQL layer architecture, details how SQL requests are processed and optimized, demonstrates the use of EXPLAIN and EXPLAIN ANALYZE, and presents two real‑world case studies that illustrate common performance pitfalls and practical optimization techniques.
This article presents four real‑world Oracle SQL cases—from a Cartesian join that halted nightly reports to a varchar primary key causing full scans, non‑standard date syntax breaking partition pruning, and missing statistics leading to unstable plans—detailing analysis, execution‑plan insights, and concrete remediation steps.
This article presents JD Daojia's data platform built on StarRocks, detailing business background, challenges such as pagination inconsistency, case‑when performance, and array‑type issues, and provides concrete SQL solutions, best‑practice tips, and future optimization directions.
This article explains the two most important Spark 3.0 features—Adaptive Query Execution and Dynamic Partition Pruning—detailing how AQE dynamically optimizes join strategies, partition coalescing, and skew handling, while DPP reduces I/O by pruning irrelevant fact‑table partitions at runtime.
This article explains how Spark SQL's Catalyst optimizer performs logical and physical planning, details the Tungsten engine's data‑structure and whole‑stage code generation improvements, compares them with the Volcano iterator model, and provides code examples and PDF resources for deeper study.
This article explains why MyBatis batch inserts using a foreach loop can become extremely slow when inserting thousands of rows, analyzes the underlying cost of parsing large prepared statements, and presents practical solutions such as using ExecutorType.BATCH, limiting foreach batch size, and employing JDBC batch execution to achieve sub‑second performance.
After repeatedly encountering account balance discrepancies in a financial system, I traced the issue to improper use of pessimistic locks and Hibernate caching, then resolved it by correcting lock placement, optimizing SQL queries, and switching to native SQL, sharing the detailed debugging steps and lessons learned.
This article explains how Apache Spark 3.0 improves SQL workload performance through Adaptive Query Execution (AQE) and Dynamic Partition Pruning (DPP), detailing their design principles, runtime optimizations, configuration parameters, and practical examples that demonstrate reduced shuffle partitions, smarter join strategies, and handling of data skew.
This article provides a comprehensive overview of Hive's execution engines—including MapReduce, Tez, and Spark—detailing their architectures, the six-stage Hive SQL compilation process, practical Explain syntax examples, and extensive tuning parameters for each engine to improve performance in big‑data environments.
The article explains why a simple GROUP BY query on a large user‑view table can become inefficient due to temporary tables, filesort, and memory allocation, demonstrates the execution steps with EXPLAIN, and shows how adding a composite index on user_id, viewed_user_age, and viewed_user_sex eliminates temporary tables and improves performance.
This article investigates why a DELETE IN subquery in MySQL 5.7 performs a full table scan instead of using an index, explains the optimizer’s behavior compared to SELECT IN, and provides practical solutions such as rewriting the statement as a JOIN or adding table aliases to enable index usage.
This article explains why using a large OFFSET with MySQL LIMIT can cause severe performance degradation, demonstrates the problem with a real‑world 9.5 million‑row table, and shows how rewriting the query with a sub‑query that selects only primary keys can cut execution time from seconds to milliseconds.
This article presents a comprehensive guide to improving MySQL query performance by addressing common issues 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 demonstrates how using WITH can simplify complex statements.
This article introduces several MySQL performance‑diagnostic utilities—including mysqltuner.pl, tuning‑primer.sh, pt‑variable‑advisor, and pt‑query‑digest—explains how to download and run them, and describes how to interpret their reports to identify configuration and query‑level issues.
This article explains why MySQL queries using LIMIT with a large offset become extremely slow, demonstrates the inefficiency through a 950‑million‑row table example, and shows how rewriting the query with a sub‑select join reduces execution time from seconds to milliseconds while preserving results.
This article explains common MySQL performance pitfalls such as large‑offset LIMIT, implicit type conversion, sub‑query updates, mixed ordering, EXISTS clauses, and condition pushdown, and shows how to rewrite each pattern using indexes, JOINs, UNION ALL, CTEs and early filtering to achieve execution times measured in milliseconds instead of seconds.
This comprehensive guide walks you through MySQL performance tuning, covering how to monitor server status, identify slow queries, use EXPLAIN for execution plans, create and manage various index types, and apply practical SQL optimizations such as import tricks, insert batching, efficient GROUP BY/ORDER BY, and pagination improvements.
This article explains what MySQL temporary tables and temporary files are, how they are created by users or the optimizer, how to view and clean them up, relevant configuration parameters, monitoring methods, and special cases that may require large storage.
This article explains MySQL deadlocks, describes InnoDB lock types, shows how to read deadlock logs, analyzes classic concurrency cases, and offers practical tips to design indexes and write SQL that minimize lock conflicts and avoid deadlocks.
The article shares painful real‑world cases of poorly written SQL and Java code, explains how tangled microservice dependencies and missing transactions cripple maintainability, and offers concrete guidelines for clean architecture, modular design, and effective code governance to boost team efficiency.
This article explains how databases store data on physical storage devices, describes the role of indexes and binary search in accelerating query performance, discusses clustered versus non‑clustered indexes, outlines the trade‑offs of excessive indexing, and lists common SQL optimization techniques.
This article walks through eight common MySQL performance pitfalls—including inefficient LIMIT usage, implicit type conversion, sub‑query updates, mixed sorting, EXISTS clauses, condition push‑down, early range reduction, and intermediate result push‑down—and shows how to rewrite each query to achieve dramatic speed improvements, often reducing execution time from seconds to milliseconds.
This article presents an extensive collection of MySQL performance‑tuning techniques, covering index design, query rewriting, use of UNION and EXISTS, temporary tables, NOLOCK hints, join limits, backup strategies, storage engine choices, data‑type selection, and other practical tips to improve query efficiency and overall database reliability.
This article explains why learning MySQL is important, outlines the focus of a multi‑part series covering MySQL's underlying principles, installation on macOS, basic architecture components, InnoDB features, and what readers can gain such as SQL optimization and cluster building skills.
This article outlines a systematic approach to identifying and fixing inefficient MySQL queries, covering slow‑query detection, EXPLAIN analysis, profiling, optimizer tracing, and practical case studies that demonstrate index tuning, query rewriting, and handling large‑scale pagination and complex conditions.
This article compiles a comprehensive set of MySQL interview questions covering index structures, B‑tree vs. hash trade‑offs, primary‑key and clustered‑index design, ACID properties, isolation levels, lock types, storage‑engine choices, data‑type considerations, binlog formats, pagination strategies, sharding, stored procedures, normalization, and MyBatis parameter syntax.
This article walks through a systematic SQL optimization workflow—identifying slow queries, interpreting EXPLAIN output, using SHOW PROFILE and TRACE, applying concrete tuning measures, and dissecting ten representative MySQL cases ranging from index ordering to large‑page handling and complex query pitfalls.
This talk presents NetEase's practical experience with Impala, covering its core architecture, new features in version 3.x, integration with Apache Iceberg, a custom management platform, profiling and statistics enhancements, as well as future plans involving Kubernetes, Alluxio caching and pre‑computation strategies.
This article shares practical MyBatis tips, detailing what different return types yield when no records match, common exceptions you may encounter, transaction lock behavior with FOR UPDATE, and essential SQL optimization strategies such as using LIMIT, proper indexing, and avoiding inefficient patterns.
This article documents a MySQL slow‑query case where a 5‑million‑row table took over 30 seconds to execute, explores several ineffective optimization attempts, uncovers a client‑side LIMIT issue, and finally resolves the performance problem by forcing the use of the idx_end_time index, cutting the runtime to under a second.
This article presents ByteDance's real‑world use of Apache Flink, covering the platform's overall architecture, SQL extensions, custom connectors, UI‑driven SQL platform, performance optimizations such as window mini‑batch and custom windows, dimension‑table enhancements, checkpoint recovery improvements, stream‑batch integration, and upcoming roadmap items.
This article examines frequent MySQL performance problems such as inefficient LIMIT usage, implicit type conversion, sub‑query updates, mixed sorting, EXISTS clauses, condition push‑down, pre‑filtering, and intermediate result handling, and provides rewritten SQL examples that dramatically reduce execution time.
This article presents a comprehensive guide to optimizing Hive queries, covering limit adjustments, join strategies, local mode execution, parallelism, strict mode, mapper and reducer tuning, JVM reuse, dynamic partitioning, speculative execution, data skew handling, and small‑file mitigation techniques.
This article examines frequent MySQL performance problems such as inefficient LIMIT usage, implicit type conversion, subquery updates, mixed ordering, EXISTS clauses, predicate push‑down, early row reduction, and intermediate result set handling, providing concrete rewrites and execution‑plan comparisons that dramatically improve query speed.
The article outlines practical backend performance optimization methods such as SQL tuning, eliminating duplicate calls, on‑demand queries, parallel execution, caching, asynchronous processing, JVM parameter tweaks, and horizontal scaling, providing code examples and actionable advice for developers.
This article explains how to replace pagehelper and mybatis-plus pagination in a legacy Java service by leveraging Druid's PagerUtils for concise count SQL generation, handling MyBatis placeholders, and implementing a custom pagination plugin to dramatically improve query performance.
This article examines frequent MySQL performance problems such as inefficient LIMIT usage, implicit type conversion, sub‑query updates, mixed ordering, EXISTS clauses, condition push‑down, early limiting, intermediate result push‑down, and demonstrates how rewriting queries with proper indexes, JOINs, UNION, and WITH statements can dramatically reduce execution time.
This article details a real‑world case where an RDS MySQL instance ran out of disk space due to an oversized temporary tablespace, explains the root‑cause analysis of a massive Cartesian‑product query, and provides step‑by‑step remediation and preventive measures for both MySQL 5.7 and 8.0.
This article presents a curated list of 100 essential MySQL interview questions covering indexes, transaction fundamentals, storage engine differences, table design, query optimization, and common pitfalls, providing concise explanations and practical examples to deepen developers’ understanding and prepare them for technical interviews.
This article explains common MySQL performance pitfalls such as large OFFSET in LIMIT clauses, implicit type conversion, sub‑query updates, mixed sorting, EXISTS usage, condition push‑down limitations, and demonstrates how rewriting queries with indexes, JOINs, UNION ALL, and WITH clauses can dramatically reduce execution time.
This article recounts the 2007 challenges faced by Taobao's monolithic MySQL setup and explains how the TDDL middleware introduced SQL optimization, sharding, read‑write separation, caching, and dynamic scaling to transform the system into a distributed, high‑performance database solution.
This article analyzes a 60‑second MySQL query that retrieves article comments with multiple filters, explains why the optimizer chooses a small table as the driver, and presents a step‑by‑step optimization—including avoiding semi‑joins, improving index usage, refining range conditions, and moving GROUP BY into a subquery—that reduces execution time to 1.3 seconds, achieving a 60‑fold speedup.
JD’s Algorithmic Intelligence team built a ClickHouse‑based real‑time analytics engine that ingests Kafka and offline data, uses MergeTree tables with strategic partitioning and sorting, and employs batch writes, materialized views, and monitoring to achieve second‑level queries over billions of rows.
This article presents a thorough overview of MySQL performance tuning, covering step‑by‑step SQL optimization techniques, common pitfalls, index and table‑structure improvements, cache parameter adjustments, and practical guidelines to reduce I/O and CPU bottlenecks while maintaining data integrity.
This comprehensive guide covers MySQL fundamentals—including primary, foreign, candidate, and super keys—auto‑increment primary keys, triggers, stored procedures, views, cursor usage, index types and design, transaction properties and isolation levels, storage engine differences, query execution order, EXPLAIN analysis, lock mechanisms, replication strategies, high‑concurrency solutions, and crash‑recovery via REDO and UNDO logs, providing practical examples and code snippets for each concept.
This article explains how large-scale recommendation systems rely on efficient feature engineering, describes the three-layer architecture (offline, stream, online), and details how Spark SQL and the LLVM‑optimized FESQL engine improve performance and ensure offline‑online feature consistency.
The article demonstrates how moving the LIMIT‑OFFSET condition into a subquery that selects only primary‑key IDs and then joining back to the main table can cut execution time from over 16 seconds to a few hundred milliseconds by avoiding massive index and data‑page scans.
The author describes how they transformed a sluggish SSRS report that took over thirty minutes to run into a fast, sub‑second query by analyzing the original SQL, adding missing indexes, avoiding full table scans, and rewriting the logic as a flexible stored procedure, complete with code examples and performance tips.
Apache Spark 3.0 introduces extensive SQL performance enhancements, including a new explain format, expanded join hints, adaptive query execution, dynamic partition pruning, enhanced nested column pruning, improved aggregation code generation, and support for newer Scala and Java versions, all aimed at optimizing query planning and execution.
This article explains how Spark SQL processes joins—including inner, outer, semi, and anti joins—by describing the overall query planning flow, the three physical join strategies (sort‑merge, broadcast, and hash), and the specific implementation details for each join type.
This article presents practical MySQL performance tricks, covering bulk data insertion with DISABLE/ENABLE KEYS, loading data in primary‑key order, multi‑value INSERTs, INSERT DELAYED, separating index and data files, GROUP BY sorting elimination, index‑driven ORDER BY, OR‑condition indexing, replacing sub‑queries with JOINs, and using SQL hints such as USE, IGNORE, and FORCE INDEX.
This article explains why using SELECT * in MySQL queries degrades performance, covering increased parsing cost, unnecessary data transfer, loss of covering index optimization, and how proper column selection and index design can dramatically improve query speed.
This article analyzes a slow MySQL view query caused by mismatched character sets, demonstrates how the automatic charset conversion prevents index usage, and shows step‑by‑step how altering the table charset and removing the conversion restores index usage and reduces execution time from seconds to fractions of a second.
This article explains the hidden costs of using SELECT * in MySQL, detailing how unnecessary columns increase parsing, network, and I/O overhead, and shows how proper column selection and composite indexes enable covering indexes for significantly faster query execution.
This article presents a comprehensive collection of MySQL performance‑tuning techniques—including proper use of EXPLAIN, limiting IN lists, selecting explicit columns, using LIMIT 1, avoiding costly ORDER BY RAND(), choosing between IN and EXISTS, preferring UNION ALL, applying full‑text indexes, and optimizing joins, pagination, and index usage—to help developers write faster, more efficient queries.
This article explains what database indexes are, their advantages and disadvantages, when to create or avoid them, various index types, and practical SQL optimization techniques, including code examples for efficient pagination in MySQL.
This article explains how to use EXPLAIN to analyze MySQL join queries, identifies causes of temporary tables and filesorts, and provides practical techniques—including driver‑table selection, STRAIGHT_JOIN usage, and index strategies—to optimize two‑ and multi‑table joins so that million‑row results return within two seconds.
This article provides a comprehensive guide to improving Hive query performance by covering column and partition pruning, predicate pushdown, replacing ORDER BY with SORT BY, using GROUP BY instead of DISTINCT, tuning MapReduce jobs, handling data skew in joins, and selecting appropriate storage formats for large‑scale data warehouses.
This article presents a comprehensive guide to MySQL performance tuning, covering bulk‑insert optimizations, efficient INSERT statements, GROUP BY and ORDER BY improvements, OR‑condition handling, nested query replacement, and the use of SQL hints such as USE INDEX, IGNORE INDEX, and FORCE INDEX, with practical code examples and benchmark results.
This article explains MySQL execution order, highlights performance problems with large OFFSET LIMIT queries, demonstrates how implicit type conversion, sub‑query rewriting, EXISTS removal, condition push‑down, and intermediate result push‑down can dramatically improve query speed, and provides practical SQL rewrite examples.
This article explains the purpose of the MySQL EXPLAIN command, details each column in its output (id, select_type, table, type, possible_keys, etc.), provides real‑world SQL examples, and shows how to interpret the plan to identify indexing, join, and sorting issues for query optimization.
This article shares practical MySQL performance optimization techniques, covering table size limits, connection settings, data type choices, index design, query rewriting, pagination, and batch processing, with code examples and actionable guidelines to improve query speed and scalability.
Meituan Takeaway’s data warehouse combines Apache Kylin’s MOLAP cubes for stable dimensions with Apache Doris’s MPP‑driven ROLAP engine to handle changing dimensions, detail queries, and near‑real‑time analytics, achieving millisecond‑level responses, reduced storage/compute costs, and simplifying operations across diverse analytical workloads.
This article explains how to identify slow MySQL queries using the slow‑query log and SHOW PROCESSLIST, configure logging parameters, and analyze query performance with EXPLAIN, covering key output fields, common values, and practical code examples for effective SQL optimization.
Data skew in Hive MapReduce jobs, caused by uneven key distribution during joins, group‑by, or COUNT(DISTINCT) operations, can severely slow tasks, and the article explains common scenarios and practical solutions such as using MapJoin, enabling map‑side aggregation, load‑balancing, and rewriting queries to mitigate skew.
This article introduces Apache Flink's Streaming SQL, explains its theoretical foundations such as the table‑stream relationship and watermark semantics, describes the platform's practical enhancements—including source/sink wrappers, built‑in functions, and native Retract Stream support—and showcases several real‑time computation examples.
This article introduces several MySQL performance‑tuning utilities—including mysqltuner.pl, tuning‑primer.sh, pt‑variable‑advisor, and pt‑query‑digest—explains how to download, install, run them, and interpret their reports to identify configuration issues and optimize database performance.
This article examines common MySQL performance pitfalls—such as large OFFSET limits, implicit type conversion, inefficient UPDATE/DELETE joins, mixed ordering, EXISTS subqueries, and poor condition push‑down—and demonstrates concrete query rewrites that reduce execution times from seconds to milliseconds.
This article presents eight practical MySQL optimization strategies—including LIMIT redesign, implicit type conversion fixes, JOIN rewrites for updates/deletes, mixed sorting tricks, EXISTS elimination, condition push‑down, early data reduction, and intermediate result set push‑down—each illustrated with before‑and‑after queries, execution plans, and measurable speed improvements.
This article demonstrates how to use the Explain feature in DBLE to visualize distributed query plans, compare join orders across multiple SQL statements, identify Cartesian products and ordered joins, and apply optimization insights for better performance in multi‑table queries.
This article explores common MySQL performance pitfalls such as inefficient LIMIT usage, implicit type conversion, subquery handling, and mixed sorting, and demonstrates eight practical rewrite strategies—including index‑friendly pagination, JOIN replacements, condition pushdown, and WITH clauses—to dramatically reduce query execution times from seconds to milliseconds.
This article presents concrete MySQL optimization techniques—including smarter LIMIT usage, avoiding implicit type conversion, rewriting updates and deletes with JOINs, handling mixed ordering, replacing EXISTS with joins, pushing down conditions, and leveraging WITH clauses—to dramatically reduce query execution times from seconds to milliseconds.
This article explains the concept of ER tables, demonstrates how they are configured and used in the DBLE middleware with parent‑child relationships, shows a practical SELECT example and query plan analysis, and concludes with best‑practice recommendations for leveraging foreign‑key optimizations.
A MySQL 5.6 database with three large tables suffered a 30,000‑second query when searching for students scoring 100 in a specific subject; by analyzing the execution plan, adding indexes on filter columns, and rewriting the query as a join, execution time dropped to under a second.
This article walks through a real‑world MySQL performance case where a nested sub‑query on millions of rows took over eight hours, explains why the plan used full table scans, and shows step‑by‑step index creation and query rewrites that cut the execution time to a millisecond.
This article examines common MySQL performance pitfalls such as inefficient LIMIT usage, implicit type conversion, suboptimal joins, mixed sorting, and unpushed predicates, and demonstrates eight practical rewriting techniques—including using max values, JOINs, WITH clauses, and early range reduction—to dramatically accelerate query execution.
The article explains why the Alibaba Java Development Manual restricts joins to three tables, discusses MySQL’s join algorithm limitations, and offers practical alternatives such as query decomposition, denormalization, and using IN or hash joins to improve performance.
Zhuanzhuan’s adoption of TiDB addressed sharding challenges and massive data storage needs, and the team shares six common issues encountered in large‑scale online deployments—including performance diagnosis, cluster management, log inconsistencies, slow‑SQL impact, optimizer limitations, and transaction conflicts—along with their standardized solutions for deployment, monitoring, alerting, and business rollout.
Understanding MySQL indexes—from hash tables, ordered arrays, and B+ trees—to their practical use cases, including clustered and secondary indexes, reveals why they boost query speed, when they may hinder performance, and how factors like selectivity, dirty page flushing, type mismatches, and function usage affect query efficiency.
OceanBase’s distributed relational database achieved the top TPC‑C benchmark score by leveraging cloud‑native resources, optimized storage, advanced transaction engines, and extensive SQL tuning, with detailed insights into preparation, system architecture, performance testing phases, ACID compliance, and storage optimizations that set new industry standards.
This article, the third in a five‑part series on OceanBase’s record‑breaking TPC‑C results, explains the SQL‑level challenges faced during the benchmark and details the optimizations—including stored procedures, LLVM‑based compilation, array binding, prepared statements, and updatable views—that enabled the performance gains.
This article demonstrates how to dramatically improve MySQL query performance by analyzing execution plans, adding appropriate single‑column and composite indexes, converting slow subqueries to efficient joins, and applying covering indexes and ordering optimizations, with detailed examples and timing results.
