This article explains how the height of MySQL InnoDB B+‑tree indexes determines the number of page I/O operations for different query types, provides theoretical calculations of index height, and shows practical methods using information_schema and hexdump to inspect the actual index height of tables.
This article explains how MySQL InnoDB stores data in 16 KB pages, analyzes the practical limits of single‑table row counts, demonstrates data‑generation experiments, and derives the commonly cited 20 million‑row recommendation by examining B+‑tree structures, index pages, and storage parameters.
Explore the inner workings of MySQL InnoDB indexes, from row formats like Compact, Redundant, and Dynamic to page structures, B+Tree organization, and practical indexing strategies, complete with diagrams and tips for efficient query optimization and index design.
The article explains MySQL InnoDB's logical storage architecture, including tablespaces, pages, segments, and B+‑tree indexes, demonstrates how primary and secondary indexes are organized and accessed, and shows how to calculate index tree height and data capacity using SQL queries and hexdump analysis.
This article provides a comprehensive overview of MySQL indexes, explaining their underlying data structures, classification methods, practical usage scenarios, and optimization strategies, while illustrating concepts with diagrams and SQL examples to help developers design efficient query performance.
This article explains MySQL index fundamentals, covering B+Tree structures, various index types, creation syntax, and practical optimization techniques such as prefix, covering, and composite indexes, while illustrating how to avoid common pitfalls like index misuse and performance‑degrading scenarios.
This article walks through MySQL's InnoDB storage engine, explaining how pages, locality, skip‑list concepts and B+‑tree structures work together to make indexes efficient, and shows how clustered, non‑clustered and covering indexes affect query performance and I/O behavior.
This article explains the fundamentals of B+ trees, introduces log‑structured merge (LSM) trees as a modern alternative for write‑intensive workloads, and demonstrates how HBase leverages LSM trees—including MemStore, HFile, compaction, and Bloom filters—to achieve efficient storage and retrieval in NoSQL environments.
This article explains the structures of B+ trees and skip lists, compares their insertion and search behaviors, and shows why MySQL prefers B+ trees for disk‑based indexing while Redis adopts skip lists for in‑memory operations.
The article walks through building a minimal SQLite‑style database in Go, covering memory‑disk page storage, B‑tree indexing, simple SQL parsing for INSERT and SELECT, row serialization, a pager that flushes pages, and a basic REPL, while highlighting current single‑page limits and future extensions.
This article explains the structures of B+ trees and skip lists, compares their read and write performance, and details why MySQL chooses B+ trees for indexing while Redis and RocksDB prefer skip lists, highlighting trade‑offs in disk I/O, fan‑out, and balancing overhead.
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.
This article explains how MySQL implements indexes using B+ trees in InnoDB and MyISAM, compares primary and secondary indexes, demonstrates performance gains from proper indexing on large tables, and provides practical commands for creating, viewing, and dropping indexes.
This article explains how MySQL single‑table row limits are determined by primary‑key size, InnoDB page structure, and B+Tree fan‑out, showing why a table with 100 million rows can still query quickly when the index depth remains low.
Explore MySQL's comprehensive index guide, covering basic concepts, B‑tree and B+‑tree structures, storage engine differences, index types, optimization strategies, and practical tips for designing effective primary, secondary, and composite indexes to boost query performance and reduce I/O overhead.
This article explains the purpose and principles of database indexes, compares various tree structures such as binary search trees, AVL trees, B‑Trees and B+Trees, and shows how MySQL uses B+Tree indexes to minimize disk I/O and boost query performance.
This article explains MySQL index fundamentals—including B‑tree, B+‑tree, hash, and ordered array structures—covers primary versus secondary indexes, describes index maintenance such as page splits and merges, examines the InnoDB change buffer, and discusses optimizer choices and cases where indexes become ineffective.
This article explains how database indexes work by first reviewing binary search trees, then introducing N‑ary trees and B+ trees, illustrating their structure, storage calculations, and search process in InnoDB to show why B+ trees are used for efficient indexing.
This article explains how InnoDB stores data in 16 KB pages, links pages via a double‑linked list, uses page directories and primary‑key directories for binary search, and builds B+‑tree index pages to accelerate MySQL queries, covering free space, page splits, and the overall search flow.
This article explains why MySQL’s DELETE only sets a delete‑mark, how InnoDB’s MVCC and purge thread reuse freed pages, the impact of B+‑tree storage on I/O, page merge and split mechanisms, and the proper way to rebuild tables to recover space.
This article explains how InnoDB stores data in 16 KB pages, calculates how many rows a B+Tree index can hold, shows how to determine the tree height from the page level, and answers why MySQL uses B+Tree rather than other tree structures.
This article explains MySQL index fundamentals, classifies indexes by data structure, storage engine, field characteristics and column count, compares B+Tree with B‑Tree, hash and red‑black trees, and demonstrates practical creation, usage, and optimization techniques with SQL examples and diagrams.
This article compares MySQL and HBase across architecture, storage engine, indexing structures (B+ tree vs LSM tree), data access features, and ecosystem integration, highlighting each system's strengths, limitations, and the scenarios where HBase is a suitable complement to MySQL for large‑scale data workloads.
This article walks through MySQL's core concepts—including ACID guarantees, the four isolation levels, undo and redo logs, buffer pool flushing, lock types, gap locks, deadlock scenarios, and the B+‑tree based index architecture—providing interview‑ready insights and practical examples.
This article compares MySQL and HBase by examining their architectural designs, storage engines (B‑Tree vs LSM‑Tree), performance characteristics, ecosystem features such as TTL and multi‑versioning, and identifies scenarios where HBase is a suitable complement to MySQL for large‑scale data workloads.
This article compares MySQL and HBase by examining their architectural designs, storage engines, data access patterns, and ecosystem features, highlighting the strengths and trade‑offs of each system and outlining the scenarios where HBase is a suitable complement to MySQL.
This article explains the fundamentals and structures of database indexes—including binary trees, red‑black trees, B‑Tree, B+Tree, hash indexes—and details how MySQL’s InnoDB and MyISAM engines implement clustered and non‑clustered indexes, covering their characteristics, storage files, and query behavior.
This article explains MySQL indexes, covering their definition, benefits, various types such as B‑tree and B+‑tree, practical techniques for creating high‑performance indexes like prefix, composite, and clustering indexes, and how to inspect and maintain index information and fragmentation.
This article explains the fundamentals of MySQL indexing, including the structure of InnoDB pages, the evolution from simple page reads to B+ tree indexes, and how clustered and non‑clustered indexes affect query performance and I/O efficiency.
This article compares MySQL and HBase across architecture, engine design, indexing structures, and feature sets such as TTL and multi‑versioning, highlighting how MySQL excels in low‑latency online transactions while HBase offers distributed scalability and write‑optimized storage for big‑data scenarios.
This article explains why MySQL chooses B+ trees for its indexes, covers the left‑most prefix rule for composite indexes, offers practical index‑design tips, discusses MyBatis caching, master‑slave replication, and sharding strategies for large‑scale databases.
This article explores the evolution and current landscape of time‑series databases, detailing design principles, storage structures such as B‑Tree, B+Tree, and LSM‑Tree, and providing an in‑depth comparison of OpenTSDB, InfluxDB, and the emerging Apache IoTDB, while also discussing practical deployment considerations and industry use cases.
This article explains MySQL’s logical architecture, the client‑server communication protocol, how queries are parsed, optimized and executed, the role of the query cache, and provides concrete performance‑tuning advice on schema design, indexing, B+Tree mechanics, and common pitfalls.
The article explains how PolarDB's Polar Index redesigns InnoDB B‑Tree structural modification (SMO) by splitting the operation into two phases, eliminating the global index latch, reducing lock granularity, and achieving up to 11× performance gains in high‑concurrency workloads such as TPCC.
This article explains MySQL's B-Tree index structure, storage layout, query patterns that benefit from it, common limitations such as left-most prefix rules, and answers why B-Tree is preferred over other structures and why auto-increment primary keys are recommended.
This article explains MySQL's B‑Tree index fundamentals, compares MyISAM and InnoDB implementations, outlines how B+Tree structures work, and provides practical guidelines for designing effective indexes and tuning MySQL configuration to boost query performance.
This article explains why large data sets must be stored on disk, compares disk and memory access characteristics, introduces the B+ tree as an efficient disk‑based index, and details its structure, search process, and dynamic adjustments for insertions and deletions.
This article explains how MySQL maintains primary and composite (non‑primary) indexes using B+ trees, outlines the essential equality‑matching, left‑most‑prefix, and range‑scan principles, and shows how to combine them with ORDER BY, LIMIT, and GROUP BY for optimal query performance.
This article explains how many rows a single InnoDB B+‑tree can hold by describing InnoDB’s 16 KB page size, the storage of data and index pages, calculating rows per leaf page, pointer capacity, tree height for typical workloads, and how to determine the height from the page‑level metadata.
This article explains MySQL's logical architecture, query execution flow, client‑server protocol, query cache behavior, parsing and optimization stages, cost‑based optimizer, execution engine, and provides practical performance‑tuning advice such as schema design, data‑type choices, index creation, B‑Tree fundamentals, covering indexes, and handling COUNT, JOIN, LIMIT, and UNION queries.
The article explains how MySQL indexes rely on ordered data structures—binary trees, red‑black trees, hash tables, and especially B‑Trees—to accelerate queries, compares their performance characteristics, illustrates how tree height affects I/O operations, and shows why B‑Tree is preferred for range searches.
This article explains the storage units of InnoDB, calculates how many rows a B+‑tree leaf page can hold, derives the total record capacity for trees of different heights, and shows how to determine the actual B+‑tree height in a MySQL table using page metadata.
This article explains the core principles of MySQL indexes, covering their essence, various types such as hash, binary, B‑tree and B+‑tree, the structure of primary key directories and index pages, clustered versus non‑clustered indexes, and the back‑table lookup process.
This article explains why MySQL uses B+‑tree indexes, describes the left‑most prefix rule for composite indexes, offers practical index‑design and optimization tips, outlines MyBatis first‑ and second‑level caching, details master‑slave replication, and introduces common sharding strategies and their implementation considerations.
This article explains how InnoDB’s 16 KB pages form B+‑tree indexes, calculates the number of rows a tree can hold at different heights, shows how to determine the tree’s height from the tablespace file, and why MySQL prefers B+ trees for indexing.
This article demystifies MySQL’s query execution by exploring its logical architecture, client‑server protocol, query cache, parsing, optimization, and execution engine, then offers practical indexing and performance‑tuning techniques—including B‑Tree fundamentals, covering indexes, and pagination tricks—to help developers write faster, more efficient SQL.
This article examines why MySQL pagination with large OFFSET values becomes painfully slow, explains the underlying B+‑tree index mechanics, and presents two practical solutions—keyset pagination and index‑covering subqueries—to dramatically improve query performance.
This article explains the storage units of InnoDB, calculates how many rows a B+‑tree can hold based on page size, record size and pointer size, demonstrates how to determine the tree height from the tablespace file, and summarizes the impact on query I/O.
This article explores why MySQL queries with large LIMIT‑OFFSET pagination become painfully slow, explains the underlying B+‑tree index mechanics and MySQL's logical operator layers, and presents two practical solutions—key‑based pagination and index‑covering queries—to dramatically improve performance.
This article explains the fundamentals of MySQL indexes, the B+Tree data structure, differences between MyISAM and InnoDB implementations, practical tips for creating effective composite indexes, and essential server configuration and SQL tuning techniques to dramatically improve query performance.
This article explains the fundamentals and drawbacks of B+ trees, introduces the Log-Structured Merge Tree (LSM Tree) used in HBase and other NoSQL databases, and discusses how LSM architecture, Bloom filters, and MemStore improve write performance while affecting read efficiency.
This article explains how database indexes improve query performance by exploring binary trees, binary search, balanced trees, B‑trees, and B+‑trees, illustrating their structures, advantages, disadvantages, and the impact of disk I/O on overall efficiency.
This article explains the fundamentals of database indexing, detailing B‑Tree structures, the differences between clustered and non‑clustered indexes, their storage formats, and how they affect query, insert, and delete operations, including the concept of index covering.
This article explains why MySQL uses B+‑tree indexes, describes the principles of B‑tree and B+‑tree structures, compares MyISAM and InnoDB index implementations, and provides practical optimization tips such as using auto‑increment primary keys, the left‑most prefix rule, and proper configuration settings.
This article explains how proper index creation boosts MySQL query performance, detailing the mechanics of indexes, why B+Tree is chosen over binary and balanced trees, the storage differences between MyISAM and InnoDB, and practical guidelines for designing effective single‑column, composite, and covering indexes.
This article explains the fundamental concepts and practical design of MySQL indexes, covering implementation-level index types, application-level index categories, the differences between clustered and non‑clustered indexes, B‑Tree and hash structures, covering indexes, and best‑practice strategies for optimal query performance.
The article explains how B+ trees serve as efficient disk-based indexes, contrasting disk and memory I/O, describing node and leaf structures, retrieval methods, and dynamic insertion and deletion operations, highlighting their advantages for large-scale data storage and query performance.
This article explains how MySQL stores indexes for the MyISAM and InnoDB engines, comparing clustered (primary) indexes and secondary indexes, illustrating their B+‑tree structures, showing SQL examples, and discussing the advantages and disadvantages of each storage method for database optimization.
This article explains how MySQL indexes work, comparing binary search trees, red‑black trees, hash tables, B‑trees, B+‑trees, and the differences between MyISAM and InnoDB implementations, and it details primary, secondary, covering, and composite index usage with practical examples.
This article explains the fundamentals of index structures—including binary trees, balanced AVL trees, B trees, and B+ trees—detailing their characteristics, search complexities, and how they are applied in MySQL indexing to reduce disk I/O and improve query performance.
This article explains why MySQL's default InnoDB storage engine uses B+ trees instead of B trees or hash indexes, covering storage engine basics, query performance, I/O considerations, and the structural advantages that make B+ trees ideal for OLTP workloads.
This article explains how MySQL indexes work, compares binary trees, B‑Tree and B+Tree structures, describes their implementation in MyISAM and InnoDB storage engines, and provides practical guidelines for creating efficient single‑column, composite, and covering indexes to improve query performance.
This article explains how InnoDB’s page size, row size, and B+‑tree structure determine that a single B+ tree can hold roughly twenty‑million rows, shows how to calculate tree height from the root page, and demonstrates the concepts with SQL queries and practical examples.
This article compares MySQL's B+ tree indexing with Elasticsearch's inverted index, explaining underlying data structures such as hash tables, ordered arrays, balanced binary trees, skip lists, and term dictionaries, and discusses optimization techniques like bitmap intersections for efficient query processing.
This article explains how B+ trees serve as efficient disk-based indexes by aligning node size with block size, separating data from indexes, and enabling fast range queries, while also covering their structure, search process, and dynamic adjustments for large-scale database systems.
This article explains what database indexes are, outlines their advantages and disadvantages, advises when to create or avoid them, compares common index data structures such as B+Tree, Hash, and FullText, and details MySQL's MyISAM and InnoDB index implementations.
This article explains MySQL's B‑Tree index structure, describes various index types such as primary, unique, normal, full‑text, and composite indexes, and provides practical techniques for creating efficient indexes, choosing column order, using prefix and covering indexes, and optimizing query sorting.
This article explains MySQL index structures—including B‑Tree, B+Tree, and hash indexes—their underlying principles, performance characteristics, and practical usage guidelines such as left‑most prefix, full‑text indexing, and common pitfalls, providing a comprehensive guide for database developers.
This article explains InnoDB's 16KB page structure, row formats, record metadata, primary key strategies, and how B+Tree indexes—including clustered, secondary, and covering indexes—organize and retrieve data, with practical SQL examples and performance considerations.
This article reviews B+ trees, introduces log‑structured merge (LSM) trees, compares their strengths and weaknesses, and explains how HBase leverages LSM trees, HFiles, compaction, and Bloom filters to achieve high‑performance storage for write‑intensive workloads.
This article walks through the evolution of MySQL index implementations—from hash tables and binary search trees to AVL, red‑black, B‑trees and finally B+ trees—explaining their performance trade‑offs, disk‑I/O considerations, and how InnoDB and MyISAM store data and indexes differently.
This article explains how MySQL stores data using pages, page directories, and multi‑page structures, demonstrates why MySQL silently sorts rows on insert, and shows how these mechanisms combine into the B+‑tree index that optimizes query performance and supports clustering, non‑clustering, and composite indexes.
This article explains the fundamentals of MySQL indexes, covering their conceptual purpose, logical classifications, underlying data structures such as hash, B‑Tree, and B+Tree, physical storage differences between clustered and non‑clustered indexes, and practical tips for effective index optimization.
This article explains the storage units of InnoDB, how B+‑tree indexes are organized on pages, calculates the maximum number of rows a B+ tree can hold based on page size and pointer count, and shows how to determine the tree height from the tablespace file.
This article presents a detailed fio benchmark comparing sequential and random I/O on a 7‑disk RAID5 array, revealing how I/O size, RAID strip size, and caching affect bandwidth, latency, and IOPS, and explains the implications for database transactions, file copying, and B+‑tree indexing.
This article explains the internal B+Tree layout of InnoDB indexes, detailing page types, record organization, and how different primary‑key data types affect page density and overall index tree height, illustrated with concrete examples and visual diagrams.
This article explains how InnoDB organizes data with B+ trees, calculates the maximum number of rows a single tree can hold (around 20 million), and shows how page size, pointer count, and tree height affect MySQL index performance.
This article explains why B+ trees are the dominant data structure for MySQL indexes, compares them with hash tables, linked lists, and skip lists, and details page splits, merges, and how index values map to row records, helping readers master high‑frequency interview questions.
This article explains MySQL storage engines, compares MyISAM and InnoDB, introduces the main index types—B‑Tree, Hash, Full‑text, R‑Tree—details the structures and characteristics of B‑Tree, B‑Tree and B+Tree, and outlines key principles for creating effective indexes.
This article presents an in‑depth technical overview of ByteGraph, ByteDance’s self‑built distributed graph database and its accompanying graph‑computing engine, covering graph data characteristics, the directed‑property graph model, API design, three‑tier system architecture, storage strategies using KV stores and B‑Trees, hotspot handling, indexing, and future research directions.
This article explains MySQL index fundamentals, compares B‑Tree and B+Tree structures, details how MyISAM and InnoDB store indexes, and provides practical interview questions and usage rules such as left‑most prefix matching, composite indexes, and common pitfalls.
This article explains how MySQL InnoDB implements indexes with B+ trees, describes primary and secondary (clustered and non‑clustered) indexes, the concepts of row lookup, covering indexes, composite indexes, the left‑most prefix rule, index push‑down, and provides practical guidelines for creating and maintaining efficient indexes.
This article explains the fundamentals of MySQL indexing, tracing the evolution from binary search trees and balanced AVL trees to B‑trees and B+‑trees, and details how InnoDB implements clustered and non‑clustered indexes, including search processes and performance considerations.
This article explains why MySQL uses B+‑tree indexes by tracing the evolution from binary search trees through balanced trees and B‑trees, detailing the structure of clustered and non‑clustered indexes in InnoDB and illustrating how data is searched using these indexes.
Understanding MySQL indexes—from hash tables, ordered arrays, and B+ trees—to their practical use cases, including clustered and secondary indexes, reveals why they boost query speed, when they may hinder performance, and how factors like selectivity, dirty page flushing, type mismatches, and function usage affect query efficiency.
This article explains how InnoDB stores data using B‑Tree primary keys, why explicit primary keys are essential, and compares ordered versus random insert patterns, illustrating their impact on page fill factor, index size, and query performance with practical sysbench examples.
This article explains the storage capacity of InnoDB B+‑tree indexes by detailing page size, record size, pointer calculations, tree height effects, and real‑world verification using page‑level metadata, showing that a typical B+‑tree can hold tens of millions of rows with only 1‑3 I/O operations.
This article explains how InnoDB’s 16 KB pages, row size assumptions, and B+‑tree node capacities combine to allow roughly 20 million rows per tree, demonstrates how to calculate tree height from page metadata, and shows why MySQL chooses B+‑trees for primary‑key indexes.
Since MySQL 5.7 InnoDB builds secondary indexes using a bottom‑up, sorted‑index approach, the article explains the three build phases, presents a step‑by‑step B‑tree construction example with SQL code, and discusses the innodb_fill_factor setting, its impact, advantages, and drawbacks.
This article provides a comprehensive overview of InnoDB's file structure, covering physical organization from pages to tablespaces, logical structure including segments and indexes, and file management processes.
This article recounts a detailed MySQL interview, explaining index fundamentals, B‑Tree versus hash structures, clustered and covering indexes, composite indexes with left‑most prefix rules, and advanced optimizations like index condition pushdown, offering practical insights for database professionals.
This article explains how library indexing inspires database indexing, introduces binary search trees, AVL trees, B‑Tree and B+Tree structures, and details InnoDB and MyISAM storage mechanisms, page organization, clustered versus non‑clustered indexes, and practical index‑optimization advice.
This article explains the principles, structures, and operations of MySQL indexes, covering B+ tree indexes, their search, insertion, and deletion mechanisms, as well as hash indexes, adaptive hash indexing, and full‑text indexes with inverted indexing, cache handling, and practical limitations.
This article explains how InnoDB’s B+‑tree index stores data, calculates the maximum number of rows a single tree can hold (around 20 million), shows how to determine the tree height from the tablespace file, and answers why MySQL uses B+ trees instead of other structures.
This article explains how MySQL storage engines differ, clarifies the structures and search mechanisms of B‑Tree and B+Tree indexes, and outlines practical principles for creating efficient indexes to reduce I/O and speed up query execution.
This article examines MySQL indexing by exploring underlying data structures such as B‑Tree and B+Tree, common search algorithms, storage engine differences, and practical optimization techniques like composite and prefix indexes, providing a comprehensive guide for designing efficient database indexes.
This article explains the main index structures used in MySQL—including hash tables, ordered arrays, search trees, and B+ trees—covers why InnoDB prefers B+ trees, the benefits of auto‑increment primary keys, and key concepts such as covering indexes, the left‑most prefix rule, and index‑pushdown for query optimization.
This article explains how disk I/O performance is shaped by page cache behavior, sequential versus random access, elevator scheduling, and storage data structures such as B+Tree and LSM trees, showing why operating systems and databases must design for sequential reads and writes to achieve optimal throughput.
This article explains MySQL’s storage engines, compares B‑Tree and B+Tree indexes, details their structures and search processes, and outlines key principles for designing efficient indexes to avoid slow queries in relational databases.
This article explains how InnoDB stores data pages, builds page directories, and uses B+‑tree indexes—including clustered, secondary, and composite indexes—to enable fast record lookup, while also covering MyISAM indexing differences and the SQL statements for creating and dropping indexes.
