This article provides a comprehensive summary of MySQL indexing strategies including B+Tree and Hash structures, composite index leftmost prefix principles, and locking mechanisms covering transaction isolation levels, row/table locks, pessimistic/optimistic locking with practical SQL examples for InnoDB and MyISAM engines.
This article explains why database indexes accelerate queries by detailing disk storage mechanics, demonstrating binary search on 100,000 records reducing block scans from 20,000 to 14, covering clustered vs non-clustered indexes, index trade-offs, common failure cases like OR conditions, and practical SQL optimization techniques.
WeChat uses SQLite for chat storage because its embedded, zero-process design fits mobile constraints; ACID transactions and B+tree indexes ensure message durability and fast pagination; WAL enables concurrent reads/writes; SQLCipher encrypts per-device; sharding by session mitigates single-writer limits, offering a blueprint for local-first apps.
This article traces the origin of the Log-Structured Merge-Tree (LSM Tree) from a 1996 paper by Patrick O'Neil et al., explaining how its sequential-write, background-compaction design solved the random I/O bottleneck of B-Trees, enabling modern write-heavy systems like Cassandra, RocksDB, and Kafka.
This article explains how MySQL uses B+ tree indexes to accelerate queries, detailing their structure, differences from binary search trees, organization around 16KB pages, and why smaller index values reduce tree height and disk I/O for better performance.
This article explains the 20-million-row rule of thumb for MySQL InnoDB tables by analyzing B+ tree index structure, 16KB page size, and fan-out calculations, showing how row size and tree depth affect query performance.
The article examines a MySQL experiment where three transactions (A, B, C) interact on the same row, showing why transaction B’s UPDATE on a primary‑key field proceeds without waiting while transaction C’s UPDATE of the primary key blocks, due to the interplay of row‑level locks, gap locks, and the internal delete‑plus‑insert transformation of indexed updates.
The article explains why ElasticSearch excels at full‑text search and analytics while MySQL remains the reliable storage engine, compares their underlying data structures, outlines real‑time write behavior, lists scenarios where ES should not be used, and describes a production double‑store architecture with asynchronous sync.
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.
This article explains MySQL indexes with a simple book‑catalog analogy, shows why missing or mis‑used indexes cause severe latency and CPU issues, outlines when to create or avoid indexes, and provides a step‑by‑step SpringBoot demo that measures query time dropping from 80‑200 ms to under 5 ms.
The article explains how InnoDB implements indexes as B+ trees, detailing their evolution from simple page directories, the differences between clustered and secondary indexes, the costs of indexing, and the specific query patterns and scenarios where such indexes are effective.
The article explains how database indexes act like a book's table of contents, reducing full‑table scans and disk I/O by using B+Tree structures, and compares clustered, non‑clustered, and covering indexes while highlighting their benefits and trade‑offs.
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.
The article breaks down MySQL index mechanics into four visual sections, explaining B+Tree structure, why it outperforms B‑tree, hash and binary trees, common index‑failure scenarios, and three practical optimization techniques, giving interviewees a clear framework to answer index‑related questions confidently.
This comprehensive guide explains MySQL index fundamentals using a dictionary analogy, covers four essential index types with SQL examples, details six common optimization pitfalls that cause index failure, and demonstrates how to verify index usage with EXPLAIN and SHOW INDEX commands.
This article systematically examines why MySQL indexes can become ineffective—covering function calls, implicit casts, LIKE patterns, OR conditions, composite‑index left‑most rules, data distribution, and ORDER/GROUP mismatches—while providing concrete code examples, production case studies, and actionable optimization techniques.
This guide explains how traditional relational databases and SQL struggle with semantic queries needed for AI applications, introduces vector databases and HNSW indexing for efficient similarity search, compares their architectures, and presents a real‑world fraud detection system that combines both technologies.
This article explains how to assess MySQL table size using table capacity, disk usage, and instance limits, reveals why large tables slow down due to B+‑tree overhead, and presents three practical solutions—partitioning, sharding, and hot‑cold archiving—to restore query performance.
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.
This guide explains MySQL’s B+‑tree index architecture, compares clustered and secondary indexes, demonstrates covering and prefix indexes, outlines design principles such as high‑selectivity columns and the left‑most prefix rule, and shows how to use EXPLAIN and real‑world examples to diagnose and improve query performance.
This article explains why multi‑way search trees such as B‑trees, B+‑trees, B*‑trees and R‑trees are crucial for reducing disk I/O in large‑scale storage systems, covering their hardware background, structural definitions, height analysis, insertion and deletion algorithms, and practical examples with code and diagrams.
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.
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 dissects a non‑typical slow SQL on a massive e‑commerce order table, explains how index structures, B+Tree height, index‑pushdown, and filesort affect performance, and presents a step‑by‑step SOP for analyzing, optimizing, and safely deploying new indexes to eliminate the slowdown.
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.
This guide walks developers through the fundamentals of database indexes, explaining why they matter, how they work, common pitfalls, and best‑practice design tips with concrete SQL examples and visual illustrations.
This guide explores the fundamentals of database indexing, answering ten common questions about index design, performance pitfalls, composite indexes, covering indexes, NULL handling, and maintenance, while providing practical SQL examples, B+‑tree concepts, and best‑practice recommendations for MySQL and PostgreSQL.
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.
This article walks through a real‑world slow‑SQL case on a massive e‑commerce order table, explains B+Tree index structures, index height estimation, index push‑down, sorting strategies, EXPLAIN and Query Profiler usage, and provides a step‑by‑step SOP for safe index changes and validation.
This article explains the fundamentals of database indexing, covering what indexes are, their advantages and drawbacks, underlying structures such as hash tables, B‑tree and B+‑tree, different index types (clustered, non‑clustered, primary, secondary) in MySQL, and practical examples with code snippets.
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 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 explains MySQL indexes, their working principle, advantages and drawbacks, different index types, the B+Tree structure, comparisons with B‑Tree, and the differences between clustered and non‑clustered (auxiliary) indexes, providing clear examples and practical insights.
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 explores how MySQL decides whether queries with IS NULL or IS NOT NULL predicates can use an index, presenting three concrete examples with varying data sizes and NULL distributions, and explains the underlying storage of NULL values and optimizer cost decisions.
PostgreSQL’s index system, officially called a B‑Tree, actually implements B+‑tree functionality, storing only TIDs in leaf nodes, using linked leaf pages for efficient range queries, and includes optimizations like deduplication, index‑only scans, and reverse‑key indexes to boost performance.
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.
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.
This article explains how MySQL stores data in 16KB pages, uses B+‑tree indexes to locate rows, derives the commonly cited 20‑million‑row limit from page fan‑out and row size calculations, compares B‑tree and B+‑tree structures, and offers practical optimization tips.
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 explores five sophisticated data structures—B‑Tree, Radix Tree, Rope, Bloom Filter, and Cuckoo Hash—explaining their design, typical use cases, and why they outperform classic structures when handling massive scale, high‑speed lookups, or complex data manipulation.
This article explores the evolution, design, and inner workings of the ext4 extent mechanism in Linux, detailing how it replaces traditional block mapping, improves storage efficiency, reduces fragmentation, and accelerates file I/O through compact data structures and B+‑tree indexing.
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 what non‑clustered indexes are, how they are built on B+‑tree structures, their main characteristics, and the scenarios where they improve query performance in relational databases, including fast lookups, join optimization, sorting, and handling frequent data modifications.
This article dissects a Pinduoduo Java backend interview, covering Redis caching strategies, MQ usage, lock mechanisms, thread‑pool tuning, database index differences, and data‑structure choices like ZSet and B+‑Tree, providing concrete examples and code snippets for each topic.
This article explains how to assess MySQL table size from table capacity, disk usage, and instance limits, describes why large tables degrade performance due to B+‑tree depth, and presents three practical solutions—partitioning, sharding, and hot‑cold archiving—to improve query speed and manage growth.
This article explains MySQL indexes, their purpose and working principle, compares primary, ordinary, composite and full‑text indexes, describes the B+Tree storage structure versus B‑Tree, and clarifies the differences between clustered and non‑clustered (auxiliary) indexes along with their advantages and drawbacks.
This article walks you through the inner workings of modern databases—explaining storage engines, query parsing, execution, and transaction models—while comparing classic B‑Tree structures with newer LSM‑Tree designs to help you decide whether SQL or NoSQL best fits your performance and consistency needs.
This article explains the purpose, design, and internal data structures of ext4 extents, describes how the B+‑tree indexes extents for efficient mapping of logical to physical blocks, and compares ext4’s extent mechanism with older file‑system addressing methods and other modern file systems.
This article explains how MySQL's indexing evolved from simple binary search trees through AVL and red‑black trees to B‑trees and finally B+ trees, highlighting the performance and I/O advantages that make B+ trees the preferred index structure for disk‑based databases.
This article explains the concept of database indexes, compares various index structures such as hash tables, ordered arrays, binary trees, B‑trees and B+‑trees, details InnoDB primary, secondary, covering and composite indexes, discusses prefix indexes and order‑by optimization, and provides practical advice and SQL examples for improving MySQL query performance.
This article walks through building a database storage engine from a simple shell script to a full distributed key‑value system, covering in‑memory indexing, SSTable creation, LSM‑Tree architecture with compaction, replication strategies, and sharding techniques for scaling across multiple machines.
This article explains why MySQL adopts B+ trees for its indexes, examining the impact of disk I/O latency, comparing B‑tree variants, and showing how B+ trees reduce I/O operations while supporting efficient range queries and balanced performance for large datasets.
This article explains how MySQL single‑table row limits are determined by primary‑key size, InnoDB page structure, and B+‑tree fan‑out, showing that a table with 100 million rows can still query quickly when the index depth remains low.
This comprehensive guide explains MySQL indexing fundamentals, B+Tree advantages, index classifications, optimal creation strategies, and how to use EXPLAIN to diagnose and fine‑tune queries for maximum performance while avoiding common pitfalls.
The article explains how using a large OFFSET with MySQL LIMIT forces the engine to scan and sort hundreds of thousands of index and data pages, then presents three concrete optimizations—covering index scans, subqueries, and partitioned tables—to dramatically reduce the work.
This article explains how to assess MySQL table size using table capacity, disk usage, and instance resources, describes why large tables degrade performance, and presents three practical solutions—partitioning, sharding (horizontal/vertical), and hot‑cold data archiving—along with their trade‑offs and implementation tips.
This article recounts how SQLite was created by Richard Hipp to solve reliability issues on a U.S. Navy destroyer, evolved from a simple file‑based engine into an open‑source, B‑Tree powered embedded database that now powers billions of devices, smartphones, browsers, and applications worldwide.
This article explains how MySQL uses B‑tree‑based indexes, covering balanced binary trees, B‑trees, B+‑trees, various index types, data‑type effects, and best practices for creating and using composite indexes to optimize query performance.
This article explains MySQL index fundamentals—including hash tables, ordered arrays, and B+‑tree structures—covers index maintenance, demonstrates how indexes improve query execution, discusses best practices such as left‑most prefix, covering indexes, index push‑down, and unique indexes, and provides practical tips for index selection, avoiding index loss, and using EXPLAIN for performance tuning.
This article breaks down the reasoning behind MySQL’s commonly cited 20 million‑row limit for a single table by analyzing page size, B+‑tree structure, storage overhead, and how leaf and index pages combine to estimate maximum row counts.
This article provides a comprehensive guide to MySQL indexing, covering index data structures, maintenance mechanisms, query execution, optimization techniques such as left‑most prefix, index push‑down, unique indexes, index selection, and common pitfalls that cause index inefficiency.
This article examines the practical limits of MySQL single‑table row counts, explains how InnoDB stores data in 16 KB pages, analyzes B+‑tree index structures, and provides formulas and experiments that justify the commonly cited 20 million‑row guideline.
This article traces the evolution of tree‑based index structures—from simple binary search trees through AVL and red‑black trees to B‑trees and B+‑trees—explaining their strengths and limitations and why MySQL adopts B+‑trees to minimize disk I/O and boost query performance.
This article explains the purpose and inner workings of various database indexing algorithms—including B‑Tree, Hash, Bitmap, and Full‑Text indexes—illustrates their strengths and weaknesses with SQL examples, and provides guidance on when to choose each type for optimal query performance.
MySQL, born in the 1990s as a free open‑source database, became a core component of the LAMP stack and remains widely used; the article explains why indexes are needed, compares binary trees, B‑trees and B+‑trees, and describes how modern cloud‑based MySQL services improve performance and reliability.
This article provides a comprehensive overview of B‑tree, B+‑tree, B*‑tree and R‑tree data structures, explaining their definitions, node layouts, height analysis, insertion and deletion algorithms with examples, and their roles in external‑memory indexing for databases and file systems.
This article explains the technical reasons behind limiting MySQL single‑table rows to around 20 million, covering primary‑key limits, InnoDB data‑page structure, B+‑tree storage math, and practical estimation methods for large‑scale databases.
This article explains how InnoDB stores data pages and builds B+‑tree indexes, illustrates the structure of clustered, secondary and composite indexes, and enumerates typical scenarios—such as left‑most prefix violations, LIKE patterns, and function use—that cause index loss and full‑table scans.
This article explains the fundamentals of B‑Tree and B+Tree data structures, their search algorithms, and how MySQL's MyISAM and InnoDB storage engines implement these indexes to achieve efficient disk‑based query performance.
This article explains how MySQL indexes work, compares clustered and non‑clustered indexes, outlines their advantages and disadvantages, and provides practical design guidelines to improve query performance and maintainability.
This article analyzes the theoretical and practical limits of a single MySQL InnoDB table by examining B+‑tree depth, page layout, row format, and index pointer size, then calculates minimum and maximum record counts for int and bigint primary keys and validates the results with real‑world table schemas.
This article explains MySQL index types, B+Tree structures, secondary indexes, and demonstrates how enabling Index Condition Pushdown (ICP) can accelerate a query on a 6‑million‑row table by roughly ninety times, providing detailed setup, commands, and performance results.
This article explains the differences between B‑trees and B+‑trees, covering row vs. column storage, index construction, binary search trees, and how B+‑trees provide faster insert, delete, and range queries in modern database systems.
This article explains how to evaluate MySQL table size, understand B+‑tree limitations, and improve query performance by using table partitioning, horizontal/vertical sharding, and hot‑cold data archiving, while also comparing the trade‑offs of each approach for practical selection.
When MySQL tables grow to millions of rows, insert and query latency increase, schema changes become costly, and irrelevant data bloats storage, so this guide evaluates table size, explains why B+‑tree depth hurts performance, and presents partitioning, sharding, and hot‑cold archiving solutions with practical SQL examples.
This article explains the technical reasons why a MySQL single table should not exceed about 20 million rows, covering limits from auto‑increment primary keys, data‑page structures, B+‑tree storage calculations, and practical partitioning recommendations for large‑scale applications.
This article explains how to assess MySQL table size, understand performance degradation caused by large B+‑tree indexes, and apply three practical solutions—table partitioning, database sharding, and cold‑data archiving—to improve query speed and maintainability.
This article explains why a single MySQL table should stay below about twenty million rows by examining primary‑key limits, InnoDB data‑page structure, B+‑tree storage calculations, and provides a practical recommendation based on row size and fragmentation.
The article explains that, although MySQL’s theoretical limits are far higher, practical B+‑tree page and index calculations suggest keeping a single table under about twenty million one‑kilobyte rows to avoid performance degradation, illustrated with interview examples and estimation formulas.
This article explains the myth of a 2 million‑row limit for MySQL tables, shows how primary‑key size and InnoDB page structure determine theoretical row limits, details B+‑tree indexing mechanics, calculates practical capacities, and compares B+‑tree with B‑tree performance.
This article explains how InnoDB stores data on disk, describing tablespaces, segments, extents, pages, row formats, record headers, page directories, B+‑tree indexes, and the differences between clustered and secondary indexes, with examples and diagrams to illustrate each component.
This article explains MySQL InnoDB index fundamentals, covering index types, data structures, clustered and secondary indexes, covering indexes, index pushdown, index merge, cost‑based index selection, common index‑inefficiency scenarios, and practical guidelines for designing effective indexes.
The article explains the structural differences between B+ trees and skip lists, compares their read‑write performance in MySQL and Redis, and shows why MySQL prefers B+ trees while Redis adopts skip lists for its in‑memory ZSET implementation.
This article explains MySQL index fundamentals—including classification, B+‑tree and hash structures, clustered and secondary indexes, single‑ and composite‑column indexes, covering indexes, index condition pushdown, index merge strategies, cost‑based index selection, common index‑invalidating scenarios, and practical guidelines for creating effective indexes.
This article compares B+ trees and skip lists, explaining their structures, insertion and search complexities, and why MySQL chooses B+ trees for disk‑based indexing while Redis prefers skip lists for in‑memory sorted sets, highlighting trade‑offs in read/write performance and I/O costs.
This article provides a comprehensive guide to MySQL indexing, covering index types, data structures, clustering vs. non‑clustering, hash and B+Tree indexes, covering indexes, index push‑down, index merge strategies, cost‑based index selection, common pitfalls, and practical indexing principles with illustrative examples and SQL snippets.
This article provides a comprehensive 15‑question guide on MySQL indexes, covering their definition, various types across data‑structure, physical and logical dimensions, situations where indexes fail or are unsuitable, B+‑tree advantages, search process, covering indexes, left‑most prefix, index push‑down, adding indexes to huge tables, using EXPLAIN to verify index usage, hash versus B+‑tree differences, pros and cons, and the distinction between clustered and non‑clustered indexes.
This article explores MySQL single‑table row limits, demonstrates how to generate massive test data, explains InnoDB page and index structures, calculates theoretical maximum rows based on B+‑tree parameters, and provides practical tuning tips for handling large tables.
The article explains the fundamentals of tree data structures, compares B, B+, B*, and red‑black trees, and shows how B+ trees reduce disk I/O and improve range query performance, which is why MySQL adopts B+ trees for its indexes.
This article provides a comprehensive deep‑dive into MySQL indexing, covering the definition, pros and cons, differences between clustered and non‑clustered indexes, InnoDB vs MyISAM implementations, B+Tree structure, capacity calculations, adaptive hash indexes, index pushdown optimization, and how to interpret EXPLAIN output to verify index usage.
The article explains why an UPDATE on the same row in MySQL can block in one transaction but not in another, by analyzing the types of row‑level locks (record, next‑key, gap) acquired during SELECT … FOR UPDATE, normal updates, and updates that modify primary‑key values, illustrating the lock behavior with B+‑tree examples.
This article explains how to determine the optimal order of columns in MySQL composite indexes by analyzing B+Tree structure, index selectivity, and real‑world query performance, providing practical examples and SQL demonstrations to help developers create more efficient indexes.
This article explains the fundamentals of MySQL indexes, compares them to a library catalog, details various index types and their physical implementations, demonstrates how indexes dramatically speed up queries on large tables, and provides practical SQL syntax for creating and managing indexes.
This article breaks down MySQL's InnoDB storage engine, detailing where data files reside, the different row formats (compact, redundant, dynamic, compressed), the internal 16 KB page layout, record header fields, overflow handling, and how B‑tree indexes (clustered and secondary) are built and searched.
This article explains why an UPDATE on a MySQL row that changes a regular column proceeds without blocking, while an UPDATE that modifies the primary‑key value gets blocked, by analyzing the locks set by preceding transactions, the B+‑tree index structure, and the delete‑plus‑insert rewrite performed by InnoDB.
This article explains MySQL row‑level locking by analyzing three transactions—A, B, and C—to show why an UPDATE on a primary‑key column can block while an UPDATE on a non‑indexed column proceeds without waiting.