BestHub
Sign in
Tagged articles

InnoDB

905 articles · Page 1 of 10
Java Tech Workshop
Java Tech Workshop
Aug 18, 2026 · Backend Development

Beyond Adding Indexes: From Disk Pages to B+Tree – Master MySQL Index Design and Operation

This article explains why indexes speed up queries by reducing disk I/O, dives into MySQL's page structure and B+Tree evolution, compares clustered and secondary indexes, clarifies composite index rules, lists common index‑misuse scenarios, and provides seven practical guidelines for designing efficient MySQL indexes.

B+TreeClustered IndexComposite Index
0 likes · 20 min read
Beyond Adding Indexes: From Disk Pages to B+Tree – Master MySQL Index Design and Operation
Raymond Ops
Raymond Ops
Aug 17, 2026 · Databases

How to Diagnose and Fix MySQL Deadlocks Without Just Restarting the Service

This article explains why MySQL deadlocks occur in production, distinguishes them from simple lock waits, and provides a step‑by‑step guide—including enabling deadlock logging, analyzing InnoDB lock types, and applying four practical solutions such as distributed locks, unique constraints, isolation‑level changes, and SQL reordering—to reliably troubleshoot and prevent deadlocks.

DeadlockInnoDBMySQL
0 likes · 31 min read
How to Diagnose and Fix MySQL Deadlocks Without Just Restarting the Service
Woodpecker Software Testing
Woodpecker Software Testing
Aug 14, 2026 · Databases

How to Diagnose Database Performance Test Failures: Real‑World Cases and a Three‑Layer Method

The article presents a systematic, three‑layer approach to uncovering root causes of database performance test failures, illustrating each step with real‑world financial and e‑commerce case studies, key metrics to monitor, reproducible fault injection techniques, and a baseline‑driven change‑gate process.

Chaos EngineeringDatabaseInnoDB
0 likes · 9 min read
How to Diagnose Database Performance Test Failures: Real‑World Cases and a Three‑Layer Method
Raymond Ops
Raymond Ops
Aug 6, 2026 · Databases

Diagnosing and Eliminating MySQL Deadlocks in Production

This article explains how MySQL deadlocks arise, details the four necessary conditions, compares lock types, shows how to enable detailed deadlock logging, query lock metadata, interpret logs, and provides practical code‑level and configuration strategies to prevent and resolve common deadlock scenarios in production environments.

DeadlockInnoDBMySQL
0 likes · 20 min read
Diagnosing and Eliminating MySQL Deadlocks in Production
Ray's Galactic Tech
Ray's Galactic Tech
Aug 1, 2026 · Databases

Beyond CRUD: Full‑Scale Production Guide for MySQL 8.4 LTS

This article walks through a complete production‑grade view of MySQL 8.4 LTS, explaining how a chain of traffic spikes, connection‑pool exhaustion, long transactions and replication lag can cause an avalanche, and then detailing the five core modules, seven production mechanisms, architectural evolution steps, incident post‑mortems, and concrete configuration and code examples to build a resilient MySQL service.

BackupHigh AvailabilityInnoDB
0 likes · 36 min read
Beyond CRUD: Full‑Scale Production Guide for MySQL 8.4 LTS
ITPUB
ITPUB
Jul 15, 2026 · Databases

Is count(*) Really the Slowest? MySQL Count Performance Explained

The article analyzes how MySQL executes different COUNT() forms—count(*), count(1), count(primary‑key), and count(column)—showing that count(*) and count(1) have identical performance, count(column) is the slowest, and offers indexing and approximation tips for large tables.

COUNTIndexInnoDB
0 likes · 10 min read
Is count(*) Really the Slowest? MySQL Count Performance Explained
Ops Community
Ops Community
Jul 12, 2026 · Databases

Which MySQL Files Can Be Safely Deleted When Disk Space Is Low?

When MySQL runs out of disk space, the safest approach is to identify the full filesystem, examine data, binlog, temporary and log directories, and use SQL‑based cleanup commands like PURGE BINARY LOGS while never manually removing critical files such as ibdata1, ib_logfile* or active binlogs.

BackupInnoDBMySQL
0 likes · 16 min read
Which MySQL Files Can Be Safely Deleted When Disk Space Is Low?
samdeepthink
samdeepthink
Jul 3, 2026 · Databases

MySQL Index Interview Guide: From B+ Trees to Index Design

This article explains MySQL index fundamentals—from the B+‑tree storage engine and InnoDB’s clustered and secondary indexes to query execution, common index‑failure scenarios, and practical design principles for building effective indexes in interview settings.

B+TreeEXPLAINIndex
0 likes · 25 min read
MySQL Index Interview Guide: From B+ Trees to Index Design
samdeepthink
samdeepthink
Jun 29, 2026 · Databases

Does MVCC Actually Prevent Phantom Reads? A Clear Explanation

The article explains that MVCC in MySQL InnoDB prevents phantom reads for snapshot (ordinary SELECT) queries by using ReadView, while write‑conflict scenarios that appear as phantom reads are handled by unique index checks and gap locks, highlighting the distinction between snapshot and current reads.

Gap LockInnoDBMVCC
0 likes · 5 min read
Does MVCC Actually Prevent Phantom Reads? A Clear Explanation
Cloud Architecture
Cloud Architecture
Jun 29, 2026 · Databases

Deep Guide to MySQL Index Failure: From Core Mechanics to High‑Concurrency Production Practices

This comprehensive guide explains why seemingly indexed MySQL queries can still cause severe latency spikes in high‑traffic systems, explores the underlying InnoDB structures and optimizer cost model, enumerates twelve common failure patterns with concrete SQL examples, and provides a production‑grade methodology for diagnosing, engineering, and automating index governance.

InnoDBMySQLhigh concurrency
0 likes · 39 min read
Deep Guide to MySQL Index Failure: From Core Mechanics to High‑Concurrency Production Practices
samdeepthink
samdeepthink
Jun 25, 2026 · Databases

How Many Locks Does a Single UPDATE Acquire in MySQL 8.0?

An UPDATE in MySQL 8.0 acquires three distinct locks—a server‑level metadata lock, an InnoDB intention exclusive lock (IX), and a row‑level exclusive lock—so understanding the three‑layer lock architecture (metadata, intention, row) helps both interview preparation and troubleshooting.

InnoDBIntention LockLocks
0 likes · 14 min read
How Many Locks Does a Single UPDATE Acquire in MySQL 8.0?
ITPUB
ITPUB
Jun 22, 2026 · Databases

How an Unindexed UPDATE Can Lock Your Whole MySQL Table and Crash Production

The article explains how an UPDATE without an indexed WHERE clause triggers InnoDB’s next‑key locks, effectively locking the entire table, shows transaction examples that cause blocking, and recommends enabling sql_safe_updates or using FORCE INDEX to ensure the statement uses an index scan.

InnoDBMySQLUPDATE
0 likes · 8 min read
How an Unindexed UPDATE Can Lock Your Whole MySQL Table and Crash Production
Cloud Architecture
Cloud Architecture
Jun 11, 2026 · Databases

MySQL Deadlock Elimination: 9 Golden Rules and Production‑Ready Defense

Deadlocks in MySQL are not random glitches but the inevitable result of uncontrolled concurrency, lock granularity, transaction boundaries, and resource ordering; this article explains the underlying lock mechanisms, common deadlock scenarios, and presents nine practical, production‑grade rules for architecture design, lock management, and observability to keep deadlocks within acceptable SLA limits.

DeadlockInnoDBMySQL
0 likes · 31 min read
MySQL Deadlock Elimination: 9 Golden Rules and Production‑Ready Defense
Top Architect
Top Architect
Jun 5, 2026 · Databases

Eliminate LIKE% in MySQL: Use Full‑Text Search for Efficient Fuzzy Queries

This article explains why using LIKE% for fuzzy searches in MySQL is inefficient, introduces InnoDB full‑text search (available since MySQL 5.6), describes inverted indexes, shows how to create and query full‑text indexes with natural language, boolean, and query‑expansion modes, discusses relevance calculation, stopwords, token‑size parameters, and provides the syntax for dropping full‑text indexes.

Boolean ModeFull-text SearchInnoDB
0 likes · 12 min read
Eliminate LIKE% in MySQL: Use Full‑Text Search for Efficient Fuzzy Queries
Cloud Architecture
Cloud Architecture
Jun 2, 2026 · Databases

Advanced MySQL Production Practices: From Kernel Principles to High‑Concurrency Implementation

This guide presents a production‑grade MySQL playbook for senior developers, architects, and DB engineers, covering kernel internals, architecture governance, connection‑pool sizing, InnoDB tuning, index design, transaction and lock handling, replication, sharding, distributed transactions, change management, observability, security, cloud‑native deployment, and a complete best‑practice checklist.

Connection PoolInnoDBMySQL
0 likes · 42 min read
Advanced MySQL Production Practices: From Kernel Principles to High‑Concurrency Implementation
Wukong Talks Architecture
Wukong Talks Architecture
May 28, 2026 · Databases

Understanding MySQL InnoDB Locks: Types, Queries, and Common Pitfalls

This article explains InnoDB's lock mechanisms—including table, intention, row, GAP, next‑key, and auto‑increment locks—shows how to inspect them via performance_schema tables, demonstrates lock behavior under different isolation levels with concrete SQL examples, and clarifies lock compatibility rules.

Gap LockInnoDBIsolation Levels
0 likes · 20 min read
Understanding MySQL InnoDB Locks: Types, Queries, and Common Pitfalls
Smart Sea Tide
Smart Sea Tide
May 22, 2026 · Databases

SQL Query Optimization: Cutting a 9M‑Row Scan from 17 s to 300 ms

The article analyzes why a MySQL LIMIT OFFSET query on a 9.5 million‑row table takes 16 seconds, demonstrates how moving the filter into a sub‑query that returns only primary‑key IDs and joining back reduces execution to 0.35 seconds, and validates the theory by measuring InnoDB buffer‑pool page accesses.

InnoDBLIMIT OffsetMySQL
0 likes · 9 min read
SQL Query Optimization: Cutting a 9M‑Row Scan from 17 s to 300 ms
dbaplus Community
dbaplus Community
May 21, 2026 · Databases

Is count(*) Really the Slowest? MySQL COUNT Performance Debunked

The article explains how MySQL implements COUNT(1), COUNT(*), COUNT(primary_key) and COUNT(column), showing that COUNT(*) and COUNT(1) have identical performance, COUNT(column) is the slowest, and provides indexing and approximation tips for large InnoDB tables.

COUNTIndexInnoDB
0 likes · 9 min read
Is count(*) Really the Slowest? MySQL COUNT Performance Debunked
dbaplus Community
dbaplus Community
May 11, 2026 · Databases

Why an Unindexed UPDATE Can Crash Your Business—and How to Prevent It

The article explains how running an UPDATE without an indexed WHERE clause in InnoDB under repeatable‑read can trigger full‑table next‑key locks that block other statements, halt the service, and how using indexed predicates, checking execution plans, enabling sql_safe_updates, or forcing an index can avoid the disaster.

IndexInnoDBMySQL
0 likes · 8 min read
Why an Unindexed UPDATE Can Crash Your Business—and How to Prevent It
Architect Chen
Architect Chen
May 4, 2026 · Databases

What’s the Difference Between MySQL Redo Log and Binlog? (Interview Insight)

The article explains that MySQL redo log operates at the InnoDB engine layer to ensure transaction durability and crash recovery, while binlog works at the server layer to record logical changes for replication, archiving, and point‑in‑time recovery, highlighting their distinct layers, purposes, content, and write mechanisms.

Crash RecoveryInnoDBMySQL
0 likes · 4 min read
What’s the Difference Between MySQL Redo Log and Binlog? (Interview Insight)
MaGe Linux Operations
MaGe Linux Operations
Apr 27, 2026 · Databases

Production MySQL Deadlocks: Diagnosis Strategies and Permanent Fixes

The article explains how MySQL InnoDB deadlocks occur, details the four necessary conditions, shows how to enable full deadlock logging, demonstrates queries against information_schema and performance_schema, and provides concrete scenarios with code‑level solutions to prevent and resolve deadlocks in production environments.

DeadlockInnoDBMySQL
0 likes · 22 min read
Production MySQL Deadlocks: Diagnosis Strategies and Permanent Fixes
Java Tech Workshop
Java Tech Workshop
Apr 21, 2026 · Databases

Optimizing SpringBoot MySQL Indexes: From Slow Query Logs to InnoDB Explain Analysis

This guide walks through why caching alone can't solve performance bottlene bottlenecks, shows how to enable MySQL slow‑query logging in SpringBoot, analyzes slow SQL with tools like mysqldumpslow and pt‑query‑digest, explains the full EXPLAIN output, and dives into InnoDB B‑tree, clustered vs secondary indexes, covering indexes, and common causes of index loss.

Covering IndexEXPLAINInnoDB
0 likes · 31 min read
Optimizing SpringBoot MySQL Indexes: From Slow Query Logs to InnoDB Explain Analysis
Architect's Guide
Architect's Guide
Apr 14, 2026 · Databases

What Happens When MySQL Auto‑Increment IDs Reach Their Limits?

This article explains how MySQL handles auto‑increment primary keys, InnoDB internal row_id, Xid, trx_id, and thread_id when their numeric limits are reached, illustrating the resulting errors, data overwrites, and potential consistency bugs with practical SQL examples and verification steps.

InnoDBMySQLXid
0 likes · 13 min read
What Happens When MySQL Auto‑Increment IDs Reach Their Limits?
dbaplus Community
dbaplus Community
Feb 27, 2026 · Databases

Understanding MySQL Locks: From Global to Row‑Level and Deadlock Prevention

The article explains why concurrent transactions cause data inconsistencies, describes MySQL’s lock hierarchy—including global, table, and row locks—covers AUTO_INCREMENT locking, illustrates lock compatibility tables, details common deadlock scenarios, and offers practical strategies such as fixed access order, optimistic locking, short transactions, proper indexing, and isolation‑level tuning to prevent deadlocks.

DeadlockInnoDBLocks
0 likes · 18 min read
Understanding MySQL Locks: From Global to Row‑Level and Deadlock Prevention
Senior Xiao Ying
Senior Xiao Ying
Feb 21, 2026 · Databases

Boost MySQL Performance: Deep Parameter Tuning to Eliminate Slowness

This guide walks through MySQL’s core memory, I/O, connection, and system variable settings—explaining each parameter’s role, recommended values, and example commands—so you can systematically adjust the configuration, monitor key metrics, and achieve up to three‑fold performance gains.

Connection ManagementInnoDBMySQL
0 likes · 9 min read
Boost MySQL Performance: Deep Parameter Tuning to Eliminate Slowness
Architect Chen
Architect Chen
Feb 13, 2026 · Databases

Boost MySQL Performance: Proven Tuning, Indexing, and Scaling Strategies

This guide presents practical MySQL optimization techniques—including SQL and index refinement, InnoDB and connection parameter tuning, cache layer integration, and architectural scaling with read‑write splitting and sharding—to dramatically increase query throughput and reduce latency.

InnoDBMySQLScaling
0 likes · 6 min read
Boost MySQL Performance: Proven Tuning, Indexing, and Scaling Strategies
Ray's Galactic Tech
Ray's Galactic Tech
Jan 14, 2026 · Databases

Why MySQL’s B+Tree Indexes Power High‑Performance Queries

This article explains how MySQL implements indexes with B+Tree structures, why they outperform full table scans, the internal layout of leaf and internal nodes, insertion and split mechanics, range‑query processing, and practical optimization tips for clustered and secondary indexes.

B+TreeDatabaseIndex
0 likes · 11 min read
Why MySQL’s B+Tree Indexes Power High‑Performance Queries
Full-Stack Internet Architecture
Full-Stack Internet Architecture
Jan 8, 2026 · Databases

Understanding MySQL Transaction Isolation Levels with Practical Examples

This article explains MySQL's four transaction isolation levels—Read Uncommitted, Read Committed, Repeatable Read, and Serializable—by creating a simple table, running paired transactions, and showing how each level affects data visibility, concurrency, and potential anomalies such as dirty reads, non‑repeatable reads, and phantom reads.

Database ConcurrencyInnoDBMySQL
0 likes · 10 min read
Understanding MySQL Transaction Isolation Levels with Practical Examples
Senior Tony
Senior Tony
Dec 15, 2025 · Databases

Avoid the 5 Hidden MySQL Pitfalls That Can Kill Your Performance

This article reveals five common MySQL pitfalls—including uncontrolled InnoDB lock granularity, low default IOPS, misleading VARCHAR length handling, undersized InnoDB Buffer Pool, and a fragile Buffer Pool LRU algorithm—and offers practical configuration tips to prevent performance degradation.

InnoDBMySQLSQL
0 likes · 6 min read
Avoid the 5 Hidden MySQL Pitfalls That Can Kill Your Performance
ITPUB
ITPUB
Dec 8, 2025 · Databases

When Should You Store NULL vs Default Values in MySQL? A Deep Dive into Row Formats

This article examines MySQL’s handling of nullable columns, comparing the storage implications of saving NULL versus assigning default values, explains InnoDB row formats (REDUNDANT, COMPACT, DYNAMIC, COMPRESSED), and outlines the effects on storage space, indexing, and query behavior.

Database DesignInnoDBMySQL
0 likes · 6 min read
When Should You Store NULL vs Default Values in MySQL? A Deep Dive into Row Formats
SpringMeng
SpringMeng
Dec 8, 2025 · Databases

Why Using Snowflake IDs or UUIDs as MySQL Primary Keys Hurts Performance

This article experimentally compares auto‑increment, UUID and Snowflake‑generated primary keys in MySQL, analyzes their index structures, shows insertion‑time benchmarks, discusses the trade‑offs of each approach, and concludes that sequential auto‑increment keys deliver the best overall performance.

Index PerformanceInnoDBMySQL
0 likes · 10 min read
Why Using Snowflake IDs or UUIDs as MySQL Primary Keys Hurts Performance
Sohu Tech Products
Sohu Tech Products
Dec 3, 2025 · Databases

Why MySQL Uses MVCC: A Deep Dive into Concurrency, Isolation Levels, and Read Views

This article explains MySQL InnoDB’s MVCC mechanism, why it replaces traditional locking, details the four SQL isolation levels, illustrates dirty, non‑repeatable and phantom reads with examples, and breaks down the hidden fields, undo‑log chain, and read‑view algorithm that enable high‑concurrency, non‑blocking reads and writes.

Concurrency ControlInnoDBIsolation Levels
0 likes · 18 min read
Why MySQL Uses MVCC: A Deep Dive into Concurrency, Isolation Levels, and Read Views
IT Services Circle
IT Services Circle
Dec 1, 2025 · Databases

Master MySQL MVCC: Unlocking Concurrency, Locks, and Isolation Levels

This article explains why MySQL uses Multi-Version Concurrency Control, how it replaces traditional locking, the inner workings of hidden fields, undo logs, and read views, and details each transaction isolation level with practical SQL examples and common anomalies such as dirty, non‑repeatable, and phantom reads.

InnoDBMVCCMySQL
0 likes · 17 min read
Master MySQL MVCC: Unlocking Concurrency, Locks, and Isolation Levels
Java Tech Enthusiast
Java Tech Enthusiast
Dec 1, 2025 · Databases

Master MySQL Full‑Text Search: Inverted Index, Query Modes, and Practical Examples

This guide explains how InnoDB implements full‑text search with inverted indexes, shows how to create and drop full‑text indexes, demonstrates MATCH() AGAINST() syntax across natural language, boolean, and query‑expansion modes, and covers relevance scoring, stopwords, token size limits, and real‑world query examples.

Full-text SearchInnoDBMySQL
0 likes · 14 min read
Master MySQL Full‑Text Search: Inverted Index, Query Modes, and Practical Examples
Programmer1970
Programmer1970
Nov 23, 2025 · Databases

MySQL Core Concepts Interview Q&A: 10 Essential Topics (Part 2)

This article provides detailed explanations of ten core MySQL topics—including InnoDB lock upgrades, redo log mechanics, MVCC version chains, tablespace types, page splitting, optimizer plan selection, crash recovery, binlog vs redo log, adaptive hash index, and memory management—along with practical tips for avoiding pitfalls and tuning performance.

Adaptive Hash IndexCrash RecoveryInnoDB
0 likes · 22 min read
MySQL Core Concepts Interview Q&A: 10 Essential Topics (Part 2)
Programmer1970
Programmer1970
Nov 21, 2025 · Databases

10 Core MySQL InnoDB Interview Questions Explained

This article walks through ten essential MySQL InnoDB interview questions, covering why InnoDB uses B+ trees, MVCC isolation levels, two‑phase commit, gap locks, buffer‑pool LRU design, undo‑log mechanics, binlog formats, lock‑upgrade behavior, double‑write buffering, and the differences between clustered and non‑clustered indexes.

2PCB+TreeBinlog Formats
0 likes · 17 min read
10 Core MySQL InnoDB Interview Questions Explained
Ray's Galactic Tech
Ray's Galactic Tech
Nov 19, 2025 · Databases

Master InnoDB: Architecture, Indexes, Transactions & Performance Tuning

This comprehensive guide walks you through MySQL InnoDB's core architecture, storage structures, indexing mechanisms, transaction and concurrency control, crash recovery, backup options, and practical performance‑tuning techniques, providing clear explanations and real‑world SQL examples.

Database ArchitectureIndexingInnoDB
0 likes · 8 min read
Master InnoDB: Architecture, Indexes, Transactions & Performance Tuning
Sohu Tech Products
Sohu Tech Products
Nov 13, 2025 · Databases

Why MySQL Deadlocks Happen and How to Prevent Them

An in‑depth guide walks through MySQL InnoDB deadlock logs, explains two‑phase locking, reproduces the issue with step‑by‑step SQL commands, details lock types and compatibility, outlines common deadlock scenarios, and offers practical strategies and configuration tweaks to prevent and monitor deadlocks.

DeadlockInnoDBMySQL
0 likes · 21 min read
Why MySQL Deadlocks Happen and How to Prevent Them
MaGe Linux Operations
MaGe Linux Operations
Nov 6, 2025 · Databases

Boost MySQL InnoDB Performance 300%: Complete Buffer Pool Tuning Guide for 32GB‑256GB

This comprehensive guide walks you through MySQL InnoDB buffer pool optimization—from assessing current settings and calculating optimal sizes for 32 GB to 256 GB servers, to configuring instances, enabling pre‑warming, tuning dirty‑page flushing, monitoring key metrics, and troubleshooting common issues—to achieve up to a 300 % throughput increase in production environments.

InnoDBMySQLSQL
0 likes · 33 min read
Boost MySQL InnoDB Performance 300%: Complete Buffer Pool Tuning Guide for 32GB‑256GB
Architect's Must-Have
Architect's Must-Have
Nov 2, 2025 · Databases

Master MySQL Indexes: From Basics to B+Tree Optimization

This article explains MySQL indexes—how they speed up queries, their types, the inner workings of B‑Tree and B+Tree structures, page storage mechanics, and the trade‑offs between clustered and secondary indexes, providing practical insights for database optimization.

B+TreeIndexInnoDB
0 likes · 11 min read
Master MySQL Indexes: From Basics to B+Tree Optimization
Senior Brother's Insights
Senior Brother's Insights
Oct 31, 2025 · Databases

Master MySQL Transactions: ACID, Locks, and Practical Examples

This article explains what database transactions are, why they matter, details MySQL’s transaction commands, the ACID properties, isolation levels, lock types, MVCC, how to use InnoDB, handle errors, employ savepoints, and provides concrete SQL examples for creating, committing, rolling back, and managing transactions.

ACIDDatabase TransactionsInnoDB
0 likes · 12 min read
Master MySQL Transactions: ACID, Locks, and Practical Examples
Sohu Tech Products
Sohu Tech Products
Oct 29, 2025 · Databases

Understanding MySQL InnoDB Deadlocks: Causes, Lock Types & Prevention

This article examines MySQL InnoDB deadlocks by analyzing error logs, explaining the two‑phase locking protocol and various lock types, demonstrates how to reproduce a deadlock scenario, categorizes lock behaviors, and offers practical strategies to prevent and monitor deadlocks in database applications.

DatabaseInnoDBMySQL
0 likes · 19 min read
Understanding MySQL InnoDB Deadlocks: Causes, Lock Types & Prevention
Senior Brother's Insights
Senior Brother's Insights
Oct 27, 2025 · Databases

How Does MySQL Power High‑Performance OLTP Workloads?

This article explains what OLTP (Online Transaction Processing) is, outlines its key characteristics, and details how MySQL—through ACID‑compliant transactions, the InnoDB storage engine, various indexing strategies, fast locking mechanisms, query optimization, and high‑availability features—effectively supports high‑concurrency, low‑latency transactional workloads.

Database TransactionsHigh AvailabilityIndexing
0 likes · 9 min read
How Does MySQL Power High‑Performance OLTP Workloads?
Senior Brother's Insights
Senior Brother's Insights
Oct 23, 2025 · Databases

InnoDB vs MyISAM: Which MySQL Storage Engine Fits Your Needs?

This article compares MySQL's InnoDB and MyISAM storage engines across dimensions such as transaction support, locking, file structure, indexing, full‑text search, and COUNT(*) performance, helping developers choose the appropriate engine based on workload and consistency requirements.

Full-text SearchInnoDBMyISAM
0 likes · 13 min read
InnoDB vs MyISAM: Which MySQL Storage Engine Fits Your Needs?
dbaplus Community
dbaplus Community
Sep 22, 2025 · Databases

Why MySQL Tables Shouldn’t Exceed 10 Million Rows – A Deep Dive into InnoDB Pages & B+Tree Limits

This article explains why the industry advises keeping MySQL single‑table row counts below ten million by examining InnoDB’s 16KB page structure, B+‑tree indexing mechanics, fan‑out calculations, and how page size and row size together determine the practical limits and performance cliffs of large tables.

B+TreeDatabaseDesignIndexing
0 likes · 20 min read
Why MySQL Tables Shouldn’t Exceed 10 Million Rows – A Deep Dive into InnoDB Pages & B+Tree Limits
Senior Brother's Insights
Senior Brother's Insights
Sep 18, 2025 · Databases

Why MySQL Table Lookups Slow Queries and How to Eliminate Them

This article explains the concept of table lookups in MySQL, shows how they arise from using secondary indexes, provides concrete query examples, and offers practical optimization techniques such as creating covering indexes, reducing selected columns, and analyzing execution plans to improve performance.

Covering IndexInnoDBMySQL
0 likes · 9 min read
Why MySQL Table Lookups Slow Queries and How to Eliminate Them
DeWu Technology
DeWu Technology
Sep 15, 2025 · Databases

Why MySQL Single-Table Data Should Stay Under 20 Million Rows

This article explains how InnoDB stores data in 16 KB pages, how B+Tree indexes work, derives the formula for the maximum number of rows a single MySQL table can hold, and why the practical limit of about twenty million rows exists, along with performance implications and optimization tips.

B+TreeInnoDBMySQL
0 likes · 20 min read
Why MySQL Single-Table Data Should Stay Under 20 Million Rows
dbaplus Community
dbaplus Community
Sep 14, 2025 · Databases

Why MySQL Updates Sometimes Disappear: InnoDB Read View Explained

A nighttime incident revealed that a MySQL transaction updated a row but subsequent reads returned the old value, prompting a deep investigation that uncovered InnoDB’s repeatable‑read snapshot behavior, concurrent updates, and how MySQL may skip updates when data appears unchanged, along with reproducible steps and mitigation advice.

DatabaseInnoDBMySQL
0 likes · 16 min read
Why MySQL Updates Sometimes Disappear: InnoDB Read View Explained
Aikesheng Open Source Community
Aikesheng Open Source Community
Sep 10, 2025 · Databases

Why SELECT … FOR UPDATE Still Reads the Primary Key: MySQL Index Scan Deep Dive

This article examines why a SELECT … FOR UPDATE query that appears to use a covering index in MySQL actually performs a table‑row lookup, detailing indirect evidence from performance_schema locks and direct proof through InnoDB source code, and explains the necessity of accessing the primary key for transaction isolation.

Database InternalsIndex ScanInnoDB
0 likes · 9 min read
Why SELECT … FOR UPDATE Still Reads the Primary Key: MySQL Index Scan Deep Dive
Raymond Ops
Raymond Ops
Sep 7, 2025 · Databases

Understanding MySQL 8 Directory Structure and Table Storage

This article explains the main directory layout of MySQL 8, the locations of data files, configuration files, system databases, and how InnoDB and MyISAM storage engines represent tables and indexes on the file system, including the use of system and file‑per‑table tablespaces.

ConfigurationDatabase DirectoryInnoDB
0 likes · 13 min read
Understanding MySQL 8 Directory Structure and Table Storage
Zhuanzhuan Tech
Zhuanzhuan Tech
Aug 29, 2025 · Databases

Why MySQL Returns Old Values After Update: Inside InnoDB Snapshot Reads

This article investigates a puzzling MySQL behavior where a row updated within a transaction sometimes returns its previous value, explains the role of InnoDB's snapshot reads and ReadView, reproduces the issue, and offers practical ways to prevent the “update disappearance” scenario.

InnoDBMySQLSnapshot Read
0 likes · 18 min read
Why MySQL Returns Old Values After Update: Inside InnoDB Snapshot Reads
Sanyou's Java Diary
Sanyou's Java Diary
Aug 25, 2025 · Databases

Understanding MySQL Next-Key Locks: Preventing Phantom Reads and Deadlocks

This article explains MySQL InnoDB's Next-Key Lock mechanism, how it combines row and gap locks to prevent phantom reads under the REPEATABLE READ isolation level, illustrates lock ranges with examples, discusses when it degrades to row or gap locks, and highlights its advantages and potential deadlock risks.

DeadlockInnoDBMySQL
0 likes · 8 min read
Understanding MySQL Next-Key Locks: Preventing Phantom Reads and Deadlocks
Raymond Ops
Raymond Ops
Aug 20, 2025 · Databases

Master MySQL Storage Engines: InnoDB vs MyISAM and Transaction Essentials

This comprehensive guide explains MySQL storage engine concepts, compares built‑in engines like InnoDB and MyISAM, details how to view and configure engines, and covers transaction fundamentals, isolation levels, locking mechanisms, and practical migration case studies for reliable database operations.

ACIDInnoDBStorage Engine
0 likes · 17 min read
Master MySQL Storage Engines: InnoDB vs MyISAM and Transaction Essentials
Sanyou's Java Diary
Sanyou's Java Diary
Aug 14, 2025 · Databases

Deep Dive into MySQL InnoDB Tablespaces, Undo Logs, and Doublewrite Buffer

This article explores MySQL InnoDB's memory and on‑disk architecture, detailing tablespace types, table and row formats, primary key strategies, index structures, the doublewrite buffer, redo log, undo log, and temporary tablespaces, complete with diagrams and code examples for practical understanding.

Doublewrite BufferInnoDBMySQL
0 likes · 25 min read
Deep Dive into MySQL InnoDB Tablespaces, Undo Logs, and Doublewrite Buffer
Lobster Programming
Lobster Programming
Aug 6, 2025 · Databases

Understanding MySQL Row Overflow and InnoDB Row Formats

This article explains why MySQL row overflow occurs, compares the four InnoDB row formats, shows how overflow data is stored, discusses performance and space impacts, and offers practical tips to prevent overflow by optimizing schema and page size settings.

InnoDBMySQLRow Overflow
0 likes · 4 min read
Understanding MySQL Row Overflow and InnoDB Row Formats
ITPUB
ITPUB
Aug 5, 2025 · Databases

Recovering Accidentally Deleted MySQL Metadata on CDH Without Downtime

This guide details how to restore a CDH cluster after MySQL metadata loss caused by an accidental deletion of the MySQL data directory, covering database locking, locating deleted file handles, reconstructing shared and independent tablespaces, and verifying consistency to bring the platform back online quickly.

CDHData RecoveryDatabase Administration
0 likes · 6 min read
Recovering Accidentally Deleted MySQL Metadata on CDH Without Downtime
Lobster Programming
Lobster Programming
Jul 28, 2025 · Databases

How MySQL’s Double Write Buffer Prevents Partial Write Failures

This article explains why MySQL data pages can suffer partial write failures during crashes, how the mismatch between InnoDB and OS page sizes contributes to the problem, and how the Double Write Buffer mechanism safeguards data integrity by providing a recoverable copy of each page.

Database RecoveryDouble Write BufferInnoDB
0 likes · 7 min read
How MySQL’s Double Write Buffer Prevents Partial Write Failures
dbaplus Community
dbaplus Community
Jul 23, 2025 · Databases

What’s New in MySQL 9.4.0? Key Features, Fixes, and Improvements

MySQL 9.4.0 introduces extensive updates including charset handling fixes, macOS compilation options, component behavior changes, new system variables, deprecations, InnoDB memory and indexing enhancements, revised installation procedures, JavaScript stored‑procedure improvements, performance‑mode tweaks, vector function fixes, numerous new features, and a long list of resolved bugs across the server, client, and replication subsystems.

Bug FixesDatabaseInnoDB
0 likes · 26 min read
What’s New in MySQL 9.4.0? Key Features, Fixes, and Improvements
dbaplus Community
dbaplus Community
Jul 20, 2025 · Databases

Recovering Deleted MySQL Data Files in CDH: A Step‑by‑Step Guide

This guide walks through diagnosing a MySQL metadata loss in a CDH cluster, locking the database, locating deleted file handles, restoring shared and independent tablespace files, fixing table structures, verifying consistency, and explains the underlying Linux and MySQL mechanisms.

CDHData RecoveryDatabase Administration
0 likes · 6 min read
Recovering Deleted MySQL Data Files in CDH: A Step‑by‑Step Guide
MaGe Linux Operations
MaGe Linux Operations
Jul 19, 2025 · Databases

Master MySQL 8.0 Performance: 20+ Proven Optimization Tips

This guide presents more than twenty practical MySQL 8.0 optimization recommendations—including hardware tuning, InnoDB configuration, index design, query rewriting, security hardening, monitoring, backup strategies, and benchmarking—to help engineers dramatically improve database throughput, latency, and stability.

InnoDBMySQLSQL
0 likes · 15 min read
Master MySQL 8.0 Performance: 20+ Proven Optimization Tips
Liangxu Linux
Liangxu Linux
Jul 15, 2025 · Databases

InnoDB vs MyISAM vs Memory: Which MySQL Storage Engine Fits Your Needs?

This article compares MySQL's three common storage engines—InnoDB, MyISAM, and Memory—by examining their core features, locking mechanisms, transaction support, durability, foreign‑key capabilities, typical use cases, and provides concrete CREATE TABLE examples and a side‑by‑side feature matrix to help developers choose the right engine.

Database PerformanceInnoDBMemory
0 likes · 7 min read
InnoDB vs MyISAM vs Memory: Which MySQL Storage Engine Fits Your Needs?
dbaplus Community
dbaplus Community
Jul 14, 2025 · Databases

Why Full Table Scans Won’t Exhaust MySQL Server Memory

Even when scanning a 200 GB InnoDB table on a server with only 100 GB of RAM, MySQL does not consume all memory because it streams results using a limited net_buffer, employs socket send buffers, and InnoDB’s optimized LRU algorithm manages the buffer pool to prevent memory blow‑up.

InnoDBLRUMemory Management
0 likes · 12 min read
Why Full Table Scans Won’t Exhaust MySQL Server Memory
macrozheng
macrozheng
Jul 4, 2025 · Databases

How Many Rows Can a MySQL Table Really Hold? A Deep Dive into B+ Tree Limits

This article consolidates theory and practical calculations to reveal how MySQL's B+‑tree storage, page structure, and row size determine the realistic maximum number of records a single table can store, ranging from millions to billions depending on schema choices.

B+TreeDatabase LimitsIndex Calculation
0 likes · 11 min read
How Many Rows Can a MySQL Table Really Hold? A Deep Dive into B+ Tree Limits
Architect's Must-Have
Architect's Must-Have
Jul 2, 2025 · Databases

Why MySQL Indexes Matter: From Basics to B+Tree Mastery

This article explains what MySQL indexes are, how they work, their advantages and drawbacks, the different index types, the inner workings of B+Tree structures, and the differences between clustered and non‑clustered indexes, providing practical insights for database optimization.

B+TreeClustered IndexIndex
0 likes · 10 min read
Why MySQL Indexes Matter: From Basics to B+Tree Mastery