This article explains why MySQL queries become slow, how proper index design, MDL locks, sharding, read‑write separation, and the use of ElasticSearch or HBase can improve query performance in large‑scale systems, and provides practical tips and code examples for each technique.
This article explains InnoDB's record storage, primary and secondary index mechanics, composite index rules, and provides practical e‑commerce sharding and table‑design guidelines with real‑world case studies to help developers optimize MySQL performance and storage efficiency.
The article documents a MySQL 5.6.35 deadlock occurring during inventory deduction, explains how Index Merge caused inconsistent lock ordering across multiple indexes, and offers solutions such as forcing specific indexes, disabling Index Merge, adding composite indexes, or restructuring the SQL to prevent the deadlock.
This article explains Ctrip's design and implementation of a flexible Elasticsearch data‑synchronization framework that handles full, incremental, ID‑based, and time‑based syncing from multiple sources, addressing the shortcomings of existing tools and simplifying complex data assembly for business search.
This article provides an in‑depth guide to MySQL internals, covering the SQL execution process, server and storage engine layers, binlog/redo/undo logs, index structures, transaction isolation levels, lock types, MVCC implementation, buffer pool management, and practical optimization techniques.
During a production outage, a high‑volume SQL query without a required leftmost index field caused full table scans that saturated CPU, prompting a temporary index fix and revealing a deeper bug involving missing @NotNull validation and improper parameter handling, highlighting MySQL’s left‑most index rule.
This guide consolidates everyday Elasticsearch tuning techniques—covering configuration file tweaks, system‑level settings, and usage‑level optimizations such as memory locking, discovery settings, fault detection, queue sizing, translog handling, bulk indexing, shard management, and disk I/O—to help you build a stable, high‑throughput search cluster.
This article presents a comprehensive set of mandatory and recommended conventions for MySQL table creation, SQL writing, and index design, aiming to improve backend development efficiency, reduce erroneous DDL and queries, and enhance overall database performance.
This article presents a comprehensive list of 52 practical SQL optimization techniques, covering index usage, query rewriting, avoiding full table scans, proper data types, transaction handling, backup strategies, and other best practices to improve database performance and scalability.
This article explains how to design, tune, and optimize an Elasticsearch‑based data platform handling hundreds of billions of records, covering Lucene fundamentals, shard routing, index and query performance tricks, and practical benchmark results for large‑scale deployments.
When a MySQL table grows large, performance degrades sharply, so this guide explains single‑table tuning, proper field choices, index strategies, query best practices, engine differences, system parameters, hardware upgrades, read‑write splitting, caching layers, table partitioning, vertical and horizontal sharding, and how to choose suitable sharding solutions.
This article provides a comprehensive overview of database system design, covering its historical evolution, classification of relational and NoSQL databases, key architectural patterns, consistency models, indexing techniques, storage formats, compression methods, and practical considerations for selecting the right database solution.
The presentation details the design, implementation, and real‑world applications of the JD Urban Spatio‑Temporal Data Engine (JUST), a distributed, scalable database that handles massive, complex spatio‑temporal data with novel storage, indexing, and query techniques, demonstrating high performance and ease of use across smart‑city scenarios.
This article examines the core data models and indexing mechanisms of three leading OLAP engines—Kylin, Druid, and ClickHouse—comparing their architectures, query performance characteristics, and suitability for aggregation versus detailed query workloads in modern big data environments.
This article introduces Elasticsearch’s core concepts—including clusters, node roles, documents, mappings, and shards—and walks through the complete indexing workflow from client request to replica synchronization, highlighting key settings, routing calculations, and the role of refresh and flush operations.
This article walks through the challenges of querying a 300‑billion‑row Hive table, analyzes why traditional partitioning, indexing, and bucketing fall short, and presents a practical solution that combines active‑user segmentation and a redesigned array‑based data model to cut query time from hours to minutes.
This guide explains how to boost MySQL performance through soft optimizations like query analysis, index usage, and table restructuring, as well as hard optimizations involving hardware upgrades, configuration tuning, sharding, read‑write splitting, and cache clustering.
This article presents a thorough overview of MySQL performance tuning, covering index usage, query rewriting, SELECT statement ordering, DML batch operations, hint directives, and table design recommendations to help developers write efficient SQL for large‑scale systems.
This article explains how proper index creation boosts MySQL query performance, detailing the mechanics of indexes, why B+Tree is chosen over binary and balanced trees, the storage differences between MyISAM and InnoDB, and practical guidelines for designing effective single‑column, composite, and covering indexes.
This article explains the different index mechanisms used by MySQL (MyISAM and InnoDB) and Lucene, compares them with Elasticsearch's inverted index, and discusses how these structures affect storage, memory usage, and query performance.
This article explains how to identify slow‑query symptoms, gather problematic SQL statements, apply common writing and indexing tips, read execution plans, and walk through a complete MySQL optimization case, enabling readers to become proficient database performance engineers.
This article explains Elasticsearch's data ingestion, retrieval, and search workflows, details the underlying indexing mechanisms, and provides comprehensive system‑level, shard‑level, and query‑level tuning recommendations—including configuration snippets and best‑practice strategies for high‑throughput and low‑latency deployments.
This comprehensive guide covers the factors that impact MySQL server performance, strategies for handling large tables and transactions, hardware and OS tuning, storage‑engine selection, replication modes, high‑availability architectures, indexing techniques, and practical SQL query‑optimization methods.
This guide outlines comprehensive MySQL best‑practice standards covering naming conventions, storage engine selection, charset usage, table and column comments, size limits, partitioning, hot‑cold data separation, index design, SQL coding rules, and operational safeguards to ensure performant, maintainable, and secure database systems.
This article explains how using MySQL's INSERT INTO … SELECT without proper indexing can cause full‑table scans and locks, illustrates the problem with a real‑world order‑migration incident, and provides a concrete solution by adding an index to the source column.
WeChat Pay’s Hermes‑based log system ingests trillions of entries daily, storing petabytes across a 200‑node HDFS cluster with four‑nine availability, while LSM‑style writes, separate inverted indexes and hot‑cold tiering cut memory, disk and cost by up to 70 % and keep 95 % of queries under five seconds.
This article explains the hidden costs of using SELECT * in MySQL queries, explores how unnecessary columns, large data types, and loss of covering‑index opportunities degrade performance, and shows how composite indexes and proper indexing strategies can dramatically speed up data retrieval.
This article explains Kafka’s internal index files—OffsetIndex and TimeIndex—including their file formats, how they store relative offsets and timestamps, the space‑saving optimizations, the processes for appending, truncating, and looking up entries, and best‑practice cautions for handling these indexes.
This article explains Elasticsearch's logical and physical design, how documents are structured and indexed, the role of shards and replicas, and provides practical examples of indexing, searching, aggregations, and retrieving documents using RESTful APIs.
This article explains Elasticsearch’s internal architecture, from cloud clusters and nodes to shards and Lucene’s inverted index, covering indexing, storage structures, query processing, caching, scaling, routing, and real‑world request handling, with detailed diagrams and examples.
This article provides a detailed collection of basic Elasticsearch interview questions and answers, covering core concepts such as cluster and node architecture, indexing, mapping, search APIs, analyzers, tokenizers, aggregation, security, and operational tools, along with practical code snippets for common tasks.
This article compiles a series of technical interview questions and answers covering MySQL query best practices, index optimization, B+‑tree storage, binlog recovery, master‑slave replication, design pattern usage in Laravel, a PHP binary‑search insertion algorithm, transport‑layer protocols, TCP three‑handshake, and concurrency control strategies.
This article shares practical Elasticsearch and Lucene optimization techniques—including index structure tuning, shard routing, DocValues management, and query pagination—to achieve sub‑second search performance on datasets exceeding a billion records while supporting multi‑year historical queries.
This article examines the data models and index structures of the three leading OLAP engines—Kylin, Druid, and ClickHouse—offering technical explanations, code examples, and a concise comparison to help readers understand their strengths, limitations, and suitable use cases.
This article shares the author's exploration of in‑memory search engine design, covering system understanding, core architecture, thread and task models, intersection algorithms, lookup optimizations, and componentization, aiming to fill the scarce documentation on memory‑based retrieval engines.
This article explains why a terms query with an array fails inside an Elasticsearch search template, demonstrates the correct Mustache‑based template syntax, and provides step‑by‑step code examples for defining the index, inserting data, creating the template, and executing the query.
This article describes Ctrip's experience building a flexible, high‑performance Elasticsearch synchronization framework that supports full‑load, MQ‑based, ID‑based, and time‑based incremental indexing for diverse data sources such as Hive, MySQL, SOA services, and message queues.
The article presents a novel spatio‑temporal trajectory risk metric and an efficient query framework, including a space‑first index (SFT) and distributed storage, to identify high‑risk contacts of COVID‑19 patients from massive movement data, with real‑world deployment results in several Chinese cities.
This article details how to improve performance of massive MySQL tables on Alibaba Cloud by redesigning schemas and indexes, migrating to PolarDB with read‑write separation, partitioning historical data using the X‑Engine storage engine, and leveraging PolarDB's parallel query capabilities for faster analytics.
This article explains Elasticsearch's foundation on Lucene, compares its concepts to MySQL, describes inverted indexing, and provides a step‑by‑step guide for installing, configuring, and using the basemkhirat/elasticsearch Laravel plugin with code examples and tips for Chinese analysis.
This article explores eight common MySQL performance pitfalls—including inefficient LIMIT usage, implicit type conversion, subquery updates, mixed sorting, EXISTS clauses, condition push‑down, early result narrowing, and intermediate result push‑down—and provides rewritten SQL examples that dramatically reduce execution time.
This article explains the various MySQL index implementations—including B‑Tree, B+Tree, hash, clustered, non‑clustered, and covering indexes—illustrates their internal structures with diagrams, provides SQL examples, and offers practical guidelines for selecting and using indexes efficiently.
This article explains Python tuples, covering their definition, creation syntax, single‑element nuances, indexing and slicing behavior, and how to modify values indirectly by converting to a list, complete with code examples and visual illustrations.
This tutorial explains Python's list data structure, covering creation, indexing, length retrieval, element addition, insertion, removal with pop, and replacement, illustrated with clear code snippets and screenshots for easy learning.
This article presents a comprehensive guide to MySQL performance tuning, covering the use of EXPLAIN, proper handling of IN clauses, selective column queries, limit usage, index best practices, avoiding costly operations, pagination techniques, full‑text search, join optimization, and advanced directives such as FORCE INDEX and STRAIGHT_JOIN.
This article analyzes a severe MySQL performance issue caused by massive user‑access logs, then presents three solution paths—optimizing the existing database, migrating to a compatible high‑performance database, and adopting big‑data technologies—detailing design best practices, indexing, partitioning, sharding, and cloud‑native options to restore query speed and scalability.
This article analyzes the performance problems of a massive MySQL 5.6 RDS table storing billions of user‑activity records and presents three practical solutions—optimizing the existing schema and queries, migrating to a MySQL‑compatible high‑performance database, and adopting a big‑data platform—detailing design, indexing, partitioning, sharding, and cloud‑native options.
The article details how a half‑hour SSRS report query on a massive MES database was transformed into a sub‑second operation by analyzing the original SQL, eliminating full table scans, adding proper indexes, using temporary tables and a well‑designed stored procedure, while also discussing common pitfalls and best‑practice tips for high‑performance database querying.
This article explores MySQL performance limits, concurrency settings, query‑time best practices, table design choices, index classifications, and a comprehensive set of SQL optimizations—including batch processing, operator rewrites, and pagination tricks—to help developers eliminate slow queries and build scalable database solutions.
This article explains the internal workings of Elasticsearch, covering its cloud‑based cluster architecture, Lucene‑based indexing structures such as segments, shards, inverted indexes, stored fields and doc values, as well as search processing, caching, merging, routing, and scaling strategies.
This article examines performance problems with a 20‑million‑row MySQL user‑log table on Alibaba Cloud RDS, outlines three solution paths—optimizing the existing database, migrating to a MySQL‑compatible high‑performance service, and adopting a big‑data engine—and provides detailed guidance on schema design, indexing, partitioning, and practical SQL tweaks.
This article examines why a MySQL 5.6 RDS table with tens of millions of rows becomes unbearably slow, then presents three concrete approaches—optimizing the existing database, migrating to a MySQL‑compatible service, and adopting a big‑data engine—detailing design, indexing, partitioning, sharding, and cloud options to restore performance.
This article presents a thorough overview of MySQL performance tuning, covering step‑by‑step SQL optimization techniques, common pitfalls, index and table‑structure improvements, cache parameter adjustments, and practical guidelines to reduce I/O and CPU bottlenecks while maintaining data integrity.
This article explains what database indexes are, outlines their advantages and disadvantages, advises when to create or avoid them, compares common index data structures such as B+Tree, Hash, and FullText, and details MySQL's MyISAM and InnoDB index implementations.
This article explains why SQL optimization is a cost‑effective way to boost system performance, outlines fundamental principles, common pitfalls such as leading wildcards, IN/OR/NULL misuse, and provides practical tips on indexing, execution order, hints, DML batching, pagination, and table design for MySQL databases.
This article introduces Elasticsearch as a distributed open‑source search and analytics engine, explains its core concepts and architecture, compares it with relational databases, details installation steps, configuration, indexing, analyzers, query DSL, pagination, sorting, and provides practical examples for building search functionality.
This article explains why MySQL tables with massive row counts suffer performance degradation and provides practical guidance on single‑table optimization, field and index design, query tuning, engine selection, system parameters, hardware upgrades, read‑write splitting, caching layers, partitioning, vertical and horizontal sharding, as well as client‑side and proxy‑side sharding solutions.
This article explains Elasticsearch's internal mechanisms for indexing, querying, and retrieving data, covering the roles of coordinating nodes, primary and replica shards, the refresh and commit cycles, near‑real‑time search, and the underlying Lucene inverted index.
This article analyzes a real‑world MySQL incident where using INSERT INTO SELECT to migrate billions of rows caused table‑wide locking and payment failures, explains the underlying locking behavior, and presents a solution using proper indexing and FORCE INDEX to avoid full‑table scans.
This document outlines comprehensive MySQL database design standards and SQL writing guidelines, covering naming conventions, character sets, table and column specifications, index design, partitioning, data types, transaction handling, and prohibited statements to ensure stable, performant, and maintainable production databases.
This article presents practical MySQL performance tricks, covering bulk data insertion with DISABLE/ENABLE KEYS, loading data in primary‑key order, multi‑value INSERTs, INSERT DELAYED, separating index and data files, GROUP BY sorting elimination, index‑driven ORDER BY, OR‑condition indexing, replacing sub‑queries with JOINs, and using SQL hints such as USE, IGNORE, and FORCE INDEX.
This article explains why and how to create prefix indexes for string fields in MySQL, compares full‑column and prefix indexes with concrete examples, shows how to pick an optimal prefix length using cardinality statistics, and discusses alternative solutions such as reverse storage and hash fields.
This article explains InnoDB's 16KB page structure, row formats, record metadata, primary key strategies, and how B+Tree indexes—including clustered, secondary, and covering indexes—organize and retrieve data, with practical SQL examples and performance considerations.
This article introduces PostgreSQL, the powerful open‑source object‑relational database, highlights its rising popularity in DB‑Engines rankings, compares its advanced features and performance advantages over MySQL, and outlines many of its unique capabilities such as rich data types, sophisticated indexing, and robust replication.
This article provides a thorough overview of MySQL performance tuning, covering proper table design according to the three normal forms, various index types and their creation, partitioning strategies, read/write separation, storage engine selection, and practical SQL tips for improving query efficiency.
The article explains why a SELECT COUNT(*) on a 5‑million‑row InnoDB table was extremely slow, analyzes the execution plan, demonstrates that the query used the primary (clustered) index, and shows how creating a small secondary index reduces the runtime from dozens of seconds to under a second.
This article explains how MySQL stores data using pages, page directories, and multi‑page structures, demonstrates why MySQL silently sorts rows on insert, and shows how these mechanisms combine into the B+‑tree index that optimizes query performance and supports clustering, non‑clustering, and composite indexes.
This article presents a comprehensive collection of MySQL performance‑tuning techniques—including proper use of EXPLAIN, limiting IN lists, selecting explicit columns, using LIMIT 1, avoiding costly ORDER BY RAND(), choosing between IN and EXISTS, preferring UNION ALL, applying full‑text indexes, and optimizing joins, pagination, and index usage—to help developers write faster, more efficient queries.
This article explains how to analyze and tune MySQL join queries with EXPLAIN, addressing issues like using temporary tables and filesort, and provides practical techniques—including STRAIGHT_JOIN and index strategies—to achieve sub‑second response times on tables with millions of rows.
This article explains why deep pagination in Elasticsearch leads to high CPU usage, introduces core concepts such as indices, shards, nodes, and documents, and details the distributed CRUD operations, query and fetch phases, and segment management that affect search performance.
MongoDB is a versatile NoSQL database that combines relational‑like querying with a flexible BSON document model, offering high performance, easy deployment, rich indexing, replication, and cloud‑scale storage, while supporting multiple programming languages and operating systems.
This article explains various MongoDB pagination strategies—including skip/limit, range queries, and sorting—covers performance monitoring with profiling, details index creation and design principles, and describes how to interpret explain plans for efficient query optimization.
This article presents a thorough guide to improving MySQL performance for large single tables, covering field choices, indexing best practices, query tuning, engine selection, system parameters, hardware upgrades, read/write separation, caching layers, partitioning, vertical and horizontal sharding, and compatible scalable database alternatives.
This article provides a comprehensive overview of Elasticsearch architecture and presents practical index and search optimization techniques, configuration recommendations, stress‑testing methods, and monitoring tools to improve cluster performance and reliability.
This comprehensive guide explains MySQL's layered architecture, storage engine plugins, index structures, transaction isolation levels, locking mechanisms, performance tuning techniques, partitioning, sharding, and replication, providing practical insights for developers and DBAs to optimize and scale MySQL deployments.
This article explains what a search engine is, describes its core components—indexing and search modules—detailing the workflow from content acquisition to result rendering, and provides an in‑depth overview of Elasticsearch, including its architecture, clusters, shards, replicas, mappings, and basic configuration.
This article systematically explores Git’s underlying architecture, detailing the lifecycle of objects, core data structures, packfile formats, indexing mechanisms, and the algorithms used for object retrieval, while providing practical command examples and visual diagrams to demystify Git’s storage and retrieval models.
This article provides a comprehensive overview of Elasticsearch's internal architecture, explaining how data flows from memory buffers to Lucene segments, the role of refresh and translog for durability, segment merging strategies, shard routing, replica consistency, allocation controls, hot‑cold data separation, and cluster discovery settings.
This article shares practical MySQL performance optimization techniques, covering table size limits, connection settings, data type choices, index design, query rewriting, pagination, and batch processing, with code examples and actionable guidelines to improve query speed and scalability.
This guide presents comprehensive MySQL best‑practice standards covering object naming, basic schema design, field definitions, index design, common index column recommendations, covering indexes, SET usage, SQL development rules, and safe operational behaviors to improve performance, maintainability, and security.
This comprehensive guide outlines MySQL database naming conventions, basic design rules, field specifications, index design principles, common index recommendations, and best practices for SQL development and operational behavior to ensure performance, maintainability, and security.
An engineer’s attempt to migrate millions of orders using INSERT INTO SELECT caused massive table locking, payment failures, and system alerts, but by analyzing the locking behavior and adding an index on the timestamp column, the migration succeeded efficiently, illustrating the importance of proper indexing for large‑scale data moves.
This article provides a detailed comparison of Solr 7.3 and Elasticsearch 7.4, covering their shared Lucene foundation, distributed architecture, feature sets, configuration approaches, storage options, shard management, and ecosystem differences to help readers choose the right search platform for their needs.
This article explains MySQL's large object data types—TEXT, BLOB, and JSON—covering their storage formats, performance implications, indexing rules, partition restrictions, relevant server parameters, and practical examples of creating tables, inserting data, and querying JSON values.
This article explores Youzan's enterprise search middle platform, detailing the challenges of siloed architectures, the concept of cognitive folding, comprehensive index design, write/read mechanisms, configuration-driven routing, monitoring, and practical implementations that enable scalable, reusable search capabilities across diverse business domains.
This article presents thirty actionable SQL performance recommendations—including selecting specific columns, using LIMIT, avoiding OR and functions in WHERE clauses, proper indexing, batch inserts, and query planning—each illustrated with clear examples and explanations to help developers write faster, more efficient queries.
This article introduces Elasticsearch by explaining its core concepts such as indices, types, documents, mappings, and Query DSL, demonstrates common use cases, and provides step‑by‑step instructions for creating, updating, viewing, and deleting indices and documents using RESTful APIs, curl commands, and Docker‑compose deployment.
This article describes ByteDance’s extensive improvements to the RocksDB storage engine—including LazyBuffer, Adaptive Map‑based lazy compaction, KV separation, adaptive multi‑index support, extreme compression techniques, and hardware acceleration—to reduce amplification, improve performance, and lower costs for large‑scale database workloads.
This article explains Python tuples as immutable ordered collections, covering their creation, indexing (including negative indices), slicing with optional stride, concatenation and replication operators, and useful built‑in functions such as len, max, and min, while contrasting tuples with lists.
This article provides a comprehensive overview of HBase, covering its LSM‑Tree based storage engine, key‑value data model, column‑family storage design, indexing mechanisms, major advantages and drawbacks, and typical scenarios where HBase excels for massive, high‑throughput data workloads.
This guide presents comprehensive MySQL best‑practice rules covering object naming conventions, fundamental schema design, field type selection, index creation strategies, and SQL development habits to improve performance, maintainability, and reliability of production databases.
This article presents a thorough set of MySQL best‑practice guidelines covering object naming conventions, basic table design, column definitions, index creation, index usage policies, SQL coding standards, and operational behaviors to improve performance, maintainability, and data integrity.
This guide introduces Elasticsearch fundamentals—including its core concepts, main features, and essential terminology—followed by step‑by‑step Docker setup and practical API commands for creating, indexing, searching, and managing documents in a single‑node cluster.
This article explains how MySQL X API (available since MySQL 5.7) can replace NoSQL databases by enabling the X protocol, showing configuration steps, equivalent insert examples, and detailed CRUD, indexing, and transaction operations with code snippets.
This article provides a comprehensive overview of ElasticSearch, covering its distributed architecture, fundamental components such as nodes, shards, and indices, as well as practical guidance on index design, mapping, bulk operations, query processing, scroll searches, alias management, and performance tuning tips.
This article explains the architecture of Elasticsearch and Lucene, outlines common performance bottlenecks, and provides concrete indexing and query optimization techniques—including shard routing, refresh intervals, doc values, and hardware considerations—to achieve sub‑second query responses on billions of records.
This article explains MySQL's three‑layer logical architecture, the end‑to‑end query execution flow—including client/server protocol, query cache, parsing, cost‑based optimization, execution engine, and result delivery—followed by practical performance‑tuning advice on schema design, data types, index creation, and specific query optimizations such as COUNT(), JOINs, LIMIT pagination, and UNION handling.
This article explains how a data platform handling billions of daily records can be optimized for cross‑month queries and sub‑second response times by tuning Elasticsearch indexing, shard routing, Lucene structures, and hardware configurations.
A MySQL 5.6 database with three large tables suffered a 30,000‑second query when searching for students scoring 100 in a specific subject; by analyzing the execution plan, adding indexes on filter columns, and rewriting the query as a join, execution time dropped to under a second.
This guide explains InfluxDB’s schemaless data types, best practices for designing tag‑and‑field schemas, and how to choose and configure its in‑memory or TSI indexes, including key parameters such as max‑series‑per‑database and max‑values‑per‑tag for optimal performance.
