This article demonstrates how to generate, insert, and query millions of rows in a MySQL 5.7 table, measures pagination performance under varying offsets and result sizes, and presents several optimization techniques—including sub‑queries, indexed look‑ups, and column selection—to dramatically reduce query latency.
Apache Doris 2.0‑beta, released on July 3, 2023, introduces a modern Cascades‑based query optimizer, a data‑driven pipeline execution engine, fine‑grained workload groups, enhanced memory management, partial‑column updates, compute nodes, cold‑hot tiering and cross‑cluster replication, delivering up to tenfold speedups and significant cost reductions for real‑time analytics.
WeChat’s multi‑dimensional monitoring platform faced severe query latency and I/O bottlenecks, so the team analyzed user behavior and Druid architecture, then introduced sub‑query splitting, Redis caching, and segment size reductions, achieving over 85% cache hit rate and reducing average query time to around 100 ms.
This article details how the WeChat multi‑dimensional monitoring platform reduced average query latency from over 1000 ms to around 100 ms by analyzing user query patterns, redesigning the Druid data layer, splitting sub‑queries, introducing Redis caching, and employing sub‑dimension tables, achieving cache hit rates above 85%.
This article details iQIYI's adoption of Iceberg for its data lake, covering the OLAP architecture, reasons for a data lake, Iceberg's table format advantages over Hive, platform construction, streaming ingestion, query and performance optimizations, real‑world business deployments, and future plans.
The article details how WeChat's multi‑dimensional metric monitoring platform, which handles billions of data points per minute, reduced average query latency from over 1000 ms to around 140 ms and achieved over 85% cache hit rate by analyzing query behavior, redesigning the data layer architecture, splitting queries into sub‑queries, adding Redis caching, and introducing sub‑dimension tables.
This article introduces Huawei Cloud's real-time data lake query optimizations using Apache Hudi, covering Hudi's query capabilities, clustering and MDT optimizations, various index types (Min‑max, Lucene, bitmap), caching strategies, and future plans for performance improvements.
This guide explains MySQL indexes—what they are, their data structures, advantages and drawbacks, when to create them, various index types, how to interpret EXPLAIN output, common pitfalls that cause index loss, and practical optimization techniques for queries, joins, ordering, grouping, and slow‑query logging.
This article explains the definition, data structures, advantages, disadvantages, and appropriate usage scenarios of MySQL indexes, details primary, unique, single‑column and composite index types, analyzes common performance bottlenecks with EXPLAIN, demonstrates index creation, invalidation cases, query and join optimizations, and shows how to configure and use the slow‑query log.
This article explains the data query fundamentals of YouShu BI charts, introduces intelligent caching design, describes query merging and various optimization techniques—including partition filters, value acceleration, and SQL generation—and outlines performance diagnosis methods to improve BI chart responsiveness.
This article explains what indexes are, when they should be applied, and provides step‑by‑step MySQL commands—including single‑column, composite, and index‑hint examples—to improve query performance on large datasets.
This article examines the inherent difficulties of SQL when handling multi‑step, ordered, and object‑referencing queries, illustrates them with practical examples, and introduces the Structured Process Language (SPL) as a more expressive alternative for data processing tasks.
This article provides a comprehensive guide to MySQL indexing, covering index types, data structures, clustering vs. non‑clustering, hash and B+Tree indexes, covering indexes, index push‑down, index merge strategies, cost‑based index selection, common pitfalls, and practical indexing principles with illustrative examples and SQL snippets.
The article explains why MySQL 8.x may ignore a primary‑key index and perform a full table scan for an IN subquery, describes the optimizer’s cost‑model reasoning, and shows how rewriting the query with an INNER JOIN restores index usage and dramatically improves performance.
The article explains that high metric cardinality in Prometheus causes long query times and timeouts, and demonstrates how using recording rules to pre‑compute aggregates dramatically reduces cardinality and latency, while recommending scrape interval tuning and metric design best practices to keep charts responsive.
This article explains how to obtain distinct code entries together with their cdate and ctotal fields in MySQL, covering the use of DISTINCT, GROUP BY, handling ONLY_FULL_GROUP_BY mode, and alternative solutions such as GROUP_CONCAT with DISTINCT.
This article provides a comprehensive 15‑question guide on MySQL indexes, covering their definition, various types across data‑structure, physical and logical dimensions, situations where indexes fail or are unsuitable, B+‑tree advantages, search process, covering indexes, left‑most prefix, index push‑down, adding indexes to huge tables, using EXPLAIN to verify index usage, hash versus B+‑tree differences, pros and cons, and the distinction between clustered and non‑clustered indexes.
This article explains why correlated subqueries are a performance bottleneck, introduces the Apply (Correlated Join) operator and a systematic set of transformation rules that push Apply down and replace subqueries with efficient joins, covering scalar, existential, and quantified subqueries, aggregation handling, and set operations, all illustrated with TPC‑H examples.
This comprehensive guide explains what MySQL indexes are, details various index types—including B+‑tree, hash, full‑text, and spatial—covers when indexes become ineffective, shows how to design, use, and troubleshoot them, and provides practical steps for large‑scale index management.
This article explains why correlated subqueries are costly, introduces the Apply (correlated join) operator, and presents a series of systematic transformation rules that convert scalar, existential, and aggregate subqueries into efficient join‑based plans while preserving SQL semantics.
This guide explains the importance of query performance optimization for database products and engineers, outlines latency and throughput goals, shows how to locate bottlenecks with observability tools and Linux profilers, and provides practical high‑level and low‑level optimization techniques along with testing best practices.
This article explains MySQL's index‑merge optimization, detailing how the EXPLAIN output indicates its use, the three underlying algorithms (intersect, union, sort‑union), practical query examples, configuration flags, and step‑by‑step guidance for testing with sample tables and indexes.
This article explains why developers often include the redundant condition “WHERE 1=1” in SQL statements, demonstrating through performance tests that it has no effect on execution time, and describing its practical use for simplifying the addition of further conditions in both static and dynamic queries.
The article explains how null or empty query values caused MongoDB to ignore the intended index, leading to massive slow queries and service timeouts, and details the step‑by‑step investigation, plan‑cache invalidation, and the corrective addition of a compound index that restored normal performance.
This article walks through common SQL development scenarios on ODPS, examining why naïve UNION and COUNT DISTINCT can be slow, how to rewrite queries with GROUP BY, UNION ALL, JSON aggregation, and map‑join techniques, and shows the resulting execution‑plan improvements with concrete code and performance numbers.
This article explains how Apache Doris extends its real‑time OLAP capabilities to support Lakehouse architectures, covering unified metadata, query acceleration, elastic compute, performance benchmarks, and future roadmap for richer data‑source integration and resource isolation.
When upgrading MySQL 5.7 tables that use utf8 or utf8mb3 to MySQL 8.0, mismatched character sets and collations can cause index loss and inefficient joins, but converting all tables to utf8mb4 and adjusting the driver table can restore optimal query performance, as demonstrated with detailed examples and profiling results.
This article presents a comprehensive design and performance‑optimization study for extracting up to 100 000 records from a pool of tens of millions, comparing multithreaded ClickHouse pagination, Elasticsearch scroll‑scan, ES + HBase, and RediSearch + RedisJSON solutions, and provides practical recommendations based on measured latency and throughput.
This article presents a comprehensive set of MySQL performance guidelines, including avoiding SELECT *, preferring specific columns, eliminating OR in WHERE clauses, using numeric types, choosing VARCHAR over CHAR, applying proper JOINs, leveraging LIMIT, UNION ALL, composite indexes, and other practical tips to improve query efficiency and resource usage.
This article explains how Presto and Alluxio work together to query Iceberg tables, describes their architectures, deployment options, best‑practice recommendations such as using Iceberg native catalogs and local caches, and outlines future research directions for improving CPU usage and off‑heap caching.
This article presents a comprehensive technical overview of Bilibili's Presto cluster architecture, the challenges of query performance on Hadoop, and the systematic optimizations—including Alluxio integration, local cache mechanisms, multi‑active coordinators, label‑based scheduling, and real‑time penalties—that together improve availability, stability, and latency for large‑scale analytics workloads.
This article explains the evolution of data storage, the fundamentals of computer storage devices, how database indexes function like a book's table of contents, the role of binary search, the benefits and drawbacks of indexes, clustered versus non‑clustered indexes, and common SQL optimization techniques.
This article describes how DataTester, Volcano Engine's A/B testing platform, achieved over four‑fold query speedup by applying five technical optimizations—including pre‑aggregation, join reduction, GroupBy redesign, AU‑metric caching, and asynchronous query handling—targeting both data construction and execution layers.
This guide walks through a collection of practical MySQL functions and commands—including GROUP_CONCAT, CHAR_LENGTH, LOCATE, REPLACE, NOW, INSERT…SELECT, INSERT…IGNORE, SELECT FOR UPDATE, ON DUPLICATE KEY UPDATE, SHOW CREATE TABLE, CREATE TABLE … SELECT, EXPLAIN, SHOW PROCESSLIST and mysqldump—providing clear syntax, example queries, result screenshots and key usage notes to help developers write more efficient and reliable SQL.
This article presents a comprehensive overview of TDSQL‑PG's HTAP capabilities, detailing the evolution of its storage, compute, transaction management, hybrid row‑column storage, cost‑based optimizer, vectorized execution, and resource isolation strategies for handling mixed OLTP and OLAP workloads.
This article presents a comprehensive analysis and multiple design alternatives—including multithreaded ClickHouse pagination, Elasticsearch scroll‑scan, ES+HBase hybrid, and RediSearch+RedisJSON—to efficiently filter, sort, and de‑duplicate up to 100,000 records from a pool of tens of millions, with detailed performance comparisons and code examples.
This guide walks through fourteen practical MySQL techniques—from aggregating rows with group_concat and measuring string length with char_length, to locating substrings, replacing text, retrieving timestamps, bulk inserting, handling duplicates, using pessimistic locks, inspecting tables, explaining queries, monitoring processes, and exporting data with mysqldump.
This talk presents JD Retail’s big‑data OLAP solution, covering the massive, variable and complex traffic data challenges, the custom data‑ingestion and versioned update tools, ClickHouse query‑architecture upgrades, optimization techniques, and future plans for multi‑cluster querying and pre‑computation.
This article examines why encrypted data hinders fuzzy queries and compares three categories of solutions—naïve, conventional, and advanced—detailing their implementation steps, performance trade‑offs, storage costs, and practical suitability for real‑world systems.
This guide walks through MySQL performance tuning, covering the architectural tuning pyramid, query optimization techniques, slow‑query logging, detailed EXPLAIN execution‑plan analysis, and high‑performance indexing strategies to help you diagnose and accelerate database workloads.
CDW PG’s optimizer determines the most efficient join order for multi‑table OLAP queries by combining bottom‑up dynamic programming for smaller joins with a genetic algorithm for larger ones, while jointly selecting scan paths, join algorithms, and data‑distribution strategies to minimize execution cost.
This article explains MySQL's client‑server architecture and walks through the three main stages—connection handling, query parsing and optimization, and storage engine selection—detailing protocols, authentication, cache behavior, optimizer mechanics, and engine characteristics with practical commands and examples.
The article explains why adding a SQL parser to big‑data platforms such as Hive, Spark, Flink or Kafka simplifies development, compares ANTLR and Apache Calcite implementations, shows code examples, and concludes that Calcite’s lower learning curve and greater flexibility make it the preferred choice for production‑grade SQL layers.
The article analyzes why MySQL LIMIT pagination becomes slower as the offset grows, demonstrates the performance impact with sample queries on a 500,000‑row table, and presents three optimization strategies—using ordered primary keys, subqueries, and join‑based pagination—to reduce full‑table scans and improve query speed.
This guide presents thirty practical SQL Server optimization techniques—including proper indexing, avoiding full table scans, rewriting predicates, and minimizing temporary objects—to help developers write faster, more efficient queries and improve overall database performance.
This article explains how Huawei Cloud leverages Apache Hudi and HetuEngine (Presto) to improve point‑query performance on Lakehouse architectures through data layout optimization, file‑skipping techniques, metadata tables, and extensive benchmark results demonstrating multi‑fold speedups.
Bilibili’s new lake‑house platform, built on Trino and Iceberg, replaces Hive‑based pipelines by ingesting logs and DB data into Iceberg tables, applying advanced sorting, Z‑order/Hilbert clustering, bitmap and bloom indexes, virtual join columns and pre‑aggregation, enabling 70 000 daily queries on 2 PB with average scans of 2 GB and sub‑2‑second response times.
This article introduces Apache Calcite as a modular data‑management framework, explains its architecture and SQL processing pipeline—from parsing and validation to relational‑algebra conversion, optimization, and execution—and demonstrates a complete CSV‑based query example with code snippets.
This article describes how AutoHome tackled the complexity of managing multiple relational, NoSQL, and Hive data stores by adopting openLooKeng for unified, cross‑source SQL queries, outlines its key features such as ANSI‑SQL support, diverse connectors, and query optimizations, and details the custom enhancements made to the Apache Kylin connector to better serve their commercial data analysis workloads.
This guide explores five essential dimensions of MySQL performance tuning—including connection configuration, architectural choices such as caching and read‑write splitting, optimizer settings, storage engine selection, and schema design—providing concrete commands, formulas, and best‑practice recommendations to accelerate query execution and reduce load.
This article examines why encrypted data resists fuzzy queries, categorizes three implementation strategies—from naïve to advanced—evaluates their security, performance, and storage trade‑offs, and recommends the most balanced approach for real‑world applications.
This article examines why a MySQL query that uses an IN subquery on a massive users table becomes extremely slow, analyzes the execution plan revealing materialized temporary tables and semi‑join optimization, and demonstrates how disabling the optimizer or rewriting the query restores index usage and dramatically improves performance.
This article explains the complete MySQL execution flow, covering the connector's duties, permission tables, caching behavior, parsing of SQL statements, optimizer decision‑making, executor interaction with storage engines, processlist states, logical query order, and practical tips for ordering WHERE‑clause conditions.
This article presents a comprehensive list of practical SQL query optimization techniques, emphasizing how to avoid full table scans by using proper indexing, rewriting WHERE clauses, avoiding certain operators, functions, and expressions, and providing example code snippets to improve database performance.
Youzan replaced ClickHouse and Druid with Apache Doris, refined its vectorized engine by eliminating deserialization overhead in the merge‑aggregation phase, achieving roughly a 30 % query‑time boost, and validated compatibility through SQL rewriting and traffic replay, while planning further SIMD‑based optimizations and broader adoption.
The article explains why a MySQL query with a large LIMIT offset becomes extremely slow, demonstrates the underlying I/O behavior, and shows how rewriting the query with a sub‑query that fetches primary keys first can cut execution time from seconds to milliseconds while also reducing buffer‑pool pollution.
This article examines a business requirement to filter up to 100,000 items from a pool of tens of millions, presenting and evaluating four technical solutions—multithreaded ClickHouse pagination, Elasticsearch scroll‑scan, an ES‑HBase hybrid, and RediSearch + RedisJSON—along with performance data and implementation details.
This article provides a comprehensive overview of Doris's architecture—including its FE and BE components, metadata management, data organization, execution planning—and details its major features such as adaptive join aggregation, vectorized execution, materialized views, and Elasticsearch integration, supplemented with example DDL and query code.
This article explains the complete MySQL execution pipeline—from client connection, authentication, and permission checks through caching, parsing, optimization, and execution—plus the logical order of SQL clauses, illustrated with diagrams to help developers optimize and comprehend database queries.
MySQL creates two kinds of temporary tables—external and internal—based on CREATE TEMPORARY TABLE statements or internal query processing, and uses the MEMORY engine by default unless large objects or the big_tables variable force MyISAM or InnoDB, with detailed guidance on scenarios, storage engine selection, conversion thresholds, indexing, and performance tuning.
This article explains how Apache Iceberg’s DataSkipping technique can lose efficiency when many filter columns are used, and presents a data‑organization optimization using space‑filling curves and Z‑Order to improve query I/O, details the OPTIMIZE implementation, and shares performance benchmark results and future plans.
The article details ByteDance's large‑scale deployment of the Presto OLAP engine for ad‑hoc, BI, and near‑real‑time analytics, describing its architecture, multi‑coordinator high‑availability design, routing gateway, adaptive cancel, history server, materialized‑view support, Hudi connector integration, and how these innovations improve performance, stability, and operational efficiency.
The article presents SuperSQL, a high‑performance big‑data SQL middleware that enables cross‑engine and cross‑data‑center query processing, detailing its architecture, metadata management, cost‑based optimization, operator push‑down, distributed execution, performance benchmarks, and future roadmap within modern data‑intensive environments.
This article introduces graph databases, explains the subgraph‑matching problem, compares it with relational databases, discusses its computational complexity, and surveys backtracking and multi‑way join algorithms, worst‑case optimal joins, set‑intersection SIMD acceleration, and the gStore system’s research contributions.
This article presents a comprehensive technical walkthrough of Sensor Data's multi‑dimensional analysis platform, covering product architecture, an Impala‑based real‑time query engine, query performance tuning, resource‑estimation strategies, and future plans, with concrete diagrams, test results, and community contributions.
This article explains why SQL performance degrades as data grows, outlines a step‑by‑step optimization workflow—including slow‑query detection, EXPLAIN analysis, profiling, optimizer tracing, and corrective actions—while detailing type and extra fields, index usage, and practical scenarios such as large pagination, IN‑order‑by, range queries, and complex joins.
This article explains how to retrieve sales statistics for multiple business units and their selected products in MySQL, compares loop‑based, OR‑concatenated, mixed‑filter, and SQL‑92 row‑comparison approaches, and shows why the row‑comparison technique was ultimately chosen for its index usage and simplicity.
This article explains how Prometheus, a data‑driven monitoring system, leverages a time‑series database (TSDB) to handle massive metric volumes, perform efficient queries, and enable powerful calculations such as recording rules for pre‑computed results.
This article examines common reasons why MySQL queries become slow, including index misuse, insufficient connections, and small InnoDB buffer pools, and provides practical solutions such as profiling, using EXPLAIN, adjusting connection limits, and tuning buffer pool size.
This article explains why MySQL queries can become sluggish beyond missing indexes, covering the full query execution flow, profiling tools, index pitfalls, connection‑pool limits, InnoDB buffer‑pool sizing, and practical steps to diagnose and accelerate database performance.
This article explains MySQL index fundamentals—including B‑tree, B+‑tree, hash, and ordered array structures—covers primary versus secondary indexes, describes index maintenance such as page splits and merges, examines the InnoDB change buffer, and discusses optimizer choices and cases where indexes become ineffective.
Using a large OFFSET in MySQL's LIMIT clause forces the server to scan hundreds of thousands of index and data pages, causing excessive random I/O, whereas rewriting the query with a sub‑select and join reduces page accesses dramatically, as demonstrated through buffer‑pool measurements.
This article explains why data lakes are essential for today’s analytics, outlines the three main user demands, defines data lakes, compares rule‑based and cost‑based optimizers, explores record‑oriented versus block‑oriented processing, and details StarRocks’ frontend‑backend architecture and benchmark results.
The article demonstrates that MySQL queries using LIMIT with a large offset cause heavy random I/O by scanning many rows, and shows how rewriting the query with a sub‑query that selects only primary keys dramatically reduces execution time from seconds to milliseconds.
This article analyzes why a MySQL query with a large LIMIT offset can take seconds to run, demonstrates the underlying index and buffer‑pool behavior, and shows how rewriting the query with a sub‑select of primary keys reduces execution time from over 16 seconds to under half a second.
The article explains why large‑offset LIMIT queries on a MySQL table with millions of rows are slow, demonstrates how rewriting the query to use a subquery that first selects primary‑key IDs dramatically reduces execution time, and validates the improvement by measuring buffer‑pool page usage.
The article explains how using a large OFFSET in MySQL pagination forces the server to read and discard many rows, causing severe performance degradation, and demonstrates that rewriting the query to first fetch primary‑key IDs via a subquery dramatically reduces I/O and execution time, with buffer‑pool experiments confirming the improvement.
This article explains MySQL's experimental EXPLAIN FORMAT=TREE feature and the later EXPLAIN ANALYZE extension, showing how they present execution plans, estimated costs, actual runtime metrics, and how to interpret the tree output for query optimization.
The article analyzes four fundamental limitations of SQL—lack of stepwise execution, incomplete set semantics, missing ordered operations, and no object reference mechanism—illustrates them with real queries, and introduces SPL as a more intuitive language that overcomes these issues.
Using LIMIT with a large offset in MySQL can cause severe performance degradation due to excessive random I/O, but by rewriting the query to first fetch primary keys in a subquery and then joining, execution time can drop from seconds to milliseconds, as demonstrated with real data.
This article introduces Presto, an open‑source MPP SQL engine, explains its coordinator‑worker architecture and connector model, and provides detailed storage, query, and join optimization strategies—including in‑memory parallelism, dynamic plan compilation, and practical SQL code examples—to achieve low‑latency, high‑performance analytics on big data.
The article explains how shifting complex filtering, validation, and join operations from the database to the application layer reduces scarce database resource usage, improves scalability, and enables more flexible resource allocation for modern web applications.
This article explains MySQL's architecture, the server and storage‑engine layers, query execution phases, status states, query cache behavior, the optimizer's parsing and planning steps, EXPLAIN output fields, the cost model, and why the optimizer selects index_author_id instead of index_title for a given query.
This article explains what an execution plan is, how to use the EXPLAIN keyword in MySQL 5.7, interprets each column of the EXPLAIN output—including id, select_type, table, type, possible_keys, key, key_len, rows, and Extra—provides detailed examples of different query scenarios, and shows how optimizer settings like derived_merge affect the plan.
This article explains MySQL index fundamentals, classifies indexes by data structure, storage engine, field characteristics and column count, compares B+Tree with B‑Tree, hash and red‑black trees, and demonstrates practical creation, usage, and optimization techniques with SQL examples and diagrams.
In a production environment, a pagination export feature caused duplicate and missing records due to MySQL’s nondeterministic ordering when multiple rows share the same ORDER BY value, and the article explains the root cause, official documentation, and a reliable solution using additional sorting columns.
This article explains what database indexes are, classifies them by data structure, storage engine, field characteristics and column count, compares B+Tree with B‑Tree, hash and red‑black trees, and demonstrates how InnoDB and MyISAM handle clustered and secondary indexes with practical SQL examples.
This article explains Cobar’s sharding middleware and presents an optimization technique for handling ORDER BY and LIMIT queries across multiple databases, detailing the original approach, its inefficiencies with deep pagination, the step‑by‑step improved algorithm, performance analysis, limitations, and practical applicability.
This article examines why a MySQL composite index on (first_name, last_name) initially fails to be used for ordering, demonstrates how adding a range predicate reduces the scanned row count, and explains the threshold at which MySQL switches from filesort to index‑based retrieval.
This article explains why MySQL LIMIT deep pagination becomes slow with large offsets and presents four practical optimization methods—including subqueries, delayed INNER JOIN, tag‑recording, and BETWEEN…AND—demonstrated with real‑world production examples.
This article explains how MySQL's cost‑based optimizer decides between using an index and performing a full table scan, demonstrates the behavior with a large test table, shows optimizer trace details, and clarifies why index access can sometimes be more expensive than scanning the whole table.
The article demonstrates how a large OFFSET in a MySQL LIMIT query forces MySQL to scan millions of index rows and data pages, causing severe slowdown, and shows that rewriting the query with an inner‑join sub‑select dramatically reduces I/O and buffer‑pool usage, confirming the performance difference through experiments.
When a MySQL query uses LIMIT with a large offset on a table of millions of rows, the database must scan hundreds of thousands of index entries and corresponding clustered rows, causing massive random I/O; rewriting the query with a sub‑query join reduces I/O dramatically, cutting execution time from over a minute to under a second while also preventing buffer‑pool pollution.
This article explains which components (user processes, IO wait, system interrupts) consume CPU in MySQL, shows their impact on performance, and provides practical strategies to reduce CPU usage through IO optimization, query tuning, data modeling, caching, and hardware upgrades.
This article explains MySQL's three‑layer logical architecture, the end‑to‑end query execution flow, and the inner workings of the optimizer, cache, and storage engine, then offers practical schema, index, and specific‑query tuning techniques to boost database performance.
This article explains MySQL’s logical architecture, query processing steps, and the principles behind query optimization, covering topics such as client‑server protocol, query cache, parsing, cost‑based optimizer, execution engine, and practical index design strategies to improve performance.
The article explains why a simple GROUP BY query in MySQL 5.6 returns the earliest rows instead of the most recent ones, demonstrates how to correctly obtain the latest record per name using sub‑queries or window functions, and discusses the impact of the ONLY_FULL_GROUP_BY mode in MySQL 5.7.