Understanding MySQL Partition Pruning: Which Partition Should Be Queried?

This article explains how MySQL implements partition pruning, describing the conditions under which the optimizer can skip irrelevant partitions, the internal data structures such as read_partitions and lock_partitions, the role of SEL_TREE and SEL_ARG, and provides a detailed example with code to illustrate the pruning process.

Database OptimizationSQLmysql

0 likes · 15 min read

Understanding MySQL Partition Pruning: Which Partition Should Be Queried?

Aikesheng Open Source Community

Feb 16, 2022 · Databases

Advanced MySQL Time-Based Partitioning: SQL Writing Tips and Optimization

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.

Database PerformanceSQL Optimizationmysql

0 likes · 10 min read

Advanced MySQL Time-Based Partitioning: SQL Writing Tips and Optimization

Alibaba Cloud Developer

Sep 17, 2021 · Databases

How MySQL 8.0 Optimizer Transforms Complex Subqueries and Joins

This article explains MySQL 8.0’s optimizer architecture and walks through the detailed transformation steps for subqueries, scalar‑to‑derived conversions, flattening, join simplification, partition pruning, and condition push‑down, illustrating each phase with code examples and execution‑plan screenshots.

Derived TablesJOINSubquery

0 likes · 25 min read

How MySQL 8.0 Optimizer Transforms Complex Subqueries and Joins

Aikesheng Open Source Community

Jun 4, 2021 · Databases

Understanding Lock Behavior in MySQL Partitioned Tables: Experiments and Insights

This article presents a series of MySQL experiments that examine how different isolation levels, partition‑key usage, and comparison operators affect the number and scope of locks acquired on partitioned tables, revealing the impact of partition pruning on lock contention.

Isolation LevelsLocksPartitioning

0 likes · 6 min read

Understanding Lock Behavior in MySQL Partitioned Tables: Experiments and Insights

vivo Internet Technology

Jan 31, 2018 · Big Data

Predicate Pushdown Rules in SparkSql Inner Join Queries

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.

JOIN optimizationPredicate PushdownSQL Optimization