This article presents a comprehensive guide to optimizing Elasticsearch for massive datasets, covering Lucene fundamentals, index and shard architecture, practical performance‑tuning techniques, and real‑world case studies that achieve sub‑second query responses on billions of records.
This article explains why a MySQL UNIQUE index does not prevent duplicate rows when indexed columns contain NULL, explores the complications of logical‑delete tables, and presents practical solutions such as incrementing delete status, adding timestamps, delete‑id keys, hash fields, and proper batch inserts.
This article walks through a MySQL scenario where a nested sub‑query runs for hours, demonstrates how adding single‑column and multi‑column indexes, rewriting the query as a join, and analyzing execution plans can reduce execution time from thousands of seconds to a few milliseconds.
A backend engineer shares a step‑by‑step case study of reducing a batch‑score query API from 20 seconds to under 500 ms by analyzing the problem, adding a composite index, introducing multithreaded CompletableFuture calls, and limiting batch size with pagination and thread‑pool tuning.
This article explains Bilibili's lake‑warehouse integrated architecture, describing how Iceberg, MagnuS, Trino, and Alluxio are used to achieve flexible data storage, high‑performance query acceleration, and automated indexing through Z‑Order, Hilbert curve, Bloom filter, and advanced BitMap techniques.
This article explains why MySQL queries can become sluggish, covering the full query execution flow, profiling techniques, index pitfalls, connection‑pool limits, buffer‑pool sizing, and additional performance tricks, while providing concrete commands and code examples for each optimization step.
This article walks through a MySQL 5.6 scenario where a subquery that originally took over eight hours is accelerated by orders of magnitude through strategic index creation, query rewriting, and join optimization, illustrating the impact of single‑column, composite, and covering indexes on execution plans.
This article walks through a MySQL scenario with large tables, demonstrates why a sub‑query that took over eight hours runs in just a second after adding appropriate single‑column and composite indexes, and explains how to read and interpret execution plans for further tuning.
This article enumerates twelve typical reasons why MySQL queries become slow—such as missing or ineffective indexes, deep pagination, massive tables, excessive joins, large IN lists, dirty pages, file‑based ORDER BY, and hardware limits—and provides concrete SQL examples and step‑by‑step optimization techniques to resolve each issue.
This article explores the various reasons MySQL queries become slow—including index misuse, insufficient connection pools, small InnoDB buffer pools, and query cache limitations—while providing practical solutions such as optimizing indexes, adjusting max_connections, enlarging buffer pools, and configuring connection pools for better performance.
This article walks through the complete lifecycle of a MySQL SELECT statement, from establishing the TCP connection and authentication, through query cache, parsing, optimization, and execution, detailing how the server and storage engine collaborate and explaining concepts like index usage, connection handling, and memory management.
This article recounts a production incident caused by a MySQL query with a huge OFFSET, explains why large offsets lead to full‑table scans and severe latency, and presents three practical solutions—including index‑covering subqueries, start‑position redefinition, and offset throttling—along with data‑generation scripts and performance test results.
This article explains typical MySQL index failure cases such as left‑most matching truncation, implicit conversion, IN + ORDER BY, range queries, leading wildcard, OR conditions, function usage, NOT IN/!=, low selectivity, and index fragmentation, and provides practical optimization strategies including index redesign, covering indexes, and pagination improvements.
This article shares hands‑on experience with MySQL 5.7 slow‑query problems, explaining common causes, proper index design, pitfalls that invalidate indexes, efficient SQL writing, deep‑pagination avoidance, and how to use EXPLAIN to verify that queries leverage the right indexes.
This article walks through ten typical scenarios where MySQL indexes fail—covering left‑most prefix violations, SELECT *, calculations, functions, type mismatches, LIKE patterns, column comparisons, OR, NOT IN/NOT EXISTS, and ORDER BY—showing why each occurs and how to keep indexes effective.
This comprehensive guide walks through MySQL performance fundamentals, real‑world testing results, slow‑query detection, index impact, pagination pitfalls, count optimizations, sharding techniques, and step‑by‑step migration plans for large tables, equipping engineers with actionable tuning insights.
This article explains why SQL queries become slow in MySQL, describes the server architecture, details how to use the EXPLAIN statement to interpret execution plans, and outlines how to read and optimize key fields such as type, key, and extra for better performance.
This article explains why MySQL queries become slow, how index design and common pitfalls affect performance, introduces MDL locks and large‑table strategies, and then compares Elasticsearch and HBase as complementary solutions for fast search and massive data storage.
This article demonstrates how to create and use a sample employees table, explains composite index structures, and provides ten concrete MySQL index best‑practice guidelines—covering full‑value matches, leftmost‑prefix rules, avoiding functions, range conditions, covering indexes, inequality, NULL checks, leading wildcards, type conversions, and OR usage—each illustrated with EXPLAIN output and code examples.
This article explains MySQL index fundamentals, the B+Tree structure, various index types, the concept of covering indexes to avoid row look‑ups, the left‑most prefix rule, and presents three real‑world optimization cases that illustrate how to design effective composite indexes, reduce index size, and improve query performance while avoiding costly table scans and sorts.
This article explains MySQL index fundamentals, B+‑tree implementation, the left‑most prefix rule, back‑to‑table issues, and provides three practical optimization cases with code examples to help developers design efficient composite indexes and avoid performance pitfalls.
This article explains MySQL index fundamentals, different index structures, the concept of covering indexes and the left‑most prefix rule, and presents three real‑world optimization cases that illustrate how to design effective composite and partial indexes to avoid table scans and improve query performance.
This article describes how a top architect optimized Elasticsearch for handling billions of records, covering Lucene fundamentals, index and shard design, DocValues, query performance tuning, bulk indexing strategies, hardware considerations, and testing methods to achieve sub‑second query responses across multi‑year data ranges.
This article presents a comprehensive guide on optimizing Elasticsearch for large‑scale data platforms, covering Lucene fundamentals, index and shard architecture, doc‑values usage, routing strategies, practical performance‑tuning techniques, and real‑world testing results to achieve sub‑second query responses on billions of records.
This article explains what constitutes a MySQL slow query, why they cause failures, defines quantitative metrics such as micro‑average and macro‑average to assess severity, outlines target goals, presents concrete optimization examples, and shares operational practices for ongoing slow‑SQL governance.
This article explains why a query that uses an index can still be slow, demonstrates how to diagnose index usage and row scans with EXPLAIN and the slow query log, and presents optimization techniques such as composite indexes, index condition pushdown, and virtual columns.
This article explains how MySQL processes ORDER BY clauses, illustrates the execution steps with a full‑field index, discusses the impact of sort_buffer and max_length_for_sort_data, and shows how adding composite and covering indexes can eliminate filesort and improve query performance.
This article analyzes common causes of slow MySQL queries such as index misuse, MDL locks, and large‑table bottlenecks, then presents practical solutions like proper indexing, sharding, read/write splitting, and evaluates when to complement MySQL with ElasticSearch or HBase for better performance.
This article explains why using SELECT * in MySQL queries hurts performance, explores the underlying reasons such as extra data transfer, I/O overhead, and loss of covering index optimization, and provides practical guidance on index usage and best practices.
The article explores why MySQL pagination with large offsets is slow, explains index structures such as clustered and secondary indexes, describes logical query operators, and presents two practical optimization strategies to improve query performance.
This article explains MySQL's ORDER BY execution process, comparing full-field sorting with rowid sorting, detailing how sort_buffer, temporary files, and index usage affect performance, and showing how composite and covering indexes can eliminate sorting altogether.
This article explains why defining columns as NOT NULL in MySQL improves query optimization, reduces index complexity and storage overhead, and avoids unexpected behavior in aggregates and calculations, while also covering default values, row formats, and practical testing results.
This article explores why MySQL queries with large LIMIT‑OFFSET pagination become painfully slow, explains the underlying B+‑tree index mechanics and MySQL's logical operator layers, and presents two practical solutions—key‑based pagination and index‑covering queries—to dramatically improve performance.
This article presents a comprehensive set of mandatory and recommended MySQL database standards covering table creation, SQL writing, and index design, explaining the rationale behind each rule to improve performance, maintainability, and consistency across projects.
A developer discovers that massive OFFSET values in MySQL pagination cause full‑table scans and severe latency, then reproduces the issue with simulated data, benchmarks the slowdown, and presents three practical solutions—including index‑covering subqueries, cursor‑based pagination, and offset limits—to restore query performance.
This article explains the architecture and core concepts of Elasticsearch and Lucene, outlines the requirements for cross‑month and high‑speed queries on massive datasets, and provides detailed index and search performance tuning techniques—including bulk writes, shard routing, doc‑values management, and pagination strategies—to achieve sub‑second response times on billions of records.
This article examines a slow MySQL query that scans millions of rows, explains why the optimizer chose a low‑selectivity index, demonstrates how forcing the correct index dramatically improves performance, and shows how to create a covering composite index to resolve the issue permanently.
The article explains why SELECT * slows down MySQL queries by increasing parsing cost, network traffic, and preventing index covering, and demonstrates how explicit column selection and well‑designed composite indexes can dramatically reduce I/O and improve query speed.
This article walks through a MySQL 5.6 case study where a nested sub‑query for finding students scoring 100 in a specific subject runs for hours, then demonstrates how adding single‑column and composite indexes, rewriting the query as a join, and analyzing execution plans can shrink the runtime from tens of thousands of seconds to under a second.
This article walks through a MySQL 5.6 scenario where a query that originally took over 30,000 seconds is optimized using proper indexing, sub‑query to join conversion, and composite indexes, demonstrating dramatic performance gains and offering practical tips for future query tuning.
This article explains common misconceptions about index selectivity and filtering, demonstrates how composite indexes can avoid slow queries, and shows why order‑by may still trigger filesort, providing practical tips and SQL examples for better MySQL index design.
This article explains the severe impact of MySQL slow queries, shows how to enable and configure the slow query log, demonstrates using EXPLAIN to analyze query execution, and provides concrete optimization techniques for indexes, pagination, and common pitfalls.
This guide explains the conditions under which MySQL can use indexes for range scans, LIKE patterns, BETWEEN clauses, and sorting, detailing key_len calculation, index key vs. filter vs. table filter, and practical examples to demystify index utilization.
This article presents twenty practical rules for optimizing MySQL indexes—including when to use or avoid indexes, how to write queries that can leverage them, the importance of index selectivity, prefix indexes, covering indexes, and common pitfalls—to help developers write faster, more efficient SQL statements.
This article outlines twenty practical principles for optimizing MySQL indexes, covering topics such as LIKE wildcards, UNION/IN/OR usage, negative conditions, composite index prefix rules, range queries, function avoidance, covering indexes, prefix indexes, pagination strategies, and common misconceptions, all illustrated with SQL examples and performance tips.
The article explains why using NULL columns in MySQL can lead to extra storage, index complications, query errors, and performance degradation, and provides concrete examples and best‑practice recommendations to prefer NOT NULL constraints whenever possible.
This article explores key database design decisions—including the benefits and drawbacks of auto‑increment primary keys, the trade‑offs of redundant columns, optimal index counts, and when to choose VARCHAR versus CHAR—offering practical guidelines to improve performance, storage efficiency, and maintainability.
This article explains common MySQL performance problems such as missing indexes and lack of pagination, and provides practical guidance on data size limits, concurrency settings, query time targets, table design, index types, and a variety of SQL optimizations with concrete code examples.
This article walks through diagnosing severe SLA alerts caused by slow queries on a massive MySQL table, analyzes query patterns with pt‑query‑digest, redesigns indexes, applies online DDL and pt‑osc for index changes, and demonstrates batch‑delete techniques that dramatically reduce execution time and replication lag.
This guide outlines comprehensive MySQL best practices covering naming conventions, table engine and charset choices, field type selection, index design principles, common index column recommendations, ordering of composite indexes, avoidance of redundant and foreign‑key constraints, SQL development rules, and operational guidelines for large‑scale data handling.
This article details the evolution and optimization of Ctrip’s vacation search engine, covering business challenges, indexing redesign, data collection pipelines, write‑path improvements, compression techniques, query performance enhancements, deployment strategies, and the resulting gains in storage, latency, and stability.
This article explains why using SELECT * in MySQL queries reduces performance, detailing increased parsing cost, network overhead, I/O for large columns, and loss of covering index optimization, and also expands on index concepts such as composite indexes and their impact on query efficiency.
Although many developers hear the advice to avoid using SELECT * in SQL, this article delves into the underlying reasons—such as increased parsing cost, unnecessary data transfer, extra I/O, and loss of covering-index optimization—while also explaining index concepts and best practices for MySQL performance.
This article examines how MySQL estimates execution costs for queries, explains why COUNT(*) can be optimal, demonstrates index selection with real‑world examples, and shows how optimizer_trace can reveal mismatches between estimated and actual performance.
This article presents a comprehensive guide to MySQL performance improvement, covering maximum data volume, connection limits, query latency targets, practical implementation principles, optimal table design, index classification and tuning, as well as detailed SQL optimizations such as batch processing, operator rewriting, and pagination techniques.
This article analyzes a MySQL master‑slave instance with a massive arrival_record table, identifies slow‑query causes using pt‑query‑digest and tcpdump, proposes index redesign, demonstrates backup/restore with mydumper, compares online DDL and pt‑osc, and presents batch‑delete strategies that dramatically reduce SLA‑triggering latency.
The article examines why MySQL queries become slow, detailing system‑level issues such as network latency, I/O bottlenecks, and CPU settings, alongside database‑level problems like missing indexes, type conversion, poor execution plans, lock contention, skewed data, and inefficient SQL patterns, and offers optimization strategies.
This article explains why using indexes does not guarantee fast queries, explores the relationship between index usage and slow‑query logs, and presents practical MySQL optimization techniques such as improving index selectivity, reducing table‑lookup costs, and leveraging virtual columns.
This article explains MySQL index design principles, demonstrates the execution steps of a range query on a sample table, and discusses common index optimizations such as covering indexes, the left‑most prefix rule, index condition push‑down, implicit type conversion, as well as why the optimizer may choose a wrong index and how to correct it.
A production MySQL query on a table with five million rows took over 30 seconds, and after reproducing the issue, examining execution plans, trying various index and query tweaks, discovering that SQLyog adds a hidden LIMIT, and finally forcing the correct index, the runtime dropped to under a second, illustrating the importance of proper index usage and tool‑specific quirks.
This article documents a MySQL slow‑query case where a 5‑million‑row table took over 30 seconds to run a GROUP BY query, explores several ineffective optimization attempts, reveals a client‑side limit issue, and finally solves the problem by forcing the correct index, cutting the runtime to under 0.2 seconds.
This article examines a MySQL instance that hit 100% CPU at 9 am on 2019‑01‑11, identifies the offending ORDER BY query through slow‑log and EXPLAIN analysis, and demonstrates how adding a composite index and correcting implicit type conversions reduced execution time from over 10 seconds to milliseconds.
An in‑depth guide walks through Elasticsearch’s underlying Lucene architecture, explains shard routing and DocValues, then presents concrete index‑ and search‑performance tweaks—bulk writes, refresh intervals, memory allocation, SSD usage, field mapping, pagination strategies—and shows benchmark results that reduce query latency to seconds for billions of records.
This article explains how MySQL composite (multi‑column) indexes work, the left‑most prefix rule, demonstrates queries where the index is used or ignored, and lists common situations that cause index failure along with practical optimization tips.
This article presents a comprehensive guide to optimizing Elasticsearch for massive data volumes, covering Lucene fundamentals, index and shard design, practical performance‑tuning techniques, and real‑world testing results that enable cross‑month queries and sub‑second response times.
This article analyzes the performance bottlenecks of a MongoDB‑based CMDB under high write concurrency, explains index structures and storage, demonstrates how to identify and eliminate full‑table scans and redundant multikey indexes, and shows the resulting reduction in slow‑log volume and CPU usage.
The author describes a sudden 30,000‑second slowdown in a simple MySQL join query, details systematic diagnostics—including network, server load, and query testing—and ultimately discovers a missing index on the user_info table, restoring it to bring response times back to sub‑second levels.
This article investigates Alibaba's rule against joining more than three tables by designing MySQL and Oracle experiments that generate massive student‑teacher‑course data, run multi‑table join queries, compare indexed versus non‑indexed scenarios, and reveal the performance limits that drive the guideline.
This article walks through a real‑world MySQL scenario, showing how a slow sub‑query can be accelerated by adding single‑column indexes, converting the query to a join, and finally creating a composite index, with performance numbers and execution‑plan analysis to illustrate each optimization step.
This article analyzes why MySQL LIMIT pagination becomes increasingly slow with larger offsets, presents six optimization techniques—including using primary keys, covering indexes, prepared statements, subqueries, and composite indexes—and provides experimental results that demonstrate dramatic performance improvements.
This article examines why MySQL LIMIT pagination becomes slower as the offset grows, presents six practical methods—including direct LIMIT, primary‑key indexing, ordered index scans, prepared statements, sub‑queries, and joins—provides benchmark results on large tables, and distills actionable guidelines for designing covering and composite indexes to achieve fast, stable pagination.
This article walks through MySQL's ORDER BY execution, detailing the full‑field sorting process, when MySQL switches to row‑id sorting due to large row sizes, and how composite and covering indexes can optimize or eliminate the sorting step.
This article demonstrates how to dramatically improve MySQL query performance by analyzing execution plans, adding appropriate single‑column and composite indexes, converting subqueries to EXISTS or JOINs, and leveraging index coverage and ordering, with detailed examples and timing results.
This guide outlines comprehensive MySQL standards covering object naming conventions, basic design rules, field type choices, index creation strategies, and SQL development practices to improve performance, maintainability, and security across database projects.
This article discusses MySQL table design choices, partitioning methods, index best practices, common causes of slow queries with optimization tips, useful diagnostic tools, and recommended actions when accidental operations or bugs affect data.
This article introduces SQLiteLint, a runtime analysis tool that automatically checks SQLite usage in mobile apps for index problems, redundant indexes, SELECT *, AUTOINCREMENT, missing prepared statements, and without‑rowid opportunities, enabling developers to catch performance issues before release.
This article explains how MySQL decides whether an index can be used for range queries, LIKE patterns, and sorting, detailing key_len calculation, index key vs. filter vs. table filter concepts, and practical examples with BETWEEN and ORDER BY.
The Youzan search system uses middleware‑driven Elasticsearch optimizations—segment merging, larger buffers, routing, and rollover—to cut index files and document scans, splits large indices into business‑specific or hot‑cold sub‑indices, and adds asynchronous cross‑datacenter replication with soft‑delete versioning for high‑availability and scalable performance.
This article explains InnoDB's transaction and index principles, covering ACID properties, transaction logs, MVCC, locking mechanisms, and B+ tree index structures to help backend developers understand how InnoDB ensures data consistency and high concurrency.
This article explains MySQL index fundamentals, demonstrates performance issues with slow queries, and walks through practical optimization cases using EXPLAIN, covering index creation, covering indexes, and composite indexes, and best practices for when to add or avoid indexes.
This article recounts a blockchain finance company's rapid user growth, the decision to move MySQL from Docker Swarm to Alibaba Cloud RDS, the ensuing CPU overload caused by missing indexes, and the step‑by‑step troubleshooting and optimization that restored system stability.
This article recounts a real‑world MySQL migration from Docker Swarm containers to an Alibaba Cloud RDS instance, detailing the original architecture, migration steps, backup scripts, a critical index‑missing outage, and the practical indexing strategies used to restore performance.
This article reviews common MySQL pitfalls—including engine choice, table partitioning, index design, slow‑query causes and optimizations, diagnostic tools, and how to recover from accidental data loss—offering practical guidance for developers to improve performance and reliability.
This article explains how QA engineers can identify and analyze MySQL slow query logs, configure slow query settings, and apply index optimization principles to improve database performance, providing practical commands, guidelines, and tool recommendations.
This article explains how to quickly locate and fix online MySQL problems such as partial update failures, InnoDB deadlocks, and lock‑wait timeouts by analyzing server logs, understanding InnoDB row‑lock behavior, optimizing indexes, adjusting transaction settings, and using a custom log‑collection script.
This article walks through a real‑world monitoring project that must store over 400 million rows per day in SQL Server 2012, detailing the performance bottlenecks, bulk‑copy tuning, schema redesign, partitioning strategies, index removal and creation, and query‑optimisation techniques that finally achieve sub‑second query times and meet strict latency requirements.
This article walks through practical MySQL optimization techniques—including configuration file tuning, key InnoDB parameters, effective index design, and the use of Percona and MySQL Tuner tools—to help you speed up queries and eliminate performance bottlenecks in production environments.
This article explains how MySQL processes ORDER BY, compares conventional, optimized, and priority‑queue sorting algorithms, shows how to leverage indexes, and provides practical tuning tips such as adjusting max_length_for_sort_data and sort_buffer_size to avoid costly temporary files and random I/O.
The article investigates why a seemingly indexed MySQL DELETE query still performs a full‑table scan, analyzes execution plans and statistics, demonstrates forcing the index, and finally rewrites the query to limit the date range, achieving a much faster operation.
A recent order‑fulfillment outage was traced to a MySQL query that used the primary‑key index because an ORDER BY id ASC LIMIT 350 forced a full scan, but tweaking the ORDER BY or removing it reduced execution time from 20 seconds to under one second.
This article explains common MongoDB connection problems, authentication mechanisms, read‑preference tuning, index types and creation, query‑plan analysis, pagination techniques, and array handling, offering practical tips to improve performance and reliability for developers.
This article explains eight typical situations in which Oracle SQL statements cannot use indexes, illustrates each case with concrete examples and step‑by‑step rewrites, and offers practical solutions—including predicate adjustments, function indexes, and query‑transformation fixes—to improve database performance.
This article explains eight common reasons why Oracle SQL statements fail to use indexes, provides concrete rewrite techniques, shows how optimizer bugs can block index usage, and outlines practical steps for enforcing SQL development standards through automated audit tools.
This article explains how to identify and analyze MySQL slow queries using tools like SHOW VARIABLES, EXPLAIN, mysqldumpslow, and profiling, and provides practical recommendations for configuring server variables, optimizing indexes, and improving query performance through systematic testing and best‑practice SQL design.
The article recounts a performance bottleneck in a Windows service that processed only about 12 records per second, analyzes why a simple SELECT TOP 1 query without proper indexes caused 89 ms latency, and demonstrates step‑by‑step how adding appropriate clustered and non‑clustered indexes reduced the execution time to virtually zero, while also discussing broader indexing strategies and common LEFT JOIN pitfalls.
This article demonstrates how PostgreSQL's Partial Index feature can be used together with BTREE and BRIN indexes to dramatically reduce storage while maintaining high query performance on both low‑frequency and high‑frequency data in a heavily skewed dataset.
This article explains what bookmark (key) lookups are in SQL Server, shows how they appear in execution plans, and presents three practical indexing techniques—clustered indexes, covering indexes with INCLUDE, and index intersection—to remove the extra I/O overhead.
This guide explains how to choose optimal MySQL data types, use enums, handle dates, and design efficient indexes—including B‑tree, prefix, and covering indexes—while also covering storage engine differences, maintenance commands, and normalization strategies to boost database performance.
This article reports a hands‑on experiment comparing single‑column and composite covered indexes in MySQL, measuring their storage overhead and insert latency on a million‑row table, and reflects on the importance of data‑driven reasoning versus intuition.
This article walks through practical Oracle SQL optimization methods—explaining execution plans, indexes, statistics, hints, and partitioning—using concrete examples and step‑by‑step case studies that show how to dramatically reduce query runtimes and CPU load.
