BestHub
Sign in
Tag

execution plan

0 views collected around this technical thread.

Aikesheng Open Source Community
Aikesheng Open Source Community
May 28, 2025 · Databases

Analyzing and Optimizing Slow OceanBase SQL Queries Using TopSQL and Execution Plan Rewrites

This article investigates a slow OceanBase SQL query by examining CPU and TopSQL metrics, dissecting the execution plan, identifying batch join inefficiencies, rewriting the SQL for better performance, and demonstrating a reduction in execution time from over 2600 seconds to under one second.

Batch JoinOceanBaseSQL Optimization
0 likes · 18 min read
Analyzing and Optimizing Slow OceanBase SQL Queries Using TopSQL and Execution Plan Rewrites
Aikesheng Open Source Community
Aikesheng Open Source Community
May 27, 2025 · Databases

MySQL Execution Plans in Tree Format

This article introduces MySQL's tree‑style execution plan output using EXPLAIN FORMAT=TREE, explains its advantages over traditional and JSON formats, and demonstrates its practical use through eight detailed SQL examples with full plan analysis and performance insights.

MySQLSQL OptimizationTree Format
0 likes · 12 min read
MySQL Execution Plans in Tree Format
Top Architect
Top Architect
Jan 12, 2025 · Databases

Understanding MySQL Indexes, Execution Plans, Transaction Isolation, MVCC, and Buffer Pool

This comprehensive guide explains MySQL's underlying index structures (B‑tree, B+‑tree, hash), how execution plans are generated and interpreted, the mechanics of query execution, the role of locks and transaction isolation levels, the MVCC model, and the InnoDB buffer‑pool caching strategy.

Buffer PoolIndexesMVCC
0 likes · 19 min read
Understanding MySQL Indexes, Execution Plans, Transaction Isolation, MVCC, and Buffer Pool
Lobster Programming
Lobster Programming
Oct 5, 2024 · Databases

How MySQL Chooses the Cheapest Execution Plan: Cost Model Explained

This article explains MySQL's layered architecture, how the optimizer generates multiple execution plans, evaluates their I/O and engine costs, and selects the plan with the lowest total cost, illustrated with example queries, cost tables, and JSON output.

MySQLSQLcost model
0 likes · 9 min read
How MySQL Chooses the Cheapest Execution Plan: Cost Model Explained
Top Architect
Top Architect
Jul 10, 2024 · Databases

Understanding MySQL Index Structures, Execution Plans, Transactions, and MVCC

This article provides a comprehensive overview of MySQL internals, covering index data structures such as B‑tree and B+‑tree, the differences between MyISAM and InnoDB indexes, SQL execution plan analysis, transaction isolation levels, lock mechanisms, MVCC implementation, and the InnoDB buffer‑pool caching process.

Buffer PoolIndexesMVCC
0 likes · 19 min read
Understanding MySQL Index Structures, Execution Plans, Transactions, and MVCC
Full-Stack Internet Architecture
Full-Stack Internet Architecture
Dec 25, 2023 · Databases

Understanding MySQL EXPLAIN Execution Plans: A Detailed Guide

This article explains how to use MySQL's EXPLAIN statement to view execution plans, demonstrates creating sample order and customer tables, walks through each column of the EXPLAIN output, and shows how indexes affect query performance, providing a practical reference for database optimization.

EXPLAINMySQLdatabases
0 likes · 7 min read
Understanding MySQL EXPLAIN Execution Plans: A Detailed Guide
Aikesheng Open Source Community
Aikesheng Open Source Community
Oct 7, 2023 · Databases

Understanding Full Table Scan vs Full Primary Key Scan in MySQL InnoDB

This article analyzes the differences between MySQL's full table scan and full primary‑key scan by examining execution plans, source‑code paths, and runtime behavior, demonstrating that both ultimately read data from the primary‑key index and explaining why the optimizer distinguishes them.

Database OptimizationFull Table ScanInnoDB
0 likes · 9 min read
Understanding Full Table Scan vs Full Primary Key Scan in MySQL InnoDB
Laravel Tech Community
Laravel Tech Community
Aug 28, 2023 · Databases

Common MySQL Performance Issues and Their Optimization Techniques

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, early range reduction, and intermediate result handling, and provides concrete SQL rewrites and execution‑plan analyses to dramatically improve query speed.

IndexingMySQLPerformance Optimization
0 likes · 13 min read
Common MySQL Performance Issues and Their Optimization Techniques
Aikesheng Open Source Community
Aikesheng Open Source Community
Feb 13, 2023 · Databases

Why the Same SQL Has Different Execution Plans in QA and DEV Environments and How to Optimize It

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.

MySQLSQL Optimizationdatabase performance
0 likes · 17 min read
Why the Same SQL Has Different Execution Plans in QA and DEV Environments and How to Optimize It
Aikesheng Open Source Community
Aikesheng Open Source Community
Feb 8, 2023 · Databases

Customizing MySQL 8.0 Execution Plan Output per User with the explain_format Variable

This article explains how MySQL 8.0.32 introduces the @@explain_format system variable that allows administrators to set different execution‑plan output formats (tree, traditional, json, etc.) for individual users, and demonstrates the configuration steps with concrete SQL commands and examples.

MySQLMySQL8database
0 likes · 5 min read
Customizing MySQL 8.0 Execution Plan Output per User with the explain_format Variable
Architecture Digest
Architecture Digest
Jul 18, 2022 · Databases

Why MySQL IN Subquery with Semi‑Join Is Slow and How to Optimize It

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.

MySQLSQL OptimizationSemi-Join
0 likes · 8 min read
Why MySQL IN Subquery with Semi‑Join Is Slow and How to Optimize It
Laravel Tech Community
Laravel Tech Community
Apr 28, 2021 · Databases

Understanding and Analyzing MySQL Execution Plans

This article explains the three‑layer architecture of MySQL, how an execution plan is generated and interpreted, and provides a detailed walkthrough of the columns displayed by the EXPLAIN statement to help developers optimize their SQL queries.

EXPLAINMySQLSQL
0 likes · 8 min read
Understanding and Analyzing MySQL Execution Plans
Aikesheng Open Source Community
Aikesheng Open Source Community
Apr 9, 2021 · Databases

Optimizing DBLE Distributed Database Middleware: Cases of Query Push‑Down Failures and Their Solutions

This article analyzes why certain join queries in DBLE distributed database middleware fail to push down to backend nodes, leading to high CPU usage and latency, and presents four concrete examples with step‑by‑step optimizations to ensure full query push‑down and improved performance.

DBLEDistributed DatabaseSQL pushdown
0 likes · 8 min read
Optimizing DBLE Distributed Database Middleware: Cases of Query Push‑Down Failures and Their Solutions
Aikesheng Open Source Community
Aikesheng Open Source Community
Feb 2, 2021 · Databases

Overview of MySQL Execution Plan Fields

This article explains the meaning of each column in MySQL's EXPLAIN output, including id, select_type, type, possible_keys, key, key_len, ref, rows, filtered, and Extra, with examples of various query patterns such as simple selects, joins, subqueries, UNIONs, and derived tables.

EXPLAINIndexesMySQL
0 likes · 22 min read
Overview of MySQL Execution Plan Fields
Laravel Tech Community
Laravel Tech Community
Jan 24, 2021 · Databases

MySQL Slow Query Optimization: Reducing Execution Time from 30 Seconds to 0.19 Seconds

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.

IndexesMySQLPerformance Tuning
0 likes · 5 min read
MySQL Slow Query Optimization: Reducing Execution Time from 30 Seconds to 0.19 Seconds
Full-Stack Internet Architecture
Full-Stack Internet Architecture
Dec 14, 2020 · Databases

SQL Slow Query Diagnosis and Optimization Techniques with a Practical Case Study

This article explains how to identify slow‑query symptoms, gather problematic SQL statements, apply common writing and indexing tips, read execution plans, and walk through a complete MySQL optimization case, enabling readers to become proficient database performance engineers.

Database OptimizationIndexingMySQL
0 likes · 10 min read
SQL Slow Query Diagnosis and Optimization Techniques with a Practical Case Study
Full-Stack Internet Architecture
Full-Stack Internet Architecture
Jul 27, 2020 · Databases

Understanding MySQL EXPLAIN: Detailed Explanation of Execution Plan Fields

This article explains how to use MySQL's EXPLAIN statement to view execution plans, describes the meaning of each of the twelve output fields, provides practical examples with sample tables and queries, and offers optimization tips such as index creation and the left‑most prefix rule.

EXPLAINIndexesMySQL
0 likes · 15 min read
Understanding MySQL EXPLAIN: Detailed Explanation of Execution Plan Fields
Full-Stack Internet Architecture
Full-Stack Internet Architecture
Jul 15, 2020 · Databases

Why Using PRIMARY Key Index in MySQL COUNT(*) Can Be Slower: Analysis and Findings

An in‑depth investigation shows that counting rows with COUNT(*) on a MySQL table using the PRIMARY key index can be slower than a full table scan, revealing differences in execution plans, optimizer behavior, logical reads, and profiling data that explain the 0.02‑second performance gap.

MySQLcount()execution plan
0 likes · 15 min read
Why Using PRIMARY Key Index in MySQL COUNT(*) Can Be Slower: Analysis and Findings
Full-Stack Internet Architecture
Full-Stack Internet Architecture
Jun 26, 2020 · Databases

Understanding MySQL Query Execution Process and Optimization

This article explains how MySQL processes a SELECT statement—from client request, cache check, parsing, optimization, and execution—to return results, using a sample User table, a detailed query example, and a step‑by‑step walkthrough of the parser, optimizer, and executor components.

MySQLSQLdatabase
0 likes · 9 min read
Understanding MySQL Query Execution Process and Optimization
Efficient Ops
Efficient Ops
Feb 9, 2020 · Databases

Why a MySQL Query Took 30 Seconds and How a Hidden Index Cut It to 0.2 s

A production MySQL query on a table with five million rows took over 30 seconds, and after reproducing the issue, examining execution plans, trying various index and query tweaks, discovering that SQLyog adds a hidden LIMIT, and finally forcing the correct index, the runtime dropped to under a second, illustrating the importance of proper index usage and tool‑specific quirks.

Index OptimizationMySQLSQLyog
0 likes · 6 min read
Why a MySQL Query Took 30 Seconds and How a Hidden Index Cut It to 0.2 s