Tagged articles
database indexing
91 articles · Page 1 of 1
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.
B+TreeInterview questionsQuery Performance
0 likes · 14 min read


21CTOOct 19, 2026 · Backend Development
This article outlines nine practical methods for diagnosing and resolving Laravel performance issues in production, covering request measurement, N+1 query elimination, query plan optimization, memory-efficient data processing, strategic caching, queue offloading, external API timeouts, framework bootstrapping optimizations, and log/payload reduction, with code examples and a step-by-step diagnostic checklist.
CachingLaravelN+1 Queries
0 likes · 16 min read

This article details systematic troubleshooting for 900% CPU spikes in MySQL and Java processes, covering top/jstack diagnostics, show processlist analysis, index optimization, cache integration, and fixing busy-wait loops with BlockingQueue.take(), illustrated through real production case studies.
Java thread analysisMySQL performance tuningblocking queue
0 likes · 12 min read

A legacy PHP 7.2 project with 8.2s average response time was optimized to 1.4s in two weeks through seven fundamental fixes: enabling OPcache, eliminating N+1 queries via eager loading, adding composite database indexes, implementing Redis caching layers, setting HTTP timeouts for external calls, right-sizing PHP-FPM workers, and separating API middleware from web routes.
LaravelN+1 QueryOPcache
0 likes · 12 min read

This article analyzes eight common API performance bottlenecks in Spring Boot applications, including N+1 queries, improper entity returns, missing indexes, JOIN FETCH pagination issues, in-memory filtering, deep pagination, long transactions, and caching strategies, with code examples and optimization techniques for each.
API performanceCachingDTO Projection
0 likes · 14 min read

This article provides a comprehensive guide to MySQL's EXPLAIN command, detailing each column in the execution plan output with practical examples to help developers analyze and optimize SQL query performance.
EXPLAINMySQLSQL Performance
0 likes · 8 min read

The article analyzes flawed approaches for querying encrypted phone numbers by last four digits — memory decryption, ciphertext fuzzy matching, partial encryption, and mapping tables — and presents a three-field design (full ciphertext, masked display, indexed last-four plaintext) that ensures compliance, security, and millisecond query performance at million-user scale.
AES-256ComplianceQuery Performance
0 likes · 6 min read

The article recounts how an API that originally used OFFSET pagination suffered 2‑3 seconds response time, and after rewriting the query to Keyset (Seek) pagination—adding a secondary sort key for stability and exploring cursor pagination, index‑only OFFSET, and materialized views—the same endpoint now responds in under 200 ms, with detailed benchmark comparisons.
Cursor PaginationKeyset PaginationSQL
0 likes · 7 min read


liandkAug 26, 2026 · Databases
The article explains that MySQL’s EXPLAIN command acts as a SQL health check, detailing each of its 12 fields—especially type, key, rows, and Extra—while providing real‑world examples, a step‑by‑step diagnostic workflow, and common pitfalls to help developers reliably tune and troubleshoot queries.
EXPLAINMySQLPerformance Tuning
0 likes · 10 min read

This article walks through a complete performance‑optimization and deployment pipeline for a Spring Boot blog, covering multi‑level caching with Caffeine and Redis, database indexing and cursor pagination, read‑write splitting, asynchronous processing, rate limiting, Docker multi‑stage builds, Nginx reverse‑proxy setup, Actuator monitoring, custom metrics, health checks, alerting, JMeter load testing, and JVM tuning.
CaffeineDockerRedis
0 likes · 17 min read

This article explains PostgreSQL's native full‑text search, its core concepts of tsvector and tsquery, demonstrates how to use built‑in functions and operators, compares built‑in, zhparser, and pg_search extensions for Chinese tokenization, and provides best‑practice tips for indexing, triggers, and performance optimization.
BM25Chinese tokenizationPostgreSQL
0 likes · 14 min read

The article explains how random UUIDs degrade database write performance, introduces the RFC 9562 UUIDv7 format that embeds a timestamp for natural ordering, shows JDK 26’s new UUID.ofEpochMillis() API, discusses its monotonicity limitation, and compares UUIDv7 with Snowflake and ULID for practical ID generation.
JDK 26JavaSnowflake
0 likes · 12 min read

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.
B+TreeB-treeR-Tree
0 likes · 51 min read

The article shows how ChatGPT examines JMeter load‑test results, identifies CPU, database and I/O bottlenecks, and then proposes concrete optimizations such as adding indexes, tuning HikariCP pool settings, caching, code refactoring, and monitoring to improve login throughput and latency.
ChatGPTHikariCPJMeter
0 likes · 11 min read

This article explains the semantic differences, performance characteristics, and optimal usage scenarios of COUNT(*), COUNT(1), and COUNT(column) in MySQL 8.0+, covering engine behavior, indexing effects, common misconceptions, and practical best‑practice recommendations for interview preparation and real‑world development.
COUNTInterviewMySQL
0 likes · 10 min read

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.
B+TreeInnoDBMySQL
0 likes · 21 min read

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.
B+TreeInnoDBMySQL
0 likes · 5 min read

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.
B+TreeB-TreePostgreSQL
0 likes · 7 min read

This guide details the four index types in StarRocks 3.3—Bitmap, Bloom Filter, N-Gram Bloom Filter, and Full-Text Inverted Index—covering their core principles, supported data types, creation syntax, ideal use cases (low/high cardinality, fuzzy/long-text queries), anti-patterns, and real-world optimization examples with performance metrics.
Bitmap IndexBloom FilterN-Gram Bloom Filter
0 likes · 27 min read

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.
B+TreeInnoDBMySQL
0 likes · 20 min read

This article walks through eight practical MySQL optimization techniques—including smarter LIMIT usage, avoiding implicit type conversion, rewriting UPDATE/DELETE with JOINs, handling mixed sorting, replacing EXISTS with joins, pushing predicates, narrowing result sets early, and leveraging WITH clauses—to dramatically reduce query execution time from seconds to milliseconds.
MySQLPerformance TuningQuery Planning
0 likes · 13 min read

This guide explains why MySQL cannot index JSON columns directly and demonstrates two practical work‑arounds—using generated columns in MySQL 5.7 and functional indexes in MySQL 8.0.13—to efficiently index a nested request.email field for fast queries.
JSONMySQLdatabase indexing
0 likes · 8 min read

This article presents a comprehensive guide to optimizing API performance in Spring Boot projects, covering batch processing, asynchronous execution, caching, pre‑processing, pooling, parallelization, indexing, transaction management, code refactoring, pagination, SQL tuning, and lock granularity, complete with code examples and diagrams.
API optimizationAsynchronous ProcessingBackend Performance
0 likes · 10 min read

An in‑depth MySQL study compares auto‑increment, UUID, and random Snowflake keys by building three identical tables, running insert‑performance tests with Spring Boot/JdbcTemplate, and analyzing index structures, revealing why auto‑increment keys outperform UUIDs despite the latter’s uniqueness.
MySQLSpring BootUUID
0 likes · 11 min read

This article examines the performance pitfalls of MySQL deep pagination and presents six practical optimization methods—including cursor‑based pagination, deferred joins, covering indexes, table partitioning, precomputed pages, and Elasticsearch integration—to dramatically speed up large‑scale order queries.
Cursor PaginationMySQLPagination
0 likes · 8 min read

This article presents ten practical rules for designing database indexes—covering business‑scenario analysis, the left‑most prefix principle, avoiding over‑indexing, using covering indexes, data‑type choices, function traps, prefix indexes, NULL handling, maintenance scripts, and monitoring—each illustrated with SQL examples, diagrams, and real‑world performance gains.
Covering IndexMySQLQuery Tuning
0 likes · 13 min read

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.
B+TreeNon-clustered IndexSQL
0 likes · 5 min read

This article presents a comprehensive set of twelve backend optimization strategies—including batch processing, asynchronous execution, caching, pooling, parallelism, indexing, transaction management, and SQL tuning—to dramatically reduce API latency and improve overall system efficiency.
API optimizationBackend PerformanceCaching
0 likes · 9 min read

Discover twelve practical SpringBoot performance optimization techniques—from connection pool tuning and JVM memory settings to caching, async processing, and full‑stack monitoring—each illustrated with code snippets and actionable guidance to prevent full‑table scans, OOM errors, and latency spikes in high‑traffic applications.
JVMJavaSpringBoot
0 likes · 13 min read

The article explains MySQL's Index Merge optimization, detailing its three types—Intersection, Union, and Sort‑Union—when it is beneficial, how to design indexes and rewrite queries for better performance, and the associated costs and limitations.
MySQLPerformance Tuningdatabase indexing
0 likes · 5 min read

This article explains how to efficiently search product titles in a few‑million‑row MySQL table by using built‑in FULLTEXT indexes, jieba‑php tokenization, and the ngram parser, avoiding unnecessary external search engines and providing practical code examples.
FullTextMySQLNGram
0 likes · 5 min read

This article explains the principles and practical implementation of vector retrieval in StarRocks, covering approximate nearest‑neighbor algorithms, index design, query planning, performance optimizations, real‑world case studies, and future challenges, showing how query latency dropped from 15 seconds to 2 seconds while cutting costs to a third.
ANNApproximate Nearest NeighborHNSW
0 likes · 25 min read

This article presents a comprehensive overview of the RAG2.0 engine design, covering RAG1.0 limitations, effective chunking methods, accurate retrieval techniques, advanced multimodal processing, hybrid search strategies, database indexing choices, and future directions such as agentic RAG and memory‑enhanced models.
RAGRetrieval-Augmented Generationchunking
0 likes · 23 min read

A MySQL data‑migration using INSERT INTO SELECT caused massive payment‑record loss due to full‑table scans and lock contention, highlighting the need for proper indexing and awareness of transaction isolation when moving large datasets.
INSERT INTO SELECTMySQLdatabase indexing
0 likes · 7 min read

This article examines MySQL's recommendation against UUID primary keys by creating three tables with different key strategies, running large‑scale insert tests using Spring JdbcTemplate, and analyzing the resulting performance, index behavior, and trade‑offs of auto‑increment, UUID, and random keys.
MySQLUUIDauto-increment
0 likes · 12 min read

Learn how to identify and resolve extreme CPU usage spikes—up to 900%—in MySQL and Java applications by using top, show processlist, indexing, caching, thread analysis, and code adjustments, with real‑world examples and step‑by‑step command guides.
CPU optimizationJavaMySQL
0 likes · 11 min read

This article explores how various unique identifier schemes—random UUIDs, sequential numbers, hierarchical paths, and distributed ID generators—are applied across programming languages, file systems, databases, and networks, and explains when and how to enforce uniqueness using pre‑validation, database constraints, or distributed locks such as Redis and Zookeeper, while also linking these mechanisms to CAS‑based concurrency control.
CASDistributed LockIdempotency
0 likes · 23 min read

This article analyzes a slow MySQL pagination query caused by uneven data distribution and range scans, demonstrates how to examine execution plans and data distribution, and shows that adding a composite index with Index Condition Pushdown dramatically reduces scanned rows and query time.
MySQLPaginationdatabase indexing
0 likes · 15 min read

This article provides comprehensive guidance on effective database indexing, covering when and how to create various index types, practical SQL examples, strategies for equality, range, fuzzy, join, and composite queries, and tips for verifying index usage with EXPLAIN.
Performance TuningSQL Optimizationdatabase indexing
0 likes · 11 min read

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.
B+TreeBitmap IndexSQL
0 likes · 12 min read

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.
B+TreeMySQLdata structures
0 likes · 9 min read

This comprehensive guide covers high‑performance development techniques—including I/O optimization, zero‑copy, multiplexing, concurrency, thread‑pool design, lock‑free programming, inter‑process communication, RPC, serialization, database indexing, caching strategies, Bloom filters, full‑text search, and load balancing—to help developers build fast, scalable, and reliable systems.
Bloom FilterCachingConcurrency
0 likes · 62 min read

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.
B+TreeB-treeR-Tree
0 likes · 46 min read


政采云技术Jan 17, 2024 · Databases
This article explains how to leverage MySQL 5.7's built‑in ngram full‑text parser to implement lightweight Chinese full‑text search, covering configuration, index creation, query modes, operator usage, and performance considerations.
Boolean ModeMySQLNGram
0 likes · 12 min read


政采云技术Jan 2, 2024 · Databases
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.
AlgorithmsB+TreeB-Tree
0 likes · 19 min read

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.
B+TreeB-TreeColumn Storage
0 likes · 16 min read

This article explains how column store indexes in SQL Server store each column separately, dramatically improve query performance through batch processing and compression, outlines their physical structure, encoding methods, creation syntax, maintenance steps, and space usage considerations.
Column StoreData CompressionSQL Server
0 likes · 11 min read

Discover a comprehensive set of ten practical techniques—including batch processing, asynchronous execution, caching, pooling, parallelization, indexing, transaction management, and SQL tuning—to dramatically reduce API latency and improve efficiency in legacy backend systems, illustrated with real-world examples and code snippets.
API performanceAsynchronous Processingbackend optimization
0 likes · 8 min read

This article walks through nine common MySQL performance pitfalls—from inefficient LIMIT clauses and implicit type conversions to sub‑query updates, mixed sorting, EXISTS usage, condition pushdown, early range reduction, and intermediate result pushdown—showing how to rewrite each pattern for dramatically faster execution.
LIMIT clauseMySQLQuery Performance
0 likes · 15 min read

This article presents a comprehensive guide to optimizing API latency by covering batch processing, asynchronous execution, caching, pre‑processing, pooling, parallelism, indexing, transaction management, code refactoring, pagination, SQL tuning, and lock granularity, each illustrated with practical code examples and diagrams.
API performanceAsynchronous Processingbackend optimization
0 likes · 10 min read

This comprehensive guide outlines 46 practical MySQL optimization rules—from understanding the query execution flow and naming conventions to index design, join strategies, pagination techniques, and safe use of data types—providing concrete examples and code snippets that help developers dramatically improve database performance and reliability.
Best PracticesMySQLPerformance Tuning
0 likes · 26 min read

This article explains the evolution of tree-based index structures—from basic binary search trees through AVL and red‑black trees to B‑trees and B+‑trees—highlighting their trade‑offs and why MySQL adopts B+‑trees for efficient disk‑based indexing.
AVLB+TreeB-Tree
0 likes · 10 min read

This article demonstrates how creating a covering composite index on MySQL tables can reduce a 55‑second aggregation query on five million rows to about two seconds by eliminating the need for row lookups and fully utilizing index data.
Covering IndexMySQLSQL Optimization
0 likes · 7 min read

This article presents a comprehensive set of MySQL optimization guidelines, covering the avoidance of SELECT *, proper use of specific columns, eliminating OR and != in WHERE clauses, preferring numeric types, choosing VARCHAR over CHAR, using TRUNCATE, batch operations, index design, join selection, GROUP BY efficiency, and other practical tips to improve query performance.
MySQLQuery PerformanceSQL Optimization
0 likes · 18 min read

This article explains the value of short URLs, the HTTP redirection principle behind them, and provides detailed design and implementation strategies—including hash‑based and distributed‑ID approaches, base‑62 encoding, database storage, caching, indexing, and security measures—to build a scalable short‑link service.
CachingHTTP redirectdatabase indexing
0 likes · 12 min read

This article examines frequent MySQL performance problems—such as inefficient LIMIT usage, implicit type conversion, suboptimal joins, mixed ordering, EXISTS subqueries, condition pushdown limitations, early range reduction, and intermediate result handling—and provides concrete rewrites and execution‑plan analyses that dramatically improve query speed.
MySQLPerformance TuningQuery Rewriting
0 likes · 16 min read

This article explains common multi‑dimensional query scenarios, compares basic indexing tricks and data heterogeneity approaches, then details how to integrate Elasticsearch with MySQL—including architecture, data import, Java client usage, code examples, and best‑practice recommendations—for scalable, near‑real‑time search.
ElasticsearchJava ClientMulti-dimensional query
0 likes · 15 min read

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.
B+TreeHBaseLSM-tree
0 likes · 7 min read

The article explains why using LIMIT together with ORDER BY in MySQL 5.6 can cause duplicate records on subsequent pages, analyzes the priority‑queue optimization that makes the sort unstable, and offers practical solutions such as indexing, proper pagination logic, and query adjustments.
LIMITMySQLORDER BY
0 likes · 8 min read

This article explains the fundamentals of B‑Tree and B+Tree data structures, covering their definitions, differences from red‑black trees, node properties, and detailed insertion procedures, while highlighting why B+Tree is preferred for database and file indexing.
B+TreeB-TreeInsertion Algorithm
0 likes · 7 min read

This article details how to optimize a high‑traffic weekly report service by adding composite indexes, partitioning data into weekly tables, leveraging Redis caching, and employing data pre‑warming to dramatically reduce query latency and database load.
BackendCachingdatabase indexing
0 likes · 8 min read

This article explains why MySQL's default full‑text index struggles with Chinese, demonstrates how to configure token size parameters, activate the ngram parser plugin, and adjust queries (including Boolean mode) to achieve reliable Chinese full‑text search results.
Boolean ModeChineseMySQL
0 likes · 12 min read


ITPUBJun 20, 2021 · Databases
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.
B+TreeMySQLdatabase indexing
0 likes · 35 min read

This article explains InnoDB's storage hierarchy (sector, block, page), calculates how many rows fit in a 16KB page, shows how B+‑tree height and pointer counts determine total record capacity, and demonstrates the I/O cost of primary and secondary index lookups using practical MySQL commands.
B+TreeInnoDBMySQL
0 likes · 8 min read

This article explains MySQL full-text (inverted) indexes, their underlying auxiliary tables, buffer pool configuration, document ID handling, and the special transaction semantics required for inserting and querying indexed data, providing practical SQL examples and performance considerations.
Full-Text IndexInnoDBMySQL
0 likes · 9 min read

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.
B+TreeInnoDBMySQL
0 likes · 9 min read

This article explains the difference between MySQL multi-value indexes and composite indexes, demonstrates how to create and query multi-value indexes using JSON columns, and shows performance benefits and practical scenarios when dealing with tables that have a large number of columns.
JSONMulti-Value IndexMySQL
0 likes · 13 min read

This article reviews MySQL composite index syntax, explains the mandatory condition that the leftmost column must appear in the query filter, analyzes seven representative SQL statements that cannot benefit from a composite index, and provides practical refactoring suggestions such as adding single‑column indexes or creating more appropriate composite indexes.
Composite IndexMySQLdatabase indexing
0 likes · 11 min read

This article explains how MySQL composite indexes work, the syntax for creating them, the conditions required for their use, and how different query patterns affect index utilization, illustrated with multiple SQL examples and execution‑plan analyses.
Composite IndexMySQLQuery Planning
0 likes · 14 min read

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.
B+TreeBloom FilterHBase
0 likes · 6 min read

This guide walks developers through a step‑by‑step progression of performance‑boosting techniques—including zero‑copy I/O, epoll, thread pools, lock‑free programming, IPC, RPC, database indexing, caching, Bloom filters, full‑text search, and load balancing—to help build faster, more scalable backend services.
Backend PerformanceConcurrencyI/O optimization
0 likes · 22 min read

This article explains how MySQL implements hash indexes, illustrates their data distribution on memory tables, and evaluates their suitable scenarios and limitations compared with B‑tree indexes through detailed SQL examples and performance analysis.
MySQLdatabase indexinghash index
0 likes · 13 min read

This article explains MySQL's hash index by introducing fundamental data structures such as arrays, dictionaries, linked lists, and hash tables, discussing their advantages and disadvantages, and showing how they are used in MySQL with practical code examples.
MySQLdata structuresdatabase indexing
0 likes · 10 min read

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.
B+TreeStoragedata structures
0 likes · 11 min read


ITPUBOct 19, 2020 · Databases
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.
B+TreeInnoDBMySQL
0 likes · 16 min read

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.
B+TreeInterview Preparationdata structures
0 likes · 9 min read

This guide explains MongoDB 4.2's wildcard indexes, showing how to create single‑field and full‑field wildcard indexes, how they index dynamic schemas, their query behavior for objects and arrays, supported index types, and important limitations such as sparsity and $exists handling.
MongoDBNoSQLWildcard Index
0 likes · 9 min read


ITPUBSep 7, 2020 · Databases
A production MySQL server was overwhelmed by a full‑table‑scan query that omitted the leftmost index column, causing CPU saturation and read‑only alerts; the article explains the root cause, temporary mitigation, the leftmost‑match principle, code‑level bug, and key lessons learned.
Java ValidationMySQLSQL Performance
0 likes · 9 min read

The author describes how they transformed a sluggish SSRS report that took over thirty minutes to run into a fast, sub‑second query by analyzing the original SQL, adding missing indexes, avoiding full table scans, and rewriting the logic as a flexible stored procedure, complete with code examples and performance tips.
MESPerformance TuningSQL Optimization
0 likes · 24 min read

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.
B+TreeInnoDBMySQL
0 likes · 9 min read

This article explains the motivation behind B‑trees, their definition, node constraints, height analysis, and compares B‑trees with B+‑trees, highlighting how multi‑level storage and batch disk I/O reduce lookup costs in large‑scale data systems.
AlgorithmsB-TreeI/O optimization
0 likes · 10 min read

This article examines why NOT EXISTS can perform poorly in MySQL, demonstrates how rewriting the query with a LEFT JOIN dramatically reduces execution time, and explains the underlying indexing and profiling details that affect query efficiency.
LEFT JOINMySQLNOT EXISTS
0 likes · 6 min read

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.
B+TreeInnoDBMySQL
0 likes · 11 min read

This article walks through a MySQL 5.6 scenario where a nested subquery to find students scoring 100 in Chinese runs for over 30,000 seconds, then demonstrates how adding single‑column and multi‑column indexes, analyzing execution plans, and applying join optimizations can reduce the runtime to milliseconds, while also covering best practices for index design and query tuning.
MySQLQuery PlanSQL Performance
0 likes · 9 min read

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.
B+TreeInnoDBMySQL
0 likes · 15 min read

This article presents a step‑by‑step analysis of several real‑world MySQL slow‑query problems, demonstrates how to reproduce the issues, examines execution plans, applies concrete optimizations such as removing function calls, adjusting date comparisons, adding bigint indexes, and explains the impact of different EXPLAIN types on performance.
EXPLAINMySQLSQL
0 likes · 12 min read

This article walks through a real‑world case where a single MySQL statement took over 1000 ms, showing how to use Druid, JMC, and process‑list analysis to uncover deadlocks, add missing indexes, kill blocking threads, and restore normal performance.
MySQLdatabase indexingdeadlock
0 likes · 6 min read


ITPUBOct 31, 2017 · Databases
This article explains why database indexes are essential, describes how they are structured and stored, walks through concrete calculations for a 5‑million‑row MyISAM table, compares linear and binary search costs, and outlines when and how to use indexes effectively.
B+TreeMySQLdatabase indexing
0 likes · 10 min read


21CTOJun 14, 2016 · Databases
An in‑depth case study of a sluggish SSRS report for Siemens China's MES system reveals how missing indexes, full‑table scans on billion‑row tables, and misguided partitioning cripple performance, and demonstrates practical SQL refactoring, stored‑procedure redesign, and indexing strategies that cut query time from hours to seconds.
PartitioningSQLdatabase indexing
0 likes · 10 min read

This article details a multi‑stage performance overhaul of a large‑scale chat application's SQLite database, covering trace‑based I/O analysis, view caching with Fragments, table‑splitting experiments, index size reduction, and the development of a PageTracer tool to quantify page‑level gains.
AndroidFragmentPageTracer
0 likes · 21 min read

While InnoDB’s row‑level locking generally outperforms MyISAM’s table‑level locking under high concurrency, misuse can negate its advantages; this guide explains why indexing, precise lock scopes, minimal range queries, smaller transactions, and lower isolation levels are essential for optimal InnoDB performance.
InnoDBMySQLRow-Level Locking
0 likes · 2 min read
