BestHub
Sign in
Tagged articles
420 articles
Page 2 of 5
Top Architect
Top Architect
Apr 25, 2024 · Databases

Comprehensive Guide to MySQL Index Optimization and EXPLAIN Plan Analysis

This article explains MySQL's architecture, how to view storage engines and variables, demonstrates creating various indexes, describes why SQL optimization is needed, details the EXPLAIN plan keywords and index types, and provides practical best‑practice tips for single‑ and multi‑table query performance improvements.

Database PerformanceEXPLAINIndex Optimization
0 likes · 35 min read
Comprehensive Guide to MySQL Index Optimization and EXPLAIN Plan Analysis
Top Architect
Top Architect
Apr 11, 2024 · Databases

MySQL Slow Query Analysis and Index Optimization for Large Tables

This article presents a detailed investigation of a MySQL master‑slave instance suffering from nightly SLA alerts due to slow‑query latency, analyzes the root causes with pt‑query‑digest and execution plans, proposes index redesign, demonstrates backup and restore with mydumper, compares online DDL and pt‑osc, and finally shows batch‑delete techniques to reduce load on large tables.

Database PerformanceIndex OptimizationLarge Tables
0 likes · 16 min read
MySQL Slow Query Analysis and Index Optimization for Large Tables
Architect
Architect
Apr 8, 2024 · Backend Development

Mastering Batch Processing: Boost API Performance and Cut Overhead

This guide explains why batch processing is essential for API tuning and provides step‑by‑step techniques—including bulk database operations, request merging, pagination, parallel execution, caching, and monitoring—backed by concrete Java code samples and SQL queries to help engineers dramatically improve throughput and latency.

API optimizationBatch ProcessingCaching
0 likes · 33 min read
Mastering Batch Processing: Boost API Performance and Cut Overhead
Mike Chen's Internet Architecture
Mike Chen's Internet Architecture
Apr 8, 2024 · Databases

9 Essential MySQL Index Optimization Techniques You Must Know

This article presents nine practical MySQL index optimization techniques—from selecting high‑selectivity columns and avoiding functions on indexed fields to applying the leftmost‑prefix rule, handling NULLs, using proper LIKE patterns, and leveraging EXPLAIN and LIMIT 1 for faster queries.

Database PerformanceIndex OptimizationQuery Tuning
0 likes · 6 min read
9 Essential MySQL Index Optimization Techniques You Must Know
Liangxu Linux
Liangxu Linux
Apr 4, 2024 · Databases

Why MySQL Tables Stall After 20 Million Rows and How B‑Link Trees Fix It

The article examines why MySQL performance drops sharply when a single table exceeds tens of millions of rows, debunks the index‑depth myth, explains SMO concurrency limits in InnoDB, and shows how B‑Link Tree indexes and heap‑organized tables can overcome these bottlenecks.

B-Link TreeB-TreeDatabase Performance
0 likes · 10 min read
Why MySQL Tables Stall After 20 Million Rows and How B‑Link Trees Fix It
Aikesheng Open Source Community
Aikesheng Open Source Community
Feb 29, 2024 · Databases

Optimizing Nested-Loop Join Performance in OceanBase: Analysis, Rewrites, and Results

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 PerformanceJOIN optimizationNested Loop
0 likes · 12 min read
Optimizing Nested-Loop Join Performance in OceanBase: Analysis, Rewrites, and Results
MaGe Linux Operations
MaGe Linux Operations
Feb 26, 2024 · Databases

Why MySQL Single Tables Hit Performance Limits and How B‑Link Trees Help

This article examines the technical reasons why MySQL single tables struggle with massive data volumes, debunks the index‑depth myth, explains InnoDB's SMO concurrency bottlenecks, and shows how B‑Link Tree indexes and heap‑organized tables in GaussDB provide superior performance for large‑scale workloads.

B-Link TreeB-TreeDatabase Performance
0 likes · 10 min read
Why MySQL Single Tables Hit Performance Limits and How B‑Link Trees Help
FunTester
FunTester
Jan 9, 2024 · Databases

Boost Java Database Performance with PreparedStatement Batch Inserts

The article explains Java's PreparedStatement interface, highlighting its performance, security, and batch processing benefits, and provides a complete example of inserting multiple user records efficiently, while also discussing practical considerations such as batch size, transaction handling, error management, and optimization tips.

Batch ProcessingDatabase PerformanceJDBC
0 likes · 6 min read
Boost Java Database Performance with PreparedStatement Batch Inserts
ITPUB
ITPUB
Dec 31, 2023 · Databases

Master MySQL Index Optimization with EXPLAIN: A Step‑by‑Step Guide

This article explains why slow queries occur in high‑traffic MySQL databases, introduces the EXPLAIN statement, walks through its syntax and output columns, demonstrates how to interpret each field with concrete examples, and provides a practical workflow for optimizing indexes to eliminate performance bottlenecks.

Database PerformanceEXPLAINIndex Optimization
0 likes · 14 min read
Master MySQL Index Optimization with EXPLAIN: A Step‑by‑Step Guide
Open Source Tech Hub
Open Source Tech Hub
Dec 11, 2023 · Databases

How MySQL 8.2 Router Enables Seamless Read/Write Splitting

Oracle’s MySQL 8.2 introduces built‑in read/write splitting via MySQL Router, allowing write traffic to target primary instances while reads are automatically routed to secondary replicas, with configuration examples, command‑line bootstrap, routing settings, and practical observations on transaction behavior.

Database PerformanceInnoDB ClusterMySQL Router
0 likes · 6 min read
How MySQL 8.2 Router Enables Seamless Read/Write Splitting
Aikesheng Open Source Community
Aikesheng Open Source Community
Dec 5, 2023 · Databases

Optimizing MySQL Join Queries: Algorithms, Execution Plans, and Practical Tips

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
Optimizing MySQL Join Queries: Algorithms, Execution Plans, and Practical Tips
php Courses
php Courses
Dec 2, 2023 · Databases

Index Optimization Techniques for PHP and MySQL Multi‑Table Queries

Effective index design, including single‑table, foreign‑key, composite, and covering indexes, along with optimizer hints, can dramatically improve the performance of multi‑table queries in PHP‑MySQL applications, and the article provides practical examples and SQL statements for each technique.

Database PerformanceIndex OptimizationPHP
0 likes · 4 min read
Index Optimization Techniques for PHP and MySQL Multi‑Table Queries
Selected Java Interview Questions
Selected Java Interview Questions
Nov 26, 2023 · Databases

Understanding and Solving Hot Key Issues in Redis

Hot keys in Redis—high‑frequency accessed keys—can overload the cache and downstream databases, causing crashes; this article explains what hot keys are, why they arise, their risks, how to detect them, and practical mitigation strategies such as scaling clusters, using secondary caches, monitoring commands, and traffic analysis.

CacheDatabase PerformanceHot Key
0 likes · 6 min read
Understanding and Solving Hot Key Issues in Redis
Open Source Linux
Open Source Linux
Nov 17, 2023 · Databases

How Large Linux Pages Boost Database Performance on Kubernetes

This article explains how using larger Linux page sizes—especially 2 MB hugepages—dramatically improves database throughput on Kubernetes nodes by reducing TLB cache misses, and provides practical guidance on configuring hugepages, disabling transparent hugepages, and sizing resources for optimal performance.

Database PerformanceKubernetesLinux
0 likes · 13 min read
How Large Linux Pages Boost Database Performance on Kubernetes
Java High-Performance Architecture
Java High-Performance Architecture
Nov 13, 2023 · Databases

Master MySQL Performance: Deep Dive into Indexes, Explain Plans & Optimization

This comprehensive guide explores MySQL’s architecture, explains how queries are parsed and executed, and provides detailed techniques for optimizing SQL performance through effective indexing, understanding EXPLAIN output, and avoiding common pitfalls that cause index misuse and slow query execution.

Database PerformanceExplain PlanSQL optimization
0 likes · 37 min read
Master MySQL Performance: Deep Dive into Indexes, Explain Plans & Optimization
IT Services Circle
IT Services Circle
Nov 12, 2023 · Databases

Why Many Large Internet Companies Avoid Using MySQL Foreign Keys

The article explains that while MySQL foreign keys ensure data consistency and integrity, they introduce performance overhead, lock contention, and scalability issues—especially in high‑concurrency and sharded environments—leading many large internet firms to forego them in favor of application‑level solutions.

Database PerformanceForeign KeyScalability
0 likes · 5 min read
Why Many Large Internet Companies Avoid Using MySQL Foreign Keys
ITPUB
ITPUB
Nov 9, 2023 · Databases

Understanding MySQL Locks: When to Use Them, How They Work, and When to Avoid Them

This article explains the fundamentals of MySQL locking—including explicit and implicit locks, transaction isolation levels, next‑key and gap locks, and practical session‑based examples—so developers can decide when to apply pessimistic locks and when to prefer safer alternatives.

Database PerformanceInnoDBLocks
0 likes · 17 min read
Understanding MySQL Locks: When to Use Them, How They Work, and When to Avoid Them
Architect
Architect
Nov 7, 2023 · Databases

MySQL Architecture, Indexes, and SQL Optimization Guide

This article explains MySQL's basic architecture, demonstrates how to view and set storage engines, and provides comprehensive guidance on SQL optimization methods, index types, creation, usage, and performance analysis using EXPLAIN, helping readers improve query efficiency and avoid common pitfalls.

Database PerformanceEXPLAINIndexes
0 likes · 35 min read
MySQL Architecture, Indexes, and SQL Optimization Guide
Tencent Cloud Developer
Tencent Cloud Developer
Nov 6, 2023 · Databases

MySQL Lock Mechanisms: When, How, and When Not to Use

The article explains MySQL’s explicit and implicit lock mechanisms—including SELECT … FOR UPDATE, metadata, row, gap, and next‑key locks—how isolation levels affect them, and offers practical guidelines such as using optimistic locking, indexing locked columns, and avoiding long transactions to prevent phantom reads, deadlocks, and performance issues.

Database PerformanceInnoDBSQL
0 likes · 15 min read
MySQL Lock Mechanisms: When, How, and When Not to Use
Selected Java Interview Questions
Selected Java Interview Questions
Nov 3, 2023 · Databases

Comprehensive Guide to MySQL Architecture, SQL Optimization Techniques, and Explain Plan Analysis

This article provides an in‑depth overview of MySQL’s core architecture, storage engines, index types, and practical SQL optimization methods, followed by detailed explanations of the EXPLAIN plan keywords and numerous real‑world examples to help developers improve query performance and avoid common indexing pitfalls.

Database PerformanceExplain PlanIndexes
0 likes · 34 min read
Comprehensive Guide to MySQL Architecture, SQL Optimization Techniques, and Explain Plan Analysis
ITPUB
ITPUB
Oct 31, 2023 · Databases

Demystifying MySQL InnoDB Buffers: Pool, Change, Log & Doublewrite

This article explains MySQL InnoDB’s key buffering mechanisms—buffer pool, change buffer, log buffer, and doublewrite buffer—detailing their roles, internal structures, eviction policies, and how they improve performance and data durability while handling page reads, writes, and crash recovery.

Change BufferDatabase PerformanceDoublewrite
0 likes · 14 min read
Demystifying MySQL InnoDB Buffers: Pool, Change, Log & Doublewrite
Liangxu Linux
Liangxu Linux
Oct 22, 2023 · Databases

How Huge Linux Pages Can Boost Database Throughput on Kubernetes by Up to 8×

This article explains how Linux page size—from the default 4 KB to 2 MB or 1 GB huge pages—affects database performance, details the role of TLB cache hits and misses, presents benchmark results showing up to an eight‑fold throughput increase, and offers practical guidance for configuring huge pages on Kubernetes nodes.

Database PerformanceKubernetesTLB
0 likes · 14 min read
How Huge Linux Pages Can Boost Database Throughput on Kubernetes by Up to 8×
DataFunTalk
DataFunTalk
Sep 24, 2023 · Databases

Insights into the Design and Challenges of Doris' New Optimizer (Nereids)

The article explains why Doris needed a new optimizer, describes its architecture—including rule‑based and cost‑based stages, early data‑size reduction techniques, dynamic‑programming join‑reorder methods, and practical challenges such as statistics errors and runtime filters—while sharing performance results and a Q&A session.

Database PerformanceJoin ReorderQuery Optimizer
0 likes · 17 min read
Insights into the Design and Challenges of Doris' New Optimizer (Nereids)
Liangxu Linux
Liangxu Linux
Sep 10, 2023 · Databases

Master MySQL Query Optimization: Architecture, Caching, and Index Strategies

This article explains MySQL's logical architecture, query execution flow, caching mechanisms, and detailed index design techniques, providing practical tips and examples so developers can understand the underlying principles and apply effective performance optimizations in real‑world applications.

Database PerformanceIndexesSQL
0 likes · 38 min read
Master MySQL Query Optimization: Architecture, Caching, and Index Strategies
Selected Java Interview Questions
Selected Java Interview Questions
Sep 4, 2023 · Databases

Common MySQL Query Performance Issues and Optimization Techniques

This article discusses common MySQL performance pitfalls such as inefficient LIMIT usage, implicit type conversion, subquery updates, mixed ordering, EXISTS clauses, condition pushdown, and intermediate result handling, and provides optimized SQL rewrites and best practices to dramatically improve query execution speed.

Database PerformanceIndexesQuery Tuning
0 likes · 13 min read
Common MySQL Query Performance Issues and Optimization Techniques
MaGe Linux Operations
MaGe Linux Operations
Aug 19, 2023 · Databases

How Deep Pagination Slowed Our System and the SQL Fixes That Saved It

This article walks through a real‑world incident where a pagination‑related slow‑query caused massive CPU spikes, details the step‑by‑step investigation, and presents several MySQL optimization techniques—including ID‑based queries, sub‑queries, and cursor‑based pagination—that ultimately resolved the performance crisis.

Database PerformanceSQL optimizationdeep pagination
0 likes · 10 min read
How Deep Pagination Slowed Our System and the SQL Fixes That Saved It
ITPUB
ITPUB
Aug 15, 2023 · Databases

Master MySQL Performance: Practical SQL Optimization and Indexing Techniques

This comprehensive guide explains MySQL’s architecture, index fundamentals, the EXPLAIN plan keywords, step‑by‑step index creation and deletion commands, and numerous real‑world optimization examples, helping developers dramatically improve query execution speed while avoiding common pitfalls.

Database PerformanceExplain PlanIndexes
0 likes · 35 min read
Master MySQL Performance: Practical SQL Optimization and Indexing Techniques
ITPUB
ITPUB
Aug 11, 2023 · Databases

Can PostgreSQL Replace Oracle in Enterprise Core Systems? Key Gaps and Challenges

The article examines why PostgreSQL still falls short of being a core enterprise database by analyzing eight technical gaps—such as XID64 support, LWLOCK efficiency, shared buffer performance, recovery, high availability, zero data loss, resource management, and CBO optimizer maturity—while reflecting on industry trends and Oracle's dominance.

Database PerformanceEnterprise DatabasesOracle Comparison
0 likes · 10 min read
Can PostgreSQL Replace Oracle in Enterprise Core Systems? Key Gaps and Challenges
Sanyou's Java Diary
Sanyou's Java Diary
Aug 7, 2023 · Databases

What Locks Does SELECT FOR UPDATE Use? 20 MySQL Scenarios Revealed

This article experimentally verifies how SELECT FOR UPDATE behaves under MySQL 5.7 and 8.0 with both REPEATABLE‑READ and READ‑COMMITTED isolation levels, covering primary keys, unique indexes, ordinary indexes, no indexes and range queries, and summarizes when row‑level, gap‑level or table‑level locks are taken.

Database PerformanceSELECT FOR UPDATElocking
0 likes · 23 min read
What Locks Does SELECT FOR UPDATE Use? 20 MySQL Scenarios Revealed
Tencent Database Technology
Tencent Database Technology
Jul 27, 2023 · Databases

MySQL Network Write Timeout (Error 1161) Analysis and Debugging

This article investigates a MySQL 8.0 TDSQL client experiencing connection interruptions during batch streaming, analyzes error 1161 (ER_NET_WRITE_INTERRUPTED) root causes in the MySQL source code, presents debugging steps with log and packet captures, and offers practical mitigation recommendations.

Database PerformanceDebuggingError 1161
0 likes · 11 min read
MySQL Network Write Timeout (Error 1161) Analysis and Debugging
IT Services Circle
IT Services Circle
Jul 9, 2023 · Databases

20 Practical Rules for MySQL Index Optimization

This article presents twenty essential principles for optimizing MySQL indexes, covering topics such as the limitations of leading wildcard LIKE queries, the advantages of IN over UNION, handling negative conditions, composite index ordering, range queries, covering indexes, prefix indexes, and common pitfalls to avoid for better query performance.

Database PerformanceIndex OptimizationQuery Tuning
0 likes · 15 min read
20 Practical Rules for MySQL Index Optimization
DataFunTalk
DataFunTalk
Jul 7, 2023 · Databases

Apache Doris 2.0-beta Release: New Query Optimizer, Pipeline Execution Engine, Workload Management and Major Performance Improvements

Apache Doris 2.0-beta, released on July 3, 2023, introduces a new Cascades‑based query optimizer, adaptive pipeline execution engine, workload‑aware resource isolation, enhanced memory management, partial column updates, multi‑catalog support, and numerous performance gains across real‑time analytics, ETL, and high‑concurrency point queries.

Apache DorisDatabase PerformancePipeline Execution
0 likes · 25 min read
Apache Doris 2.0-beta Release: New Query Optimizer, Pipeline Execution Engine, Workload Management and Major Performance Improvements
Java High-Performance Architecture
Java High-Performance Architecture
Jun 5, 2023 · Databases

Master MySQL Indexes: Types, Usage, and Performance Optimization

This guide explains MySQL indexes—what they are, their data structures, advantages and drawbacks, when to create them, various index types, how to interpret EXPLAIN output, common pitfalls that cause index loss, and practical optimization techniques for queries, joins, ordering, grouping, and slow‑query logging.

Database PerformanceEXPLAINindex
0 likes · 19 min read
Master MySQL Indexes: Types, Usage, and Performance Optimization
Top Architect
Top Architect
Jun 1, 2023 · Databases

Comprehensive Guide to MySQL Indexes, Types, and Query Optimization

This article explains the definition, data structures, advantages, disadvantages, and appropriate usage scenarios of MySQL indexes, details primary, unique, single‑column and composite index types, analyzes common performance bottlenecks with EXPLAIN, demonstrates index creation, invalidation cases, query and join optimizations, and shows how to configure and use the slow‑query log.

Database PerformanceEXPLAINIndexes
0 likes · 19 min read
Comprehensive Guide to MySQL Indexes, Types, and Query Optimization
Su San Talks Tech
Su San Talks Tech
May 30, 2023 · Databases

Master MySQL Indexes: From B+ Trees to Index Merge and Optimization

This article explains MySQL index fundamentals—including classification, B+‑tree and hash structures, clustered and secondary indexes, single‑ and composite‑column indexes, covering indexes, index condition pushdown, index merge strategies, cost‑based index selection, common index‑invalidating scenarios, and practical guidelines for creating effective indexes.

B+TreeDatabase PerformanceInnoDB
0 likes · 29 min read
Master MySQL Indexes: From B+ Trees to Index Merge and Optimization
php Courses
php Courses
May 26, 2023 · Databases

Using Indexes to Optimize MySQL Queries

This article explains what indexes are, when they should be applied, and provides step‑by‑step MySQL commands—including single‑column, composite, and index‑hint examples—to improve query performance on large datasets.

Database PerformanceSQLindexing
0 likes · 6 min read
Using Indexes to Optimize MySQL Queries
ITPUB
ITPUB
Apr 20, 2023 · Databases

Understanding Redis Blocking: Commands, Persistence, and Big Key Pitfalls

This article explains why certain Redis commands, persistence mechanisms, and large keys can block the single‑threaded server, detailing the underlying causes, how to detect big keys, and practical ways to mitigate blocking in production environments.

AOFBig KeyBlocking
0 likes · 8 min read
Understanding Redis Blocking: Commands, Persistence, and Big Key Pitfalls
Java Architect Essentials
Java Architect Essentials
Apr 18, 2023 · Databases

How MySQL Calculates Execution Cost for Index Selection and Why It May Choose Suboptimal Plans

This article explains how MySQL estimates the I/O and CPU costs of different indexes when executing COUNT(*) and other queries, demonstrates the calculations with a concrete example on a 100k‑row table, and shows why the optimizer sometimes picks a full‑table scan over a seemingly better index.

Database PerformanceEXPLAINIndex Optimization
0 likes · 10 min read
How MySQL Calculates Execution Cost for Index Selection and Why It May Choose Suboptimal Plans
IT Architects Alliance
IT Architects Alliance
Apr 11, 2023 · Databases

MySQL Execution Process and Comprehensive Optimization Best Practices

This article explains MySQL's query execution flow and provides extensive best‑practice guidelines—including naming conventions, storage engine choices, primary‑key design, character set settings, query writing tips, indexing strategies, batch operations, and performance‑enhancing techniques—to help developers write efficient and maintainable SQL code.

Best PracticesDatabase PerformanceQuery Tuning
0 likes · 24 min read
MySQL Execution Process and Comprehensive Optimization Best Practices
ITPUB
ITPUB
Apr 4, 2023 · Databases

Mastering MySQL Replication: Principles, Lag Fixes, and Failover Strategies

This article explains MySQL master‑slave replication fundamentals, the binlog‑based data flow, common consistency and latency problems, practical solutions such as row or mixed binlog formats, caching and query routing, and the trade‑offs of one‑master‑one‑slave versus one‑master‑many‑slaves architectures.

Database PerformanceMaster‑Slavemysql
0 likes · 11 min read
Mastering MySQL Replication: Principles, Lag Fixes, and Failover Strategies
Top Architect
Top Architect
Mar 29, 2023 · Databases

MySQL Query Optimization and Index Tuning: From Subqueries to Joins and Composite Indexes

This article demonstrates MySQL query performance issues with large tables, shows how a subquery without indexes can take hours, and walks through step‑by‑step optimizations including adding single‑column indexes, converting to EXISTS and JOIN queries, creating composite indexes, covering indexes, and sorting improvements, achieving speedups of thousands of times.

Composite IndexDatabase PerformanceQuery Tuning
0 likes · 12 min read
MySQL Query Optimization and Index Tuning: From Subqueries to Joins and Composite Indexes
dbaplus Community
dbaplus Community
Mar 19, 2023 · Databases

Should MySQL Run in Docker? Risks and Strategies Explained

While containers and Docker dominate modern infrastructure, this article critically examines whether MySQL should be containerized, highlighting data safety, performance, statefulness, and resource isolation concerns, and offering practical strategies such as separating data from the program, using lightweight or distributed databases, and appropriate deployment choices.

Cloud NativeContainerizationData Safety
0 likes · 7 min read
Should MySQL Run in Docker? Risks and Strategies Explained
ITPUB
ITPUB
Mar 7, 2023 · Databases

Why Oracle Auto Tasks Slow Your Database and How to Fix Them

The article explains how Oracle's built‑in auto‑task jobs run during specific weekday and weekend windows, why they can cause performance degradation by changing execution plans or consuming resources, and provides practical steps to diagnose, adjust windows, and selectively disable problematic tasks.

Auto TaskDatabase PerformanceOracle
0 likes · 9 min read
Why Oracle Auto Tasks Slow Your Database and How to Fix Them
ITPUB
ITPUB
Feb 28, 2023 · Databases

Master MySQL Query Performance: A Step‑by‑Step SQL Optimization Guide

This article explains how to prepare and tune MySQL SQL statements before execution, covering logical and physical optimization, table and view analysis, join‑key checks, query rewriting, and execution‑plan evaluation to achieve faster, more efficient database queries.

Database PerformanceQuery TuningSQL optimization
0 likes · 9 min read
Master MySQL Query Performance: A Step‑by‑Step SQL Optimization Guide
Selected Java Interview Questions
Selected Java Interview Questions
Feb 24, 2023 · Databases

Why Avoid MySQL Joins and Use Application‑Level Data Merging

The article advises against using MySQL subqueries and joins for large datasets, recommending single‑table queries merged in application code, and outlines the benefits, scenarios, drawbacks, and alternatives for application‑level data association while also noting when joins can still be useful.

Application LayerDatabase PerformanceJoins
0 likes · 6 min read
Why Avoid MySQL Joins and Use Application‑Level Data Merging
Aikesheng Open Source Community
Aikesheng Open Source Community
Feb 15, 2023 · Databases

SQL Statement Optimization Practices for MySQL

This article explains how DBAs can improve MySQL query performance by applying logical and physical optimizations, analyzing table structures, indexes, and execution plans, and provides a step‑by‑step checklist for diagnosing and rewriting slow or poorly optimized SQL statements.

Database PerformanceQuery TuningSQL optimization
0 likes · 8 min read
SQL Statement Optimization Practices for MySQL
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.

Database PerformanceSQL optimizationexecution plan
0 likes · 17 min read
Why the Same SQL Has Different Execution Plans in QA and DEV Environments and How to Optimize It
JD Cloud Developers
JD Cloud Developers
Feb 6, 2023 · Databases

Master MySQL Performance: Indexing, Execution Plans, and Real-World Cases

This article explains why database performance monitoring becomes critical as data grows, outlines common optimization techniques, dives into MySQL's storage architecture and index structures, and shares three real‑world cases that illustrate how proper indexing and execution‑plan analysis can prevent severe latency and CPU spikes.

Database PerformanceIndex OptimizationSQL Tuning
0 likes · 15 min read
Master MySQL Performance: Indexing, Execution Plans, and Real-World Cases
Tencent Database Technology
Tencent Database Technology
Jan 31, 2023 · Databases

Common MySQL OOM Scenarios and TDSQL‑C Memory Optimization Strategies

This article examines typical MySQL out‑of‑memory (OOM) situations in production, explains how to diagnose memory usage with Performance Schema and other tools, and presents a series of TDSQL‑C‑specific optimization techniques—including server‑parameter tuning, process‑list monitoring, cold‑page detection, buffer‑pool limits, and dynamic resizing—to mitigate OOM risks.

Database PerformanceMemory OptimizationOOM
0 likes · 12 min read
Common MySQL OOM Scenarios and TDSQL‑C Memory Optimization Strategies
MaGe Linux Operations
MaGe Linux Operations
Jan 22, 2023 · Databases

How to Fix Deep Pagination Slow Queries in MySQL: A Real Incident Walkthrough

This article recounts a real‑world MySQL pagination outage, detailing how deep‑page queries caused massive CPU spikes, the step‑by‑step investigation, and the series of SQL redesigns—including ID‑based range queries, sub‑queries, scroll queries and join‑based solutions—that finally restored stable performance.

Database PerformanceSQL optimizationdeep pagination
0 likes · 10 min read
How to Fix Deep Pagination Slow Queries in MySQL: A Real Incident Walkthrough
Tencent Database Technology
Tencent Database Technology
Jan 18, 2023 · Databases

In‑depth Analysis of MySQL InnoDB Buffer Pool Architecture and Performance Optimizations

This article provides a comprehensive technical walkthrough of MySQL‑8.0.22 InnoDB's buffer pool, detailing its physical and logical structures, page management algorithms, fast read/write optimizations, change buffer, adaptive hash index, and the complete page‑read call stack, complete with code excerpts and diagrams.

CachingDatabase PerformanceInnoDB
0 likes · 47 min read
In‑depth Analysis of MySQL InnoDB Buffer Pool Architecture and Performance Optimizations
ByteDance Data Platform
ByteDance Data Platform
Jan 4, 2023 · Databases

How ByteHouse Enhances ClickHouse with Resource Isolation and High Availability

This article explains how ByteHouse, an enhanced version of ClickHouse used at ByteDance, adds full upsert support, multi‑table joins, high‑availability features, and, most importantly, a Resource Group mechanism that provides fine‑grained CPU, memory, and concurrency isolation to improve query performance and stability.

ByteHouseClickHouseConcurrency Control
0 likes · 8 min read
How ByteHouse Enhances ClickHouse with Resource Isolation and High Availability
Aikesheng Open Source Community
Aikesheng Open Source Community
Dec 27, 2022 · Databases

Understanding How MySQL Converts Uncorrelated Subqueries to Correlated Subqueries and Their Execution Plans

This article explains the process by which MySQL transforms uncorrelated IN subqueries into correlated subqueries, details the differences in EXPLAIN output columns, explores optimizer trace information, compares materialization versus correlated execution costs, and provides best‑practice recommendations for writing efficient queries.

Database PerformanceOptimizer_tracemysql
0 likes · 15 min read
Understanding How MySQL Converts Uncorrelated Subqueries to Correlated Subqueries and Their Execution Plans
MaGe Linux Operations
MaGe Linux Operations
Dec 22, 2022 · Databases

MySQL 5.7 Parallel Replication: Architecture, Config & Performance Gains

This article explains MySQL 5.7’s enhanced multi‑threaded slave (MTS) parallel replication, contrasting it with the schema‑based approach of MySQL 5.6, detailing its group‑commit mechanism, configuration parameters, GTID handling, performance testing, and practical tuning tips for optimal replication throughput.

Database PerformanceGTIDMTS
0 likes · 14 min read
MySQL 5.7 Parallel Replication: Architecture, Config & Performance Gains
ITPUB
ITPUB
Dec 18, 2022 · Databases

Why ClickHouse Is So Fast: Deep Dive into Storage and Compute Engine Optimizations

This article explains how ClickHouse achieves high query performance by leveraging storage‑engine designs such as pre‑sorting, columnar layout, and block‑level compression, and by exploiting a vectorized compute engine while avoiding joins and using built‑in functions.

Big DataClickHouseColumnar Storage
0 likes · 9 min read
Why ClickHouse Is So Fast: Deep Dive into Storage and Compute Engine Optimizations
Zuoyebang Tech Team
Zuoyebang Tech Team
Nov 25, 2022 · Databases

Cut Storage Costs 400%: Inside BitalosDB’s High‑Performance KV Engine

An in‑depth look at BitalosDB, the home‑grown NoSQL storage engine behind Zuoyebang’s massive KV traffic, covering its novel IO architecture, KV‑separation design, Raft‑based consistency, multi‑cloud CRDT replication, and benchmark results that show up to 400% cost savings versus standard Redis.

CRDTDatabase PerformanceKV storage
0 likes · 11 min read
Cut Storage Costs 400%: Inside BitalosDB’s High‑Performance KV Engine
Programmer DD
Programmer DD
Oct 26, 2022 · Databases

Boost MySQL Performance: 20 Proven SQL Optimization Tips

This article presents a comprehensive set of MySQL optimization techniques, covering everything from avoiding SELECT * and OR conditions to proper index usage, join strategies, batch operations, and query planning, all aimed at reducing resource consumption, improving execution speed, and ensuring reliable database design.

Database PerformanceQuery TuningSQL optimization
0 likes · 19 min read
Boost MySQL Performance: 20 Proven SQL Optimization Tips
Liangxu Linux
Liangxu Linux
Oct 7, 2022 · Databases

Why MyISAM’s Table Locks Can Outperform InnoDB in High‑Concurrency Scenarios

This article provides a systematic deep‑dive into MySQL table‑lock mechanics, comparing storage engines, explaining the advantages of table locks, detailing lock acquisition and release rules, showing how to monitor lock statistics, and revealing why MyISAM can deliver superior performance for heavy concurrent insert and select workloads despite using only table‑level locking.

Database PerformanceLock MechanismMyISAM
0 likes · 7 min read
Why MyISAM’s Table Locks Can Outperform InnoDB in High‑Concurrency Scenarios
AntTech
AntTech
Sep 30, 2022 · Databases

OceanBase: Distributed Architecture, High‑Performance Storage Engine, Paxos‑Based 2PC, and Record‑Breaking TPC‑C Benchmarks

The article reviews OceanBase's distributed relational database design, its integrated architecture, high‑compression LSM‑tree storage engine, Paxos‑enhanced two‑phase commit protocol, and how these innovations enabled the system to set successive world records in the TPC‑C benchmark, illustrating China's growing database capabilities.

Database PerformanceOceanBasePaxos
0 likes · 18 min read
OceanBase: Distributed Architecture, High‑Performance Storage Engine, Paxos‑Based 2PC, and Record‑Breaking TPC‑C Benchmarks
Wukong Talks Architecture
Wukong Talks Architecture
Sep 27, 2022 · Databases

MySQL Large Table Index Optimization and Delete Performance Tuning

This article analyzes a MySQL large‑table performance issue caused by slow queries and full‑table scans, demonstrates using pt‑query‑digest, explains execution plans, proposes index redesign, shows backup and restore with mydumper, compares online DDL and pt‑osc, and presents batch delete strategies to reduce latency.

Batch DeleteDDLDatabase Performance
0 likes · 13 min read
MySQL Large Table Index Optimization and Delete Performance Tuning
Architecture Digest
Architecture Digest
Sep 15, 2022 · Databases

Case Study: Resolving Deep Pagination Slow Queries in MySQL

This article walks through a real‑world incident caused by deep pagination in a MySQL‑based system, detailing the timeline of symptoms, root‑cause analysis, and a series of optimizations—including ID‑based queries, range scans, and rolling cursor techniques—that ultimately restored stable performance.

Database PerformanceIndex OptimizationSQL
0 likes · 10 min read
Case Study: Resolving Deep Pagination Slow Queries in MySQL
dbaplus Community
dbaplus Community
Aug 21, 2022 · Databases

Master MySQL Performance: Architecture, Indexes, and Advanced Query Tuning

This comprehensive guide walks you through MySQL's core architecture, explains why SQL optimization matters, details index types and creation methods, demystifies EXPLAIN output, and provides practical examples and best‑practice tips to dramatically improve query performance.

Database PerformanceExplain PlanQuery Tuning
0 likes · 34 min read
Master MySQL Performance: Architecture, Indexes, and Advanced Query Tuning
dbaplus Community
dbaplus Community
Aug 14, 2022 · Databases

Master MySQL Performance: 12 Proven SQL Optimization Techniques

This article explains MySQL's internal architecture and provides twelve practical SQL optimization strategies—including avoiding subqueries, using IN instead of OR, efficient pagination, minimizing ORDER BY, batch inserts, selective column retrieval, proper JOIN handling, and index best practices—to dramatically improve database query performance.

Database PerformanceQuery TuningSQL optimization
0 likes · 20 min read
Master MySQL Performance: 12 Proven SQL Optimization Techniques
Java Backend Technology
Java Backend Technology
Aug 5, 2022 · Databases

Why Store IPv4 Addresses as UNSIGNED INT in MySQL? Benefits, Drawbacks & Java Example

This article explains why using a 32‑bit UNSIGNED INT to store IPv4 addresses in MySQL saves space and improves query performance, outlines the trade‑offs such as readability, shows MySQL conversion functions, discusses IPv6 alternatives, and provides Java code for converting between string and numeric representations.

Database PerformanceIP addressJava
0 likes · 5 min read
Why Store IPv4 Addresses as UNSIGNED INT in MySQL? Benefits, Drawbacks & Java Example
Top Architect
Top Architect
Jul 22, 2022 · Databases

Why and How to Optimize Slow SQL Queries in MySQL

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.

Database PerformanceIndexesSQL optimization
0 likes · 10 min read
Why and How to Optimize Slow SQL Queries in MySQL
Aikesheng Open Source Community
Aikesheng Open Source Community
Jul 11, 2022 · Databases

Avoiding Global Mutex Contention in MySQL SHOW PROCESSLIST with performance_schema_show_processlist

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.

Database PerformanceSHOW PROCESSLISTSQL optimization
0 likes · 6 min read
Avoiding Global Mutex Contention in MySQL SHOW PROCESSLIST with performance_schema_show_processlist
Open Source Linux
Open Source Linux
Jun 20, 2022 · Databases

Understanding Buffer vs Cache and MySQL’s Flush Strategies

This article explains the fundamental differences between buffers and caches, details MySQL’s multi‑layer buffer architecture, and examines how write‑through, write‑back, and Direct I/O affect data durability and performance through various InnoDB flush parameters.

CacheDatabase PerformanceFlush
0 likes · 12 min read
Understanding Buffer vs Cache and MySQL’s Flush Strategies
ITPUB
ITPUB
Jun 14, 2022 · Databases

12 Common MySQL Slow‑Query Causes and How to Fix Them

This article enumerates twelve typical reasons why MySQL queries become slow—such as missing or ineffective indexes, deep pagination, massive tables, excessive joins, large IN lists, dirty pages, file‑based ORDER BY, and hardware limits—and provides concrete SQL examples and step‑by‑step optimization techniques to resolve each issue.

Database PerformanceIndex Optimizationmysql
0 likes · 33 min read
12 Common MySQL Slow‑Query Causes and How to Fix Them
dbaplus Community
dbaplus Community
Jun 12, 2022 · Databases

12 Common MySQL Slow‑Query Triggers and Practical Fixes

This article enumerates twelve typical reasons why MySQL queries become slow—ranging from missing or ineffective indexes, type conversions, OR/LIKE patterns, composite‑index misuse, built‑in functions, large tables, deep pagination, join overload, oversized IN clauses, dirty‑page flushing, order‑by filesort, lock contention, delete‑in pitfalls, and group‑by temp tables—and provides concrete SQL examples, execution‑plan explanations, and optimization techniques for each case.

Database PerformanceSQL Tuningmysql
0 likes · 28 min read
12 Common MySQL Slow‑Query Triggers and Practical Fixes
dbaplus Community
dbaplus Community
May 10, 2022 · Databases

How Meituan Optimizes MySQL Slow Queries with Cost‑Based Index Recommendations

This article explains how Meituan tackles MySQL slow‑query problems by leveraging the database cost‑based optimizer to generate index suggestions, evaluate their quality, and operate a full‑stack governance workflow that includes data collection, statistical modeling, validation, and continuous improvement.

Database PerformanceIndex Recommendationcost‑based optimizer
0 likes · 23 min read
How Meituan Optimizes MySQL Slow Queries with Cost‑Based Index Recommendations
Java Backend Technology
Java Backend Technology
May 2, 2022 · Databases

When Does a MySQL Table Hit Its Limits? Insights and Demo

This article explains that MySQL imposes no fixed row limit, discusses the common 5‑million‑row myth and Alibaba's 2 GB recommendation, shows a Java insertion test that slows after millions of rows, and highlights how InnoDB buffer size and memory constraints affect table performance.

Data InsertionDatabase PerformanceInnoDB
0 likes · 4 min read
When Does a MySQL Table Hit Its Limits? Insights and Demo
dbaplus Community
dbaplus Community
Apr 24, 2022 · Databases

Eight Practical Strategies to Tackle Database Performance Bottlenecks

This article outlines a systematic approach for backend engineers to identify root causes of database slowness and presents eight concrete optimization techniques—ranging from data reduction and space‑for‑performance trade‑offs to appropriate storage system selection—while explaining when each method is most effective and what trade‑offs to consider.

CQRSCachingDatabase Performance
0 likes · 18 min read
Eight Practical Strategies to Tackle Database Performance Bottlenecks
dbaplus Community
dbaplus Community
Apr 17, 2022 · Databases

Why Killing Slow Queries Often Fails and a Safer MySQL Timeout Solution

The article examines how slow‑query‑induced avalanches arise in MySQL, explains why naïvely killing slow queries is fraught with accuracy, automation and responsibility issues, and proposes a signature‑based timeout mechanism that lets applications safely abort problematic queries.

DBADatabase Performancemysql
0 likes · 17 min read
Why Killing Slow Queries Often Fails and a Safer MySQL Timeout Solution
Architecture & Thinking
Architecture & Thinking
Apr 14, 2022 · Databases

Mastering MySQL Indexes: Boost Query Performance with B‑Tree, Prefix, and Composite Strategies

This article explains how MySQL stores indexes using B+ trees, how to evaluate index selectivity and prefix length, the importance of the leftmost principle for composite indexes, and practical techniques such as covering indexes, index condition pushdown, and avoiding common pitfalls that render indexes ineffective, all illustrated with real‑world query examples and performance measurements.

B+TreeDatabase PerformanceSQL
0 likes · 36 min read
Mastering MySQL Indexes: Boost Query Performance with B‑Tree, Prefix, and Composite Strategies
Big Data Technology & Architecture
Big Data Technology & Architecture
Apr 13, 2022 · Databases

Common MySQL Index Failure Scenarios and Optimization Techniques

This article explains typical MySQL index failure cases such as left‑most matching truncation, implicit conversion, IN + ORDER BY, range queries, leading wildcard, OR conditions, function usage, NOT IN/!=, low selectivity, and index fragmentation, and provides practical optimization strategies including index redesign, covering indexes, and pagination improvements.

Database PerformanceIndex OptimizationQuery Tuning
0 likes · 17 min read
Common MySQL Index Failure Scenarios and Optimization Techniques