Tagged articles
join optimization
35 articles · Page 1 of 1
A step-by-step MySQL query optimization case study showing how indexing, join rewriting, composite indexes, and covering indexes reduced execution time from 30,248 seconds to 0.001 seconds with execution plan analysis.
Composite IndexCovering IndexIndexing
0 likes · 12 min read

This comprehensive guide covers MySQL 9.5 performance optimization across table design, indexing strategies, query analysis with EXPLAIN, JOIN optimization, pagination techniques, system-level InnoDB tuning, and monitoring using Performance Schema and slow query logs to achieve dramatic query speed improvements.
Buffer PoolEXPLAINIndexing
0 likes · 30 min read

This article walks through diagnosing slow MySQL queries with EXPLAIN, applying index and join optimizations, using FORCE INDEX, monitoring index usage, preventing unsafe MyBatis patterns, and scaling massive tables with sharding, presenting concrete SQL examples and step‑by‑step guidance.
EXPLAINIndex OptimizationMySQL
0 likes · 10 min read

This guide explains how to configure MySQL's slow query log, use pt‑query‑digest to analyze logs, identifies common slow‑query patterns such as full table scans, large sorts, N+1 queries, implicit type conversion and bad joins, and provides concrete rewrite and indexing techniques to optimize each case.
Index OptimizationMySQLPagination
0 likes · 12 min read

This article explains why excessive MySQL joins—often inherited from legacy or Oracle‑migration code—cause performance and maintenance problems, and presents nine concrete optimization techniques including SQL splitting, temporary tables, redundant fields, index tuning, query rewriting, result‑set reduction, configuration tweaks, big‑data tools, and materialized summary tables.
Database PerformanceMySQLSQL
0 likes · 11 min read

A reproducible evaluation of StarRocks using the open‑source Coffee‑shop Benchmark shows that across 500 M, 1 B and 5 B row scales, StarRocks completes 17 complex join and aggregation queries 2–10× faster and with significantly lower cost than ClickHouse, Snowflake and Databricks, demonstrating superior performance and cost efficiency for analytical workloads.
Coffee-shop BenchmarkDatabase PerformanceStarRocks
0 likes · 11 min read

ClickHouse 25.10 introduces a suite of enhancements—including 20 JOIN performance upgrades, lazy column replication, Bloom filter runtime filters, disjunction push‑down, automatic column statistics, the QBit vector type, expanded SQL operators, negative LIMIT/OFFSET, Arrow Flight support, and delayed secondary index materialization—backed by detailed benchmarks and contributor acknowledgments.
ClickHouseSQL ExtensionsVector Search
0 likes · 23 min read

This article explains how MySQL's implicit type conversion during table joins can render indexes unusable or alter join order, and provides practical solutions such as aligning column types, using optimizer hints, and forcing indexes to restore query performance.
MySQLOptimizer Hintsforce index
0 likes · 6 min read

This article explores the origins of the "no more than three‑table JOIN" rule, explains MySQL's join algorithm limitations, shows real‑world performance pain points in distributed architectures, and presents practical solutions such as step‑wise queries, denormalization, and materialized views to safely break the restriction.
Database PerformanceDenormalizationMySQL
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

This article explains why excessive MySQL joins can cause performance problems and provides a comprehensive set of techniques—including query splitting, temporary tables, denormalization, index tuning, query rewriting, result‑set reduction, configuration tweaks, big‑data tools, and summary tables—to effectively optimize multi‑table join queries.
Database PerformanceDenormalizationMySQL
0 likes · 10 min read

The article explains why multi‑table JOINs in MySQL can degrade performance, readability, and index usage, and it presents optimization strategies such as query decomposition, data redundancy, wide tables, and introduces the hash join algorithm with detailed build and probe phases, including disk‑based handling.
Database PerformanceHash JoinMySQL
0 likes · 9 min read

This article explains Doris's data partitioning, the four join methods (Broadcast, Shuffle, Bucket Shuffle, and Colocate), runtime filter mechanisms and types, join reorder strategies, and practical tuning recommendations to improve query performance in the Doris MPP database.
Database PerformanceDorisRuntime Filter
0 likes · 17 min read

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.
Database PerformanceIndexingSQL
0 likes · 7 min read

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.
Database PerformanceNested LoopOceanBase
0 likes · 12 min read

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.
BKADatabase PerformanceHash Join
0 likes · 12 min read


政采云技术Jul 6, 2023 · Big Data
To address the resource‑intensive challenges of joining billion‑row tables in data warehouses, this article examines common optimization approaches, analyzes Spark’s SortMergeJoin algorithm, and proposes a Bloom‑filter‑based solution that filters unchanged data early, dramatically improving performance and reducing cluster resource consumption.
Bloom FilterJavaSQL
0 likes · 17 min read

CDW PG’s optimizer determines the most efficient join order for multi‑table OLAP queries by combining bottom‑up dynamic programming for smaller joins with a genetic algorithm for larger ones, while jointly selecting scan paths, join algorithms, and data‑distribution strategies to minimize execution cost.
CDW PGDistributed DatabaseMPP architecture
0 likes · 9 min read

This article provides a comprehensive, step‑by‑step guide to StarRocks join optimization, covering join types, logical rewrite rules, predicate push‑down, join reorder algorithms, cost modeling, distributed join strategies, and runtime filters, while offering practical tips for achieving high‑performance query execution.
Query PlanningStarRockscost-model
0 likes · 26 min read

The article explains how space‑efficient probabilistic structures such as BloomFilter and Count‑min Sketch enable large‑scale data deduplication, join pruning, real‑time idempotent filtering, and approximate top‑K analytics by trading modest accuracy loss for dramatically reduced storage and faster computation.
BloomFilterCount-Min SketchHash
0 likes · 12 min read

This article examines MySQL’s limited join capabilities compared to PostgreSQL, explains why multi‑table queries over three tables can be inefficient, and explores strategies such as service‑layer joins, query decomposition, and caching to improve performance and scalability in database‑centric applications.
MySQLQuery DecompositionService Layer
0 likes · 9 min read

This article examines ClickHouse's join mechanisms, detailing the limitations of standard joins, the advantages of Global joins, and optimization strategies such as hash and merge joins, subquery filtering, and memory considerations, illustrated with SQL examples and source‑code analysis.
ClickHouseHash JoinMerge Join
0 likes · 16 min read

This article explains JD’s self‑developed data‑skew mitigation solution for Spark, detailing the problem of uneven key distribution, the limitations of the open‑source AQE implementation, and JD’s OptimizeSkewedJoinV2 algorithm that dramatically reduces stage latency in large‑scale join workloads.
Data SkewSparkadaptive query execution
0 likes · 13 min read

This article explains how to inspect and clear Linux memory buffers, details the information shown by the free command, and provides a comprehensive overview of SQL join types, their performance impact, and optimization techniques such as indexing, join buffer tuning, and algorithm choices.
BuffCacheDatabase PerformanceLinux
0 likes · 7 min read

This article investigates the Alibaba Java Development Manual’s rule against joining more than three tables by designing and running large‑scale MySQL and Oracle experiments, analyzing performance impacts, presenting test results with and without indexes, and providing full DDL/DML scripts for reproducing the tests.
Database TestingLarge DatasetOracle
0 likes · 12 min read

This guide compiles practical Spark tuning techniques, covering essential configuration parameters, programming best‑practices, detailed shuffle mechanics, and join optimization strategies, while also addressing common errors and mitigation steps, enabling developers to improve performance and resource utilization in large‑scale data processing jobs.
ShuffleSparkbig data
0 likes · 25 min read

This article explains MySQL's various join algorithms—including Simple Nested Loop, Index Nested Loop, Blocked Nested Loop, and unsupported Hash and Merge joins—detailing their mechanisms, performance trade‑offs, and optimization strategies such as MRR, BKA, join buffers, and driver‑table selection.
BKADatabase PerformanceIndex
0 likes · 11 min read

This article investigates the Alibaba Java Development Manual's recommendation against joining more than three tables by experimentally evaluating multi-table join performance in MySQL and Oracle using massive synthetic datasets, analyzing query execution times, indexing effects, and providing data generation scripts and detailed results.
Data GenerationMySQLOracle
0 likes · 13 min read

This article analyzes a slow MySQL LEFT JOIN query, explains why the optimizer chose the inefficient Block Nested Loop algorithm instead of Index Nested Loop, shows how character‑set and collation differences cause index loss, and demonstrates how converting to INNER JOIN or fixing indexes restores high performance.
BNLIndexMySQL
0 likes · 10 min read

This article summarizes a MySQL webinar covering cost‑based query optimization, monitoring and analysis tools, data‑access methods and index selection, join optimization techniques, and ways to influence the optimizer through hints and configuration, providing a concise technical reference for database practitioners.
Index SelectionMySQLOptimizer Hints
0 likes · 11 min read

This article explains how SQL optimizers choose join methods and table order, compares nested loop and merge joins, outlines factors influencing table selection, and offers practical guidance for improving multi‑table query performance.
Database PerformanceMerge JoinNested Loop
0 likes · 8 min read

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, fine‑tuning join operations, and optimizing MapReduce parameters such as mapper/reducer counts, file merging, compression, JVM reuse, parallel execution, strict mode, and storage formats.
HiveMapReducePerformance Tuning
0 likes · 19 min read

SparkSql optimizes inner‑join queries by pushing predicates to the scan phase, allowing filters connected with AND to be applied before the join without changing results, while OR‑connected filters can be unsafe except when they involve the join key or partitioned tables which use partition pruning.
Partition PruningPredicate PushdownSQL Optimization
0 likes · 10 min read


ITPUBAug 31, 2016 · Databases
The article examines how unpredictable "black‑swan" incidents caused by slow, high‑frequency queries can destabilize database systems and presents concrete MySQL optimization techniques for large pagination, join operations, and concurrent count(*) queries that reduced slow‑query volume by about ninety percent.
Count OptimizationMySQLPagination
0 likes · 9 min read

Learn practical techniques to reduce nested loop iterations, prioritize inner-loop optimization, ensure join columns are indexed, and configure join buffers effectively, so your SQL joins run with minimal resource consumption and maximum performance.
Database PerformanceIndexingNested Loop
0 likes · 3 min read
