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.
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.
This guide compiles practical MySQL performance techniques—including proper indexing, avoiding costly operators, rewriting OR conditions, using EXISTS, limiting SELECT *, managing temporary tables, configuring threads, choosing storage engines, and optimizing data types—to dramatically reduce full‑table scans and improve query speed.
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 explores MySQL's Hash Join algorithms, comparing Basic, On-disk, Grace, and Hybrid variants, while detailing rule-based optimizer decisions, memory management, disk spilling mechanisms, and outer join execution strategies through source code analysis.
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 how Tencent's financial big‑data platform uses Impala, detailing its overall architecture, concurrency mechanisms, cost‑based join optimization, storage layer design, and practical performance‑tuning experiences to achieve fast, interactive analytics.
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.
This article explains how MySQL may rewrite a LEFT JOIN as an INNER JOIN during optimization when the WHERE clause filters the joined table, demonstrates the behavior with sample class and student tables, and presents test cases confirming the optimizer's decision.
This article examines common causes of slow queries in MySQL, presents practical indexing techniques, and extends the discussion to performance considerations in ElasticSearch and HBase, offering combined solutions such as sharding, read/write splitting, and using ES as a search layer for large‑scale systems.
This article explains why correlated subqueries are a performance bottleneck, introduces the Apply (Correlated Join) operator, classifies subquery types, shows how to transform them into efficient joins, and discusses rule‑based push‑down techniques for Project, Filter, Aggregate, and Set operations.
This article explains MySQL's B-Tree index structure, storage layout, query patterns that benefit from it, common limitations such as left-most prefix rules, and answers why B-Tree is preferred over other structures and why auto-increment primary keys are recommended.
This article explains the various classifications of OLAP data warehouses—including MOLAP, ROLAP, HOLAP, and HTAP—based on data volume and modeling, reviews common open‑source ROLAP products, and details performance‑boosting techniques such as MPP architecture, cost‑based optimization, vectorized execution, and storage optimizations.
A unified semantic layer for data development solves metric‑change ripple effects, developer burden, and large‑scale query performance problems by offering consistent metric definitions, multi‑view access, concise auto‑generated SQL, instant propagation of updates, and engine‑driven optimal query selection, thereby bridging business and engineering and cutting maintenance effort.
This article explains how Elasticsearch processes queries across shards, identifies two key performance bottlenecks—query computation time and segment file I/O—and offers practical optimization strategies such as simplifying query logic, maximizing OS file cache usage, increasing memory, reducing stored data, and applying hot‑cold data separation.
This article explains how LEFT JOIN works in SQL, illustrates why duplicate rows can appear when joining tables, and provides practical techniques such as DISTINCT, GROUP BY, MAX, and IN subqueries to ensure a one‑to‑one result set while also covering RIGHT JOIN, INNER JOIN, update and delete operations, and the concept of Cartesian products.
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 examines why full-table scans become slow on large MySQL tables, demonstrates various pagination techniques—including simple LIMIT, sub‑query, ID‑range, and IN‑clause methods—provides benchmark results for different record counts and offsets, and offers practical tips to dramatically speed up paginated queries.
This article explains how MySQL maintains primary and composite (non‑primary) indexes using B+ trees, outlines the essential equality‑matching, left‑most‑prefix, and range‑scan principles, and shows how to combine them with ORDER BY, LIMIT, and GROUP BY for optimal query performance.
This article explains how MySQL processes a GROUP BY query on a user view table, detailing the creation and use of memory and disk temporary tables, the MEM_ROOT allocation mechanism, the execution stages revealed by EXPLAIN, and how adding a composite index can eliminate temporary tables and filesort for better performance.
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 explains the three‑layer architecture of MySQL, how an execution plan is generated and interpreted, and provides a detailed walkthrough of the columns displayed by the EXPLAIN statement to help developers optimize their SQL queries.
This article demystifies MySQL’s query execution by exploring its logical architecture, client‑server protocol, query cache, parsing, optimization, and execution engine, then offers practical indexing and performance‑tuning techniques—including B‑Tree fundamentals, covering indexes, and pagination tricks—to help developers write faster, more efficient SQL.
This article explains the purpose and proper use of the SQL HAVING clause, demonstrates how it works with GROUP BY to filter aggregated groups, shows examples for finding groups with specific counts, missing IDs, mode, median, and non‑NULL values, and clarifies best practices for query performance and semantics.
This article explains the concept of MySQL index cardinality, how it influences index selectivity, factors that affect cardinality values, methods to view them, and demonstrates with practical examples how different data distributions and query patterns impact the optimizer's execution plans.
This article explains various situations—such as column-to-column comparisons, NULL values, NOT conditions, wildcard LIKE patterns, functions on indexed columns, low‑cardinality leading columns in composite indexes, implicit datatype conversions, arithmetic predicates, virtual and invisible indexes—that can cause an otherwise usable index to be bypassed by the optimizer, leading to full‑table scans.
This article explains what correlated subqueries are, why they challenge database engines, and how they can be transformed into ordinary SQL using decorrelation, push‑down rules, result reuse, and window‑function tricks, illustrated with TPCH examples and code snippets.
This article reviews MySQL composite index syntax, explains the mandatory condition that the leftmost column must appear in the query filter, analyzes seven representative SQL statements that cannot benefit from a composite index, and provides practical refactoring suggestions such as adding single‑column indexes or creating more appropriate composite indexes.
This article explains why a small table should drive a large table in SQL queries, demonstrates data preparation, compares IN and EXISTS performance when the small table is either the inner or outer query, and concludes with practical guidelines for choosing between IN and EXISTS.
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 explains the meaning of the Extra column in MySQL EXPLAIN output, covering how "Using index", "Using index condition", and "Using where" appear, when index coverage and index condition push‑down occur, and provides multiple practical examples with code.
This article explains why using ORDER BY with LIMIT for pagination in MySQL can yield inconsistent results when the ordering column contains duplicate values, describes MySQL's limit optimization behavior, and provides a reliable solution by adding a secondary unique sort key.
This article explains why correlated subqueries are a performance bottleneck, introduces the Apply (Correlated Join) operator, and presents a comprehensive set of transformation rules—including basic unnesting, Project/Filter push‑down, Aggregate handling, and Set‑operation rewrites—to convert subqueries into efficient join plans for modern SQL engines.
This article explains MySQL 8.0 window function FRAME clause syntax, differentiates ROWS and RANGE units, and demonstrates various frame boundaries such as CURRENT ROW, UNBOUNDED PRECEDING/FOLLOWING, and expression‑based preceding/following with concrete query examples.
This article explains why MySQL queries that use indexes can still become slow, examines full‑index scans, index selectivity, back‑table costs, and shows how features like index condition pushdown and virtual columns can dramatically improve performance.
When you combine ORDER BY with LIMIT in MySQL, the result set may appear in a different order than expected, especially if the ordered column contains duplicate values, and understanding the optimizer's behavior and adding deterministic columns can resolve this issue.
This article explains why SQL performance is critical for fast‑response web services, presents database‑agnostic optimization techniques—such as preferring EXISTS over IN, using JOINs, avoiding unnecessary ORDER BY, leveraging UNION ALL, and applying indexes—to dramatically reduce query latency and resource consumption.
This article presents five practical rules—return only needed rows, use proper indexes, avoid subqueries, replace OFFSET pagination with key‑based limits, and understand SQL's logical execution order—to help developers write efficient, high‑performance SQL queries across major relational databases.
This article explains why SQL query optimization matters and presents five practical rules—return only needed rows, use the right indexes, avoid subqueries, replace OFFSET‑based pagination, and master the logical execution order—to help developers write faster, more efficient database queries across major relational systems.
This article presents five practical rules for optimizing SQL queries—including selecting only needed columns, ensuring proper indexes, avoiding subqueries, using keyset pagination instead of OFFSET, and understanding the logical execution order—along with concrete examples and execution‑plan analysis for major relational databases.
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 article explains how MySQL decides whether an index can be used for range scans, LIKE patterns, and ORDER BY operations, detailing key_len calculation, index key vs. filter vs. table filter distinctions, and providing concrete examples and code snippets for better query optimization.
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 why using indexes in MySQL does not always prevent slow queries, analyzes full‑index scans, index selectivity, back‑table lookups, virtual columns, and provides practical optimization techniques to reduce scanned rows and improve performance.
This article presents five practical rules for writing efficient SQL queries—including selecting only needed rows, using proper indexes, avoiding subqueries, replacing OFFSET pagination, and understanding the logical execution order—supported by examples, execution plans, and performance tips for various relational databases.
This article explains MySQL’s logical architecture, query processing steps, caching mechanisms, index structures, and provides practical performance‑tuning advice—including schema design, index creation, query rewriting, and pagination—helping readers grasp the underlying principles and apply effective optimizations in real‑world workloads.
This article explains how using a large LIMIT offset on a 9.5‑million‑row MySQL table causes massive I/O and slow queries, and demonstrates a sub‑query‑based rewrite that fetches only primary keys before joining, dramatically reducing execution time while also showing buffer‑pool effects.
This article explains the two forms of SQL CASE expressions, demonstrates how to use CASE for concise data aggregation and conditional updates, and provides practical examples with customer and order tables to simplify queries and avoid pitfalls.
This comprehensive guide answers the most frequent MySQL interview questions, covering index structures, transaction isolation levels, table design best practices, storage engine differences, query optimization techniques, and miscellaneous topics such as data types, binlog formats, and sharding strategies.
This article demonstrates how to dramatically improve MySQL LIMIT offset query performance by moving the filter into a subquery that selects only primary keys, then joining to retrieve full rows, and validates the approach with buffer pool statistics and execution time measurements.
This article presents five practical rules for writing efficient SQL queries—including using precise WHERE clauses, creating appropriate indexes, avoiding costly subqueries, replacing OFFSET‑based pagination with key‑based limits, and understanding the logical execution order of SQL clauses—to help developers improve database performance across major relational systems.
The article introduces Kylin 4.0's major architectural upgrades, explains why previous tuning practices no longer apply, and provides a detailed performance‑tuning guide and video covering Cube build and query optimizations, along with additional reference resources.
This article explains why indexes dramatically improve MySQL query speed on large tables, outlines common reasons for slow SQL, shows how to use EXPLAIN and SHOW PROFILE to diagnose problems, and provides step‑by‑step examples of creating and evaluating indexes for optimal performance.
This article examines why a MySQL 5.7 query that finds tables lacking primary or unique keys runs extremely slowly, demonstrates an experiment reproducing the issue, analyzes the optimizer's subquery rewrite, and shows how adding hints and materialization reduces execution time from over 16 seconds to under one second.
The article examines MySQL pagination performance on a large order_history table, presenting various pagination methods—including simple LIMIT, subquery, ID range, and temporary table techniques—along with detailed test results that show how query speed varies with record count and offset.
This article explains the MySQL EXPLAIN command, its extended forms, the meaning of each output column, common performance pitfalls such as full table scans, using temporary tables or filesorts, and provides practical optimization recommendations with illustrative examples.
The article explains MySQL 8.0.18's new hash join capability, shows how to enable and view it with EXPLAIN FORMAT=TREE, provides sample SQL and table definitions, compares its performance against block nested‑loop joins under various conditions, and discusses configuration limits and tuning tips.
This guide explains why MySQL queries become slow, how to diagnose issues with slow‑query logs, Explain and Show Profile, details the structure and types of indexes, interprets each column of the Explain output, and demonstrates practical optimization steps with real SQL examples.
This article explains why MySQL indexes dramatically improve query speed, outlines common reasons for slow SQL, shows how to use EXPLAIN to analyze execution plans, and provides step‑by‑step optimization examples with code and visual illustrations.
This article explains how to optimize Hive query performance by configuring fetch task conversion, enabling local mode, using map‑side aggregation, handling COUNT(DISTINCT) efficiently, avoiding Cartesian joins, applying partition and column pruning, and setting dynamic partition parameters, with practical command‑line examples.
This guide walks you through MySQL's EXPLAIN command, explaining each output column, demonstrating how to read execution plans for simple selects, joins, subqueries, UNIONs, and advanced variants, and showing how to use this information to improve query performance.
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 examines MySQL pagination on a multi‑million‑row table, compares plain LIMIT queries with subquery, ID‑range, and temporary‑table techniques, provides SQL examples and performance measurements, and offers recommendations for fast, scalable paging.
