Tagged articles

14 articles

Page 1 of 1

Dec 6, 2024 · Databases

Why MySQL COUNT(*) Can Be Milliseconds Fast: Engine Tricks & Optimization Strategies

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.

InnoDBParallel ReadPerformance Optimization

0 likes · 9 min read

Why MySQL COUNT(*) Can Be Milliseconds Fast: Engine Tricks & Optimization Strategies

ITPUB

Feb 26, 2023 · Databases

Why InnoDB Stores Primary Keys in Secondary Index Leaves – A Deep Dive

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.

Clustered IndexInnoDBcovering index

0 likes · 7 min read

Why InnoDB Stores Primary Keys in Secondary Index Leaves – A Deep Dive

IT Services Circle

Mar 13, 2022 · Databases

Understanding InnoDB Index Types: Clustered Index, Secondary Index, and Optimization Techniques

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.

Clustered IndexDatabase OptimizationInnoDB

0 likes · 11 min read

Understanding InnoDB Index Types: Clustered Index, Secondary Index, and Optimization Techniques

Big Data Technology & Architecture

Nov 7, 2021 · Databases

Understanding Secondary Indexes and Coprocessor Solutions in HBase

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.

Big DataCoprocessorHBase

0 likes · 11 min read

Understanding Secondary Indexes and Coprocessor Solutions in HBase

Java Interview Crash Guide

Oct 11, 2021 · Databases

Understanding MySQL Indexes: Types, B+Tree vs Hash, and Practical Examples

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.

B+TreeClustered Indexmysql

0 likes · 15 min read

Understanding MySQL Indexes: Types, B+Tree vs Hash, and Practical Examples

Full-Stack Internet Architecture

May 24, 2021 · Databases

Understanding MySQL Indexes: Principles, Types, and B+Tree Structure

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.

B+TreeDatabase Optimizationindex

0 likes · 15 min read

Understanding MySQL Indexes: Principles, Types, and B+Tree Structure

ITPUB

Apr 28, 2021 · Databases

Understanding MySQL Index Pushdown and Table Lookup: From Basics to High-Version Optimizations

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.

Index PushdownTable Lookupmysql

0 likes · 6 min read

Understanding MySQL Index Pushdown and Table Lookup: From Basics to High-Version Optimizations

dbaplus Community

Apr 7, 2020 · Databases

How Pharos Accelerates HBase Multi‑Condition Queries with Low‑Latency Indexing

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.

HBaseLow latencyPharos

0 likes · 14 min read

How Pharos Accelerates HBase Multi‑Condition Queries with Low‑Latency Indexing

Big Data Technology Architecture

Feb 4, 2020 · Big Data

Using Apache Phoenix on CDH HBase: Installation, Configuration, and Secondary Index Creation

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.

Apache PhoenixCDHHBase

0 likes · 15 min read

Using Apache Phoenix on CDH HBase: Installation, Configuration, and Secondary Index Creation

Big Data Technology & Architecture

Sep 25, 2019 · Big Data

Designing and Using Global Secondary Indexes in Apache Phoenix

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.

Big DataHBasePhoenix

0 likes · 4 min read

Designing and Using Global Secondary Indexes in Apache Phoenix

NetEase Game Operations Platform

Jul 27, 2019 · Databases

Using Redis Secondary Indexes to Replace FLUSHDB for Targeted Cache Invalidation

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.

Data StructuresFLUSHDBcache invalidation

0 likes · 11 min read

Using Redis Secondary Indexes to Replace FLUSHDB for Targeted Cache Invalidation

Big Data Technology & Architecture

May 28, 2019 · Databases

Introduction to Apache Phoenix: An Open‑Source SQL Layer for HBase

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.

HBaseJDBCPhoenix

0 likes · 7 min read

Introduction to Apache Phoenix: An Open‑Source SQL Layer for HBase

ITPUB

Oct 18, 2016 · Databases

How to Verify B+Tree Index Order in InnoDB Using bcview and Custom Tools

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.

B+TreeInnoDBbcview

0 likes · 12 min read

How to Verify B+Tree Index Order in InnoDB Using bcview and Custom Tools

Art of Distributed System Architecture Design

Apr 6, 2015 · Databases

Non‑Intrusive High‑Performance Complex Query Engine for HBase Using Secondary Multi‑Column Indexes

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.

CoprocessorHBaseNoSQL

0 likes · 18 min read

Non‑Intrusive High‑Performance Complex Query Engine for HBase Using Secondary Multi‑Column Indexes