This article walks through practical MySQL performance tuning from a developer's perspective, covering a real‑world example, data‑type selection, index design, and SQL query refinements, while providing concrete commands, visual illustrations, and expert Q&A for deeper understanding.
This article demonstrates how PostgreSQL 9.5's BRIN index leverages the allnulls and hasnulls flags in its regular pages to filter out unnecessary heap page scans, dramatically improving query performance while also discussing the impact of MVCC bloat and maintenance.
This article experimentally compares PostgreSQL 9.5's BRIN and BTREE indexes across seven data distributions, revealing that BRIN uses up to 6,848 times less storage while BTREE consistently outperforms BRIN in query speed, with the gap narrowing as data repetition increases.
MySQL 5.7 introduces generated (virtual) columns, allowing you to store computed values such as day‑of‑week without extra storage, enabling indexed queries that bypass function‑based limitations, improving read performance while avoiding the write‑time overhead and redundancy of manual columns.
This article explains MySQL’s indexing fundamentals, the left‑most prefix rule, B+‑tree structure, disk I/O considerations, and provides step‑by‑step methods and real‑world cases for diagnosing and optimizing slow queries in production systems.
This article analyzes a poorly performing MES reporting SQL query that scans billions of rows, identifies indexing and partitioning shortcomings, and proposes a redesign using stored procedures, temporary tables, and query‑writing best practices to achieve sub‑second response times.
This article details Baidu Maps' technical team’s refactoring of the indexing (build) unit, outlining existing bottlenecks, design challenges, and a new decoupled architecture that separates storage, incremental updates, and full‑index construction using distributed table storage and message‑driven pipelines to improve scalability and reliability.
This article analyzes the characteristics of log data, explains why HBase is chosen for log storage, discusses the shortcomings of self‑built indexes, and presents optimization strategies such as rowKey design, filter usage, coprocessor integration, and third‑party indexing to improve query performance.
This article explains how Uber engineered Schemaless, a highly available, horizontally scalable datastore built on MySQL that stores immutable JSON cells without a fixed schema, detailing its design goals, architecture, data model, trigger system, indexing strategy, and fault‑tolerant read/write mechanisms.
EventBus 3 provides a lightweight publish/subscribe framework for Android that simplifies inter‑module communication by allowing any object to be posted and received via @Subscribe methods, with optional index acceleration for faster registration, customizable thread modes, and guidance on setup, ProGuard rules, pitfalls, and alternatives.
This article details Youzan's practical architecture, indexing pipeline, advanced search module, and performance‑tuning techniques for building a commercial e‑commerce search engine using Elasticsearch, Hadoop, Kafka, and custom backend integrations.
This article explains MySQL index fundamentals, B+‑tree structures, disk I/O considerations, and practical steps for diagnosing and optimizing slow queries, illustrated with multiple real‑world examples and concrete indexing guidelines.
This guide explains practical MySQL optimization techniques—including selecting InnoDB vs. MyISAM, avoiding COUNT(*), using EXPLAIN, and adding selective indexes—to dramatically reduce query time and prevent full‑table scans.
This article describes the practical architecture of Youzan's commercial e‑commerce search engine, covering data source integration, distributed real‑time indexing with Elasticsearch, Hadoop and Kafka, advanced search modules, and several performance‑tuning techniques for large‑scale deployments.
Facebook’s Dragon distributed graph query engine optimizes high‑volume single‑hop and multi‑hop queries by introducing specialized indexing, socially aware inverted indices, and functional primitives, dramatically reducing latency, storage reads, and CPU usage while handling massive social‑graph data.
Baidu’s LBS Cloud Storage and Retrieval platform offers developers a fully managed, high‑performance solution for massive geospatial data, featuring free large‑scale storage, GeoHash‑based spatial queries, real‑time updates, strong isolation, and a series of architectural optimizations that dramatically improve latency, availability, and scalability.
This article explains how InnoDB's Adaptive Hash Index works, covering its initialization, information gathering, index and block statistics updates, page‑level hash construction, usage conditions, maintenance procedures, shortcut query mode, and monitoring via performance metrics.
This article explains MySQL InnoDB full-text indexing, covering supported modes, auxiliary tables, creation, DML handling, query processing, transaction management, cache synchronization, optimization, background threads, monitoring, stopwords, plugins, and the built‑in n‑gram parser, with code references from MySQL 5.7.
The article reviews MongoDB's storage engine developments—including MMAP, WiredTiger, RocksDB, and Memory engines—highlights replication protocol improvements, automatic sharding changes, and introduces new features such as batch and partial indexes, document validation, and join capabilities, while providing performance test results and a Q&A section.
This guide walks through ElasticSearch fundamentals, core concepts, step‑by‑step installation, Python indexing examples, index‑level tuning, and memory‑usage optimizations, providing practical tips for deploying and maintaining a high‑performance search cluster.
This guide outlines a structured MySQL DBA interview process, covering self‑introduction, resume deep‑dive, career‑development questions, fundamental knowledge such as index types, core technical skills like backup, replication and performance troubleshooting, and broader potential areas including Linux and programming language proficiency.
This article explains MySQL's indexing mechanisms—including BTREE and hash structures, page layout, index types, and practical query patterns—while showing how to interpret EXPLAIN output and avoid common pitfalls for efficient database performance.
This article walks through the fundamentals of search engine architecture, explains Lucene's role as an indexing library, details ElasticSearch's distributed design, clustering, sharding, and plugins, and demonstrates practical RESTful API usage and query DSL techniques for effective log analysis.
This article introduces NoSQL concepts, compares common storage models, and details MongoDB's replication architectures, special node roles, synchronization processes, and the variety of index types available for efficient data handling.
A slow Oracle OLTP query that consumed 56% of DB resources and averaged 28 seconds per execution was dramatically accelerated to millisecond response time by forcing the proper index, refreshing stale table statistics, and ultimately creating a cn + c_date composite index.
This article explores the unique characteristics of e‑commerce search engines, their specialized architecture, core modules, data update processes, and practical solutions for bugs, high concurrency, caching, and cold‑start challenges, offering a comprehensive guide for building robust search systems.
In MySQL 5.6, using LIMIT together with ORDER BY on a non‑unique column can produce nondeterministic result sets, a known bug that persists regardless of indexing, and the article shows how adding a secondary unique sort key resolves the uncertainty for reliable pagination.
This article provides a comprehensive guide to MySQL performance optimization, covering isolation levels, MVCC, schema design, index strategies, query execution, partitioning, replication, scaling, hardware considerations, and backup/recovery techniques for building high‑performance, reliable database systems.
This article provides a comprehensive overview of search engine evolution, core technologies such as crawling, indexing, retrieval and link analysis, platform foundations including cloud storage and computing, and techniques for improving search results through anti‑spam, user‑intent analysis, deduplication and caching.
This article chronicles the historical shift from early hierarchical data models to relational SQL, the rise of NoSQL, and the emergence of NewSQL solutions like Alibaba's DRDS, illustrating key concepts with diagrams, code examples, and a detailed Q&A.
Learn practical techniques to reduce nested loop iterations, prioritize inner-loop optimization, ensure join columns are indexed, and configure join buffers effectively, so your SQL joins run with minimal resource consumption and maximum performance.
This article explains four situations where MySQL will ignore indexes—using a non‑leading column in a composite index, leading wildcards in LIKE queries, OR conditions with non‑indexed fields, and unquoted string literals—offering guidance to improve query performance.
This article explains how retailers can replace costly ETL‑driven vendor systems with a centralized MongoDB‑based service architecture, detailing data models for products, variants, store‑specific pricing, and multi‑facet search, and showing query and indexing techniques to achieve fast, reliable catalog access.
This article explains the fundamentals of B+Tree indexes, why they matter for SQL performance, and presents three concise indexing techniques—single, composite, and covering indexes—to reduce resource consumption, I/O, and sorting overhead in relational databases.
This article explains how MySQL 5.7 introduced native JSON support with validity checks, performance improvements, and virtual column indexing, compares it to MariaDB's dynamic column and PostgreSQL, and provides step‑by‑step SQL examples for creating, inserting, querying, and indexing JSON data.
The article outlines how search engines combine the Tao of underlying principles—crawling, binary‑based Boolean indexing, PageRank matrix calculations, and TF‑IDF weighting—with specific Shu implementations to efficiently retrieve, rank, and present relevant web pages using Boolean logic, link analysis, and term relevance metrics.
