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.
This article explains why COUNT(*) on a large InnoDB table can take seconds while MyISAM returns instantly, explores the underlying storage‑engine differences, and presents six practical techniques—including Redis counters, counting tables with transactions or triggers, parallel read threads, secondary indexes, and SHOW TABLE STATUS—to dramatically speed up row counting in MySQL.
This article explains InnoDB’s clustered and secondary index structures, why secondary index leaf nodes store primary key values, how lookups trigger “row‑lookup” (回表) operations, and how covering indexes can eliminate those extra scans, illustrated with MySQL examples.
This article explains the classification of InnoDB indexes into clustered and secondary types, describes how they are stored and accessed, and introduces optimization concepts such as back‑to‑table queries, covering indexes, and index pushdown to improve MySQL performance.
This article explains the concept of secondary indexes in HBase, describes how coprocessors (including observers and endpoints) enable server‑side processing, compares coprocessor‑based solutions such as Apache Phoenix with non‑coprocessor approaches using Elasticsearch or Solr, and outlines their advantages and trade‑offs.
This article explains MySQL index fundamentals, classifies indexes by data structure, storage, field characteristics and column count, compares B+Tree with B‑Tree, hash and red‑black trees, and demonstrates clustered and secondary indexes with practical SQL examples and diagrams.
This article explains the fundamentals of MySQL indexing, covering the purpose, optimization principles, various index types such as hash, B‑tree and B+‑tree, the structure of primary‑key and secondary indexes, index pages, and the concept of back‑table lookups, providing clear diagrams and examples.
This article explains the fundamentals of MySQL table‑lookup (回表) operations, compares primary‑key and secondary‑index structures, illustrates how low‑version MySQL performs lookups for every matching row, and shows how index pushdown in MySQL 5.6+ reduces unnecessary lookups for better performance.
This article examines Pharos, Everbright Bank's home‑grown HBase indexing middleware, detailing why existing secondary‑index solutions fall short, the design goals of low latency, simple architecture and non‑intrusiveness, and the concrete storage, pagination, and transaction‑consistency techniques that enable fast complex queries on massive data.
This article explains how to integrate Apache Phoenix with CDH‑based HBase, covering Phoenix overview, version selection, parcel installation, HBase configuration, command‑line usage, mapping existing tables, creating schemas and views, building secondary indexes, and comparing different index types for performance optimization.
This article explains how Apache Phoenix implements global secondary indexes using separate HBase tables, demonstrates index creation and data synchronization with example SQL, and provides design guidelines to optimize query latency and avoid full‑table scans in big‑data environments.
The article examines the risks of using the dangerous FLUSHDB command in Redis, explores common alternatives like SCAN, async deletion, and expiration, and proposes a secondary‑index solution using sets and sorted sets to efficiently invalidate only the affected cache entries without blocking the service.
Apache Phoenix is an open‑source SQL layer for HBase that lets developers use standard JDBC instead of the native HBase client API, offering features such as secondary indexes, transactions, and various SQL‑level optimizations while supporting full table creation, insertion, and querying.
This article demonstrates a step‑by‑step method to confirm that InnoDB secondary index leaf pages store rows sorted first by the secondary key and then by the primary key, using bcview, mysqlblock, and a small C utility to read raw .ibd pages.
This article presents a non‑intrusive, high‑performance engine that adds secondary multi‑column indexes to Apache HBase, enabling efficient complex condition queries while preserving HBase's scalability, and details its principles, architecture, query API, index configuration, and practical trade‑offs.
