0 views collected around this technical thread.
This article explains the materialized view pattern, describing why pre‑populated views are created when source data formats hinder efficient queries, how they improve performance as disposable caches, and the key considerations, trade‑offs, and scenarios for adopting this approach.
This article explains how MySQL's InnoDB engine implements full‑text search using inverted indexes, shows how to create and use full‑text indexes with various query modes, demonstrates Boolean operators and query expansion, and covers index removal techniques.
This article presents a series of MySQL interview questions and answers that illustrate typical situations where indexes become ineffective—such as leading wildcards in LIKE, NOT IN, NOT EXISTS, functions, inequality operators, OR conditions, mismatched ORDER BY, large result sets, and explains how to analyze execution plans and use FORCE INDEX to improve performance.
ByteDance Data Platform’s recent breakthroughs in database research—spanning query‑level Bayesian tuning, adaptive stream‑processing parallelism, and learned cardinality estimation—were highlighted by two papers accepted at VLDB 2025 and ICDE 2025, showcasing significant performance gains and real‑world deployments.
This article provides a thorough tutorial on SQL fundamentals, including statement order, core clause rules, templates for SELECT/INSERT/UPDATE/DELETE, practical multi‑table query cases, join and subquery techniques, common pitfalls, index optimization strategies, experimental performance data, and a structured learning path for mastering database development.
To improve MySQL performance, first identify slow queries via logs, then examine their EXPLAIN plans and profiling data, understand optimizer traces, and apply fixes such as proper left‑most indexing, avoiding implicit conversions, large offsets, unsuitable IN/ORDER BY patterns, range‑blocking, NOT‑IN predicates, and consider alternative storage or pagination strategies for complex or massive datasets.
This article explains the three MySQL EXPLAIN output formats—TRADITIONAL, JSON, and TREE—demonstrates how the JSON format provides richer cost and data‑read information, compares query costs of sample SQL statements, and offers practical optimization suggestions for poorly performing joins.
VictoriaMetrics employs a modular, horizontally scalable architecture composed of vmagent, vminsert, vmstorage, vmselect, and vmalert, each handling data collection, ingestion, storage, querying, and alerting, while leveraging consistent hashing, LSM‑tree storage, TSID indexing, and multi‑tenant isolation to efficiently manage large‑scale time‑series workloads.
The article explains MySQL's Index Merge optimization, detailing its three types—Intersection, Union, and Sort‑Union—when it is beneficial, how to design indexes and rewrite queries for better performance, and the associated costs and limitations.
Index Condition Pushdown (ICP) is a MySQL query‑optimization technique that pushes eligible WHERE predicates to the storage engine so that index entries can filter rows early, reducing row fetches and server‑engine communication.
This article explains what the rows column in MySQL EXPLAIN means, demonstrates three situations—when a small rows value indicates good performance, when it does not, and when rows is misleading—using concrete SQL examples, force‑index tricks, and multi‑table join analyses to show why rows alone cannot reliably judge query efficiency.
This article explains the most frequent MySQL EXPLAIN EXTRA hints—Using filesort, Using temporary, and Using join buffer—detailing when they appear, how to interpret them, and practical optimization strategies illustrated with concrete SQL examples and execution‑plan outputs.
This article explains the underlying principles of the Spark SQL optimizer and presents three classic optimization paradigms—push‑down optimization, operator elimination/merging, and expression elimination/replacement—illustrating each with concrete rule implementations and code examples.
This article explains why the SQL keywords IN and NOT IN often lead to poor performance and unexpected results, demonstrates the problems with large tables and NULL values, and recommends using EXISTS, NOT EXISTS, or JOIN alternatives for more reliable and efficient queries.
This article explains how to read MySQL's EXPLAIN EXTRA column, covering SEMI JOIN hints such as FirstMatch, Start/End temporary, LooseScan, and the Range‑checked‑for‑each‑record optimization, with concrete examples and command‑line output to illustrate each case.
This article explores advanced SQL query optimization techniques for large datasets, covering sorting methods, GROUP BY improvements, pagination strategies, covering indexes, and prefix index considerations, with practical examples, EXPLAIN analyses, and recommendations to reduce filesort usage and enhance performance.
This article details how JD’s BIP procurement system tackled massive data volume and complex query performance issues by reducing slow SQL, partitioning “in‑stock” orders, migrating large datasets to Elasticsearch, implementing dynamic query routing, and establishing robust monitoring, resulting in a 92% data reduction and smoother operations.
The article compares MySQL and PostgreSQL multi‑table join capabilities, explains why MySQL’s nested‑loop joins can be less efficient for complex queries, and discusses how decomposing joins into separate single‑table queries or moving logic to the service layer can improve performance, scalability, and caching.
This article explains MySQL EXPLAIN type column, detailing the inefficiencies of type=ALL, how adding ORDER BY can change it to index, and demonstrates the use of INDEX_MERGE for UNION and INTERSECT queries, including practical examples and cost comparisons to improve query performance.
This article explains why indexes are essential for query performance, lists the most common scenarios for creating single‑column, unique, composite, and prefix indexes, provides concrete SQL examples, and shows how to verify index usage with EXPLAIN.