Part 3 of the Elasticsearch indexing optimization guide advises balancing shard and replica counts, using index aliases, leveraging the Bulk API with appropriately sized batches, dedicating data nodes, and upgrading storage (SSD/RAID 0) while monitoring resources to achieve higher throughput and scalable, reliable clusters.
Database sharding, or splitting databases into multiple instances and tables, is essential for scaling backend systems; this article explains its importance, historical context, core concepts, storage stretching techniques, index and consistency handling, and provides a practical design example for transaction systems.
This article explains the motivations behind database sharding, defines the concept, explores its historical background, discusses storage stretching, indexing and consistency challenges, and presents a practical sharding design example for transaction systems.
This article walks through diagnosing a sluggish Oracle search query with many optional filters, removing unnecessary joins and function calls, applying conditional MyBatis joins, and creating composite indexes to dramatically lower the execution plan cost from 13902 to under 100.
This article describes the motivation, design, and implementation of a label‑property graph database built as a thin layer over Aerospike, detailing its data model, API operations for graphs, indexes, vertices, edges, and query capabilities.
This guide presents ten practical MySQL performance techniques—from workload profiling and resource awareness to smart indexing, pagination, and monitoring—offering concrete steps, tool recommendations, and common pitfalls to help database administrators keep their servers fast, stable, and scalable.
A developer added a seemingly harmless filter to an Oracle SQL statement, causing the optimizer to switch the driving table and join method, which inflated execution time from 2 seconds to 90 seconds, prompting a deep dive into execution plans, indexing, and hint techniques.
This article compiles a DBAplus online session that presents a systematic SQL‑optimization methodology, rewrite tricks, common pitfalls, and a series of concrete Oracle examples—including CASE WHEN, ROWNUM pagination, hint usage, column selection, function‑call reduction, trigger avoidance, and ROWID access—plus a detailed Q&A that clarifies practical tuning questions.
This guide outlines practical principles for creating Oracle indexes, compares B‑Tree and bitmap index types, explains their creation syntax, highlights performance characteristics, and lists common situations where indexes may be ignored or unsuitable.
Before profiling performance you must locate slow queries, enable MySQL's slow query log, use EXPLAIN to analyze execution plans, and add appropriate indexes—such as on picture.album_id—to dramatically reduce scanned rows and boost query speed by hundreds of times, while balancing read/write trade‑offs.
This article explains the data structures and algorithms behind MySQL indexing, covering B‑Tree fundamentals, insertion and deletion steps with visual examples, the differences between B‑Tree and B+Tree, InnoDB's clustered and secondary index implementations, and why monotonically increasing primary keys improve performance.
This article explains the inner workings of relational databases, covering their core components, query optimization flow, indexing, merge sort, transaction handling, concurrency control, and lock management, while illustrating concepts with examples and diagrams.
This guide outlines practical MySQL optimization strategies—including table schema design, index creation, SQL tuning, connection‑pool configuration, and historical data archiving—to help developers maintain fast, scalable databases as data volume grows.
This article reviews the latest Elasticsearch 5.x enhancements—including append‑only indexing, range fields, removal of the _all field, unified highlighter, keyword normalizer, multi‑word synonyms, field collapsing, cancellable searches, partitioned term aggregations, cluster allocation explain, Java REST client updates, cross‑cluster search, batched reduce phases—and previews the major features expected in Elasticsearch 6.0 such as sparse doc values, index sorting, sequence numbers, seamless rolling upgrades, type removal, index‑template inheritance, load‑aware shard routing, and X‑Pack extensions like SQL and machine learning.
The article details Tencent File System (TFS), describing its platform components, 3D indexing techniques, high‑performance key‑value storage (TSSD) with MHT, dual‑read and smooth scaling mechanisms, hybrid index storage, host‑level FTL, Append‑Only and erasure‑coding storage engines, and how these innovations deliver scalable, low‑cost, high‑performance data storage for massive workloads.
This article introduces the architecture, key modules, and implementation details of the Little Boy platform, a Greenplum optimization and operations system that parses SQL, applies index and distribution‑key tuning, manages resources, and outlines future enhancements for large‑scale data warehouses.
This article explains the fundamentals of search engine architecture, covering text collection, indexing, ranking and evaluation, and then traces Twitter's internal search evolution from MySQL full‑text search to the Earlybird index server, Blender aggregation, and smart memory‑SSD strategies.
This guide walks through identifying the root cause of a SQL Server instance's 100% CPU usage using Profiler, analyzing the offending queries, and applying targeted index and query optimizations to dramatically reduce CPU consumption.
This article compiles frequently used MySQL SQL commands—including data migration, multi‑table updates, conditional updates, aggregation inserts, and various ALTER TABLE operations such as adding indexes, dropping and adding columns—to serve as a practical reference for database developers.
A detailed case study describes how a rapidly growing edit‑log feature caused query times to soar to 30 seconds, and walks through the step‑by‑step investigation, identification of a custom function bottleneck, data‑volume analysis, and the eventual implementation of partitioning, mandatory time filters, and composite indexing to restore acceptable performance.
A detailed Oracle performance case study shows how adding selective predicates and a supporting index to an UPDATE that runs hourly can reduce redo log generation from over 100 MB to under 1 MB and cut logical reads by more than 95%, with step‑by‑step measurements and code examples.
This article presents a comprehensive set of practical strategies for improving database query performance, covering index design, SQL statement refinements, Java backend considerations, hardware tuning, and storage‑procedure usage to accelerate operations on large‑scale relational databases.
A .NET MVC warehouse system’s MySQL view was taking over 30 seconds to fetch 70,000 inventory records, but by adding a composite index on the dictionary table, removing a UNION ALL, and adjusting view algorithms, the query time dropped to a few seconds, with a stored‑procedure alternative for flexible use.
The discussion explores practical strategies for adding an index to a MySQL table containing tens of millions of rows without stopping service, covering intermediate tables, double‑write syncing, rename operations, traffic considerations, lock types, and tooling such as Percona.
This article presents practical MySQL optimization techniques covering table schema design, character set choices, primary key strategies, field definitions, index creation, SQL query tuning, pagination tricks, join handling, connection‑pool parameter tuning, and historical data archiving for large‑scale databases.
This guide shares ten practical MongoDB performance tips—including proper index ordering, using explain() and hint(), avoiding costly query operators, leveraging capped collections, upsert, sorting limits, aggregation, drop vs remove, batch inserts, and optimal indexing for arrays and range queries—to help developers boost database efficiency.
This article explains why Zabbix's original table design slows down data insertion and query in large‑scale deployments, and provides a step‑by‑step code‑level optimization that creates separate "latest" tables with primary‑key indexes, modifies the server source, and updates the front‑end query to dramatically improve performance without affecting historical data ingestion.
This article shares practical MongoDB indexing guidelines—including compound index rules, naming limits, cardinality considerations, TTL and geospatial indexes, lock behavior, and real‑world case studies—to help DBAs and developers design efficient, safe indexes for production workloads.
This article outlines essential MongoDB document best‑practice guidelines, including key naming restrictions, collection organization, avoiding custom _id values, pitfalls of array fields, compression of large fields, using MD5 hashes for long strings, case‑sensitivity handling, and index size limits, illustrated with real‑world cases.
This article walks through a real‑world case study of a hospital HIS running on SQL Server 2008R2, detailing the system’s hardware specs, performance metrics, three‑phase optimization process—including general tuning, statement‑level fixes, and deep CPU/memory analysis—and the measurable results achieved without adding new hardware.
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.
