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.
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.
This article explains why MySQL deadlocks occur, shows how to enable and read deadlock logs, walks through three real‑world case studies, and provides practical SQL, configuration, and architectural solutions to resolve and prevent deadlocks in production environments.
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.
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.
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.
This guide explains the principles behind InnoDB indexes, why secondary‑index queries may trigger back‑table lookups, and how covering indexes can eliminate the extra I/O, providing concrete SQL examples, EXPLAIN analysis, and best‑practice tips for interview preparation.
MySQL relies on B+Tree indexes for faster I/O, easy range queries, and stable performance, and InnoDB stores the primary key as a clustered B+Tree where ordered keys prevent page splits that would otherwise degrade speed.
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.
This article explains the most impactful MySQL InnoDB variables, shows how to monitor their key metrics, provides concrete adjustment guidelines, and highlights when each tweak improves performance or can unintentionally degrade it.
When interviewers ask about MySQL InnoDB vs MyISAM, go beyond transaction support and discuss six deeper differences—including count(*) performance, MVCC, storage layout, caching, crash recovery, and ideal use cases—to showcase comprehensive technical knowledge.
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.
MySQL’s ORDER BY DESC queries run slower than ASC because InnoDB stores records in a forward‑oriented singly linked list, using an infimum‑supremum structure, a page directory, and N_OWNED fields, which make backward scans O(n) while forward scans remain O(1).
MySQL 9.6.0 introduces a modular audit log system, GTID replication overhaul, InnoDB enhancements, hardened security components, richer JSON view controls, logging upgrades, Shell extensions, and numerous bug fixes, all aimed at improving performance, flexibility, and enterprise‑grade reliability.
This guide shows how to set up MySQL InnoDB ReplicaSet, start MySQL Router 8.2, configure read/write split routing, and verify that reads are directed to replicas while writes go to the primary instance, all without modifying application code.
This article explains MySQL transaction fundamentals, illustrates a banking transfer scenario, details the ACID properties, explores dirty reads, non‑repeatable reads and phantom reads, and compares the four SQL‑92 isolation levels with practical code examples and InnoDB behavior.
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.
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.
An in‑depth comparison of B+Tree and B‑Tree reveals how their differing node designs affect storage capacity, tree height, disk I/O, range queries, caching, and insert/delete costs, with concrete MySQL InnoDB examples illustrating why B+Tree is the preferred index structure.
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.
This article explains the structural differences between MyISAM and InnoDB storage engines in MySQL, focusing on how each implements primary and secondary indexes using B+ trees, with code examples and diagrams to illustrate clustering versus non‑clustering index designs.
The article shares a seasoned DBA’s firsthand comparison of MySQL and PostgreSQL, covering their histories, performance quirks, real‑world case studies, lock mechanisms, backup strategies, and practical selection guidelines to help developers decide which database best fits their workload.
This article explains the internal mechanics of MySQL 8.0 InnoDB pages, how random primary keys such as UUID cause costly page splits and merges, and provides Java‑level best practices and MySQL commands to optimize primary‑key design for better performance and space utilization.
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.
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.
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.
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.
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.
The article examines whether a nullable MySQL column should store NULL or a business default, detailing InnoDB row formats, storage overhead, record headers, hidden columns, and the trade‑offs of NOT NULL versus NULL definitions.
This article explains how MySQL stores rows, compares the four InnoDB row formats, and evaluates the trade‑offs of using NULL versus explicit default values for nullable columns, covering storage overhead, indexing behavior, and best‑practice design recommendations.
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.
This article examines how MySQL stores rows in InnoDB, compares the four row formats, shows how NULL and default values affect storage, hidden columns, and record headers, and discusses the trade‑offs of defining columns as NOT NULL or allowing NULL values.
This article explains how MySQL stores rows using different InnoDB row formats, illustrates the internal layout of variable‑length columns and hidden fields, and compares the trade‑offs of defining columns as NOT NULL versus allowing NULL values, offering practical guidance for database design.
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.
When MySQL executes an UPDATE or DELETE that uses a column without an index, it must scan the entire table and acquires a combination of row, gap, and intention locks, which effectively blocks all other write operations and behaves like a table‑level lock, so adding proper indexes is essential.
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.
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.
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.
This article explains why MySQL uses B+‑tree indexes—highlighting disk‑IO efficiency, dense storage, superior range queries, and stable performance—while comparing B+‑trees to other structures, detailing their implementation in InnoDB, and providing interview‑style Q&A and optimization tips.
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.
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.
This article delves into the hierarchical logical architecture of MySQL’s InnoDB storage engine, detailing how table spaces, segments, extents, pages, and rows are organized, their functions, configuration options, and how they together enable efficient data storage, access, and transaction management.
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.
This article examines how MySQL's lock mechanisms and transaction isolation levels can cause deadlocks during high‑concurrency inserts, presents a real‑world case study, walks through log analysis and testing, and offers practical strategies to diagnose, prevent, and resolve such deadlocks.
After a normal DDL change on OneDBA, the table size grew by nearly 100 % and some queries became slow because InnoDB’s B+Tree page‑splitting logic mishandles rows larger than 8 KB, causing each page to hold only one record and corrupting statistics.
This article explains MySQL's InnoDB storage architecture, how data and indexes are organized in 16 KB pages, calculates the capacity of B+ tree nodes, and determines that a table with 20 million rows requires a three‑level B+ tree under typical assumptions.
This article explains the complete lifecycle of a MySQL UPDATE—from the initial log buffer, through redo and undo logs, buffer‑pool dirty pages, checkpoint thresholds, binary logging, and the innodb_flush_log_at_trx_commit setting—highlighting how InnoDB guarantees durability, consistency, and crash recovery.
Index Condition Pushdown (ICP) in MySQL pushes filter conditions to the storage engine, dramatically cutting data transferred to the server layer, speeding up queries especially when using composite indexes, and requiring no changes to the original SQL statements.
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.
This article explains the concept of MySQL pessimistic locking, its implementation via row‑level locks in InnoDB, and provides step‑by‑step SQL examples showing how SELECT … FOR UPDATE secures data against concurrent modifications.
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.
After a normal DDL change on the OneDBA platform, a table's storage grew nearly 100% and some queries slowed down, revealing a MySQL InnoDB B+Tree page‑split flaw that stores only one row per 16KB page when large records are inserted, which also corrupts statistics and leads to slow SQL.
Understanding MySQL's clustered indexes—including their definition, selection rules, customization via primary key design, practical creation examples, and key design considerations—helps developers optimize query performance and storage efficiency in InnoDB tables for various workloads.
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.
This article explains how InnoDB's redo log works as a safety cushion, detailing its generation, buffering, flushing, circular storage, and recovery process to ensure transaction durability even when the database crashes unexpectedly.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
This article provides a deep dive into MySQL’s architecture, covering the InnoDB storage engine’s layered structure, index design and usage, query execution plans, deep pagination challenges, slow‑query diagnostics, transaction processing, MVCC isolation, and the roles of undo, redo and binary logs.
This article explains MySQL’s index structures, SQL execution plan analysis, query execution flow, transaction isolation levels, MVCC mechanisms, and InnoDB buffer‑pool caching, providing detailed diagrams and practical examples to help developers optimize database performance.
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.
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.
This article explains why MySQL 5.7 community edition may block online DDL operations when leftover temporary table files remain, analyzes the root cause, and provides step‑by‑step commands and alternative solutions to safely remove the orphaned tables.
This article explains MySQL InnoDB's dual memory and on‑disk architecture, detailing the various tablespace types (system, file‑per‑table, general, undo, temporary), their internal structures (segments, extents, pages), configuration tips, and how tables, row formats, primary keys, auto‑increment lock modes, and B+Tree indexes are organized and managed.
This article explains MySQL's four transaction isolation levels—READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE—through step‑by‑step examples, demonstrating their effects on dirty reads, non‑repeatable reads, phantom reads, and performance considerations, helping developers choose the appropriate level.
This article explores the inner workings of InnoDB's architecture—including the Buffer Pool, Change Buffer, Adaptive Hash Index, and Log Buffer—detailing their designs, configuration parameters, performance impacts, and best‑practice recommendations for high‑throughput MySQL deployments.
This article explains MySQL’s locking mechanisms, categorizing locks by performance, operation type, data granularity, and finer‑grained levels such as gap and next‑key locks, and offers guidance on selecting the appropriate lock strategy for reliable concurrent database operations.
This article explains how MySQL InnoDB transforms a simple INSERT statement into a complex on‑disk record by detailing the COMPACT row format, record header, variable‑length field list, NULL bitmap, and overflow handling, helping developers optimize storage and performance.
This article explains MySQL InnoDB locking mechanisms—including row, record, gap, next‑key, and intention locks—illustrates each type with SQL statements and performance_schema data, and shows how they interact during concurrent transactions.
This article explains MySQL's index limits for InnoDB and MyISAM engines, detailing the maximum number of indexes per table, the maximum columns per index, and provides practical recommendations on how many indexes a table should have to balance performance and maintenance.
This guide walks you through locating MySQL deadlock logs, analyzing their contents to pinpoint transaction timing, order, and lock details, and identifying the root cause of the deadlock, including special locking scenarios that can trigger deadlocks.
This guide walks you through locating the deadlock log, analyzing its contents to determine the time, order, and involved SQL statements, and pinpointing the root cause of MySQL InnoDB deadlocks, including special locking scenarios and how to interpret lock structures.
A full‑table scan of a 200 GB InnoDB table on a MySQL server with 100 GB RAM does not exhaust server memory because MySQL streams rows to the client, uses a fixed net_buffer, and InnoDB’s optimized LRU algorithm limits buffer‑pool pressure, ensuring stable performance.
This article explains how to find the MySQL deadlock log, parse its contents to determine the time, order, and affected rows, identify the lock types and root cause, and provides extended examples of special locking scenarios, all illustrated with real‑world SQL and code snippets.
This article explains how to reproduce a MySQL InnoDB lock‑wait scenario, use system tables such as information_schema.processlist, innodb_trx, sys.innodb_lock_waits and performance_schema to locate the blocking transaction, and finally resolve the issue by killing the offending session, while also providing quick‑check queries and parameter tuning advice.
This article explains how MySQL's count() works across storage engines, why InnoDB scans rows and can time out on massive tables, and presents practical alternatives such as using EXPLAIN rows, a dedicated count table, batch processing, or binlog‑to‑Hive for efficient row‑count estimation.
This article explains InnoDB clustered and secondary indexes, their physical B‑tree storage, page‑size and fill‑factor settings, the three‑phase sorted index build process, and the design, tables, cache, document‑ID handling, transaction semantics, and monitoring of InnoDB full‑text indexes.
This article explains how MySQL redo logs and binlog logs work together to ensure data durability, consistency, and high availability, covering InnoDB crash‑recovery, flush policies, log file groups, binlog formats, two‑phase commit, and the complete recovery workflow.
This article walks through the investigation of MySQL bug #115352, describing how an assertion failure in InnoDB’s dictionary cache caused a server crash during a killed ALTER TABLE on a partitioned table, the debugging steps taken, and the eventual fix applied in MySQL 8.0.40.
This article experimentally compares MySQL auto_increment, UUID, and random Snowflake keys by measuring insert and query speeds, analyzing InnoDB index behavior, and discussing the trade‑offs of each primary‑key strategy, ultimately showing why auto_increment generally outperforms UUID in large‑scale workloads.
This article explains what MySQL indexes are, how they work, their advantages and drawbacks, the different index types—including primary, ordinary, composite, full‑text, clustered and non‑clustered—and compares B‑Tree with B+Tree structures to help you design faster, more efficient queries.
This article provides a comprehensive index of a MySQL internals series covering the transaction, lock, and undo modules, detailing each sub‑topic such as transaction initialization, two‑phase commit, various lock types, deadlock handling, and undo log management across numerous linked articles.
The article explains how InnoDB handles transaction commit by modifying Insert and Update Undo segment states, generating a transaction commit number, adding rollback segments to the purge queue, and finally cleaning or caching Undo segments based on their usage and state.
This guide explains MySQL 8's main directory layout, the locations of command binaries and configuration files, how databases and system schemas are represented on the file system, and the differences between InnoDB and MyISAM storage engines, including tablespace and file naming conventions.
This article explains how to read and parse the Undo log generated by a DELETE operation in MySQL 8.0.32 InnoDB, covering preparation, log extraction, detailed field parsing, locating the primary index record, and the step‑by‑step rollback of secondary and primary index records.
This article explains how MySQL 8.0 InnoDB generates and stores Delete Undo logs, detailing the log structure, field meanings, type_flag and info_bits decoding, address composition via DB_ROLL_PTR, and provides concrete SQL examples and shell calculations to illustrate the process.
This article explains MySQL's storage engine architecture, compares built‑in engines such as InnoDB and MyISAM, details their file system interactions, table‑space types, and transaction features—including ACID properties, isolation levels, locking mechanisms, and practical migration steps for production environments.
This article experimentally compares the performance of various MySQL COUNT queries on an InnoDB table with one million rows, explains the underlying execution plans using EXPLAIN, analyzes primary‑key versus secondary‑index behavior, and contrasts InnoDB’s fast count implementation.
