This article explains what NoSQL means, contrasts it with relational databases, and guides readers through building a simple Python key‑value NoSQL database, covering schema basics, querying, indexing, command parsing, and the trade‑offs between flexibility and query performance.
This article examines the performance drawbacks of MySQL's LIMIT pagination on large tables, presents six practical query methods—including direct LIMIT, primary‑key indexing, index‑based ordering, prepared statements, covering indexes, and sub‑query/join techniques—provides extensive benchmark results, and offers concrete indexing recommendations to achieve fast, stable pagination even with millions of rows.
This article reproduces a MongoDB issue where queries using a hashed sharding key return no results and a unique compound index appears to fail, then explains the root cause—index entries exceeding the 1024‑byte limit and the failIndexKeyTooLong parameter—providing step‑by‑step commands, configuration changes, and practical recommendations.
This article explains MySQL's logical architecture, query execution flow, client‑server protocol, query cache behavior, parsing and optimization stages, cost‑based optimizer, execution engine, and provides practical performance‑tuning advice such as schema design, data‑type choices, index creation, B‑Tree fundamentals, covering indexes, and handling COUNT, JOIN, LIMIT, and UNION queries.
This article provides a comprehensive overview of database system design, covering the historical evolution, classification of relational and NoSQL databases, common architectures, consistency models, indexing techniques, storage formats, compression, and practical considerations for selecting and optimizing database solutions.
This comprehensive guide explains Elasticsearch’s role in handling massive data, covering its architecture, core concepts like shards and replicas, indexing and search processes, performance considerations, common pitfalls, and practical interview insights for developers and engineers.
This article compiles 50 practical SQL performance optimization techniques—from indexing strategies and query rewriting to avoiding full table scans, using temporary tables, proper data types, and efficient backup methods—helping developers write faster, more scalable database queries.
This comprehensive guide explains Elasticsearch’s fundamentals, including its distributed architecture, indexing process, shard and replica mechanisms, query execution, near‑real‑time search, segment management, and practical optimization techniques, providing developers and engineers with the knowledge needed to design, operate, and troubleshoot large‑scale search clusters.
This comprehensive guide explores MySQL’s origins, key characteristics, logical architecture, storage engines, transaction handling, concurrency control, and detailed indexing strategies, offering practical insights and best‑practice recommendations for developers and database administrators seeking to master MySQL performance and reliability.
This article explains why and when to scan product code for vulnerabilities, describes static source‑code and binary scanning methods, introduces deep code‑search techniques and a real‑time Sphinx‑based indexing architecture, and shows how these practices can significantly raise overall product quality.
This article explains why setting MySQL columns to NOT NULL improves query optimization, reduces index complexity, saves storage space, and prevents unexpected behavior in calculations, while also detailing default values, handling of NULL in aggregates, and the internal row format differences between NULLable and NOT NULL columns.
This article provides a thorough overview of MySQL fundamentals—including schema design, storage engines, transaction mechanisms, logging, indexing, and performance tuning—along with practical SQL examples, code snippets, and optimization strategies for both hardware and database configuration.
This article explains why MySQL 5.6 may return duplicate rows on the second page when using ORDER BY with LIMIT, analyzes the optimizer’s priority‑queue behavior, and provides practical solutions such as indexing, proper pagination understanding, and handling of sorting edge cases.
The article explains why developers insert the clause "WHERE 1=1" in dynamically generated SQL statements, shows example Java code, discusses how it prevents syntax errors when concatenating AND conditions, and offers advice on adding selective filters and indexes to avoid full table scans.
This article investigates why MySQL recommends auto_increment primary keys over UUID or non‑sequential snowflake IDs by building three tables, running insert‑and‑query benchmarks with Spring Boot's JdbcTemplate, analyzing index structures, and summarizing the performance trade‑offs and practical drawbacks of each key strategy.
This article provides a comprehensive overview of Elasticsearch, covering its distributed architecture, fundamental concepts such as nodes, shards, and indices, as well as practical guidance on index design, bulk writing, query‑fetch workflow, scroll queries, aliases, and basic optimization tips.
This article describes how to improve the performance of a massive MySQL 5.6 user‑log table by redesigning schema, applying proper indexes, using partitioning, considering table sharding, and evaluating upgrade paths to compatible cloud or big‑data databases, with concrete SQL examples and cost analysis.
This article explains how Python lists can contain mixed data types, demonstrates accessing elements with positive and negative indexes, shows slicing techniques, and illustrates various ways to add, remove, replace, and manipulate list items using built‑in functions and methods.
This article investigates why MySQL recommends auto_increment over UUID or non‑sequential snowflake IDs by creating three tables, running insert and query benchmarks with Spring Boot and JdbcTemplate, analyzing index structures, and summarizing the performance trade‑offs and security considerations of each primary‑key strategy.
An engineer’s costly mistake using MySQL’s INSERT INTO SELECT for nightly data migration led to out‑of‑memory crashes, full‑table scans, and payment record loss, prompting a deep dive into locking behavior, transaction isolation, and how proper indexing can safely rescue large‑scale inserts.
An in-depth case study describes how to dramatically improve the performance of a massive MES reporting query—reducing execution from over half an hour to seconds—by analyzing inefficient SQL, adding proper indexes, avoiding full table scans, redesigning with stored procedures, temporary tables, and selective use of NOLOCK.
This article compiles fifty‑two practical SQL tuning strategies—including proper indexing, query rewriting, avoiding full table scans, using temporary tables, managing locks, and choosing the right storage engine—to help developers dramatically improve database query performance and scalability.
This article analyzes common causes of slow MySQL queries—especially index misuse—offers practical indexing techniques, explains MDL locks and large‑table bottlenecks, and then compares ElasticSearch and HBase as complementary solutions for high‑performance search and storage.
This article compiles 52 practical SQL performance‑tuning strategies—including indexing rules, query‑writing shortcuts, join optimizations, lock handling, temporary tables, storage‑engine choices, data‑type recommendations, backup tips, and useful code examples—to help developers dramatically improve query speed and overall database efficiency.
Testing shows that omitting single quotes around a string primary key in MySQL queries can cause a full table scan and increase execution time by about 100‑fold, while quoting the value enables index usage and dramatically improves performance, a difference that disappears when the column is an INT.
This article explores why MySQL queries become slow, delves into index pitfalls and optimization techniques, then compares ElasticSearch and HBase architectures, offering practical guidance on when to use each technology and how to combine them for high‑performance data retrieval.
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.
