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 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.
This article outlines a comprehensive approach for search product managers to drive system improvements, covering overall architecture, query understanding, recall and ranking optimization, business and presentation rules, content enrichment, frontend design, and methods for uncovering user needs through data and behavior analysis.
This article presents a comprehensive guide to MySQL performance tuning, covering the use of EXPLAIN, proper handling of IN clauses, selective column queries, limit usage, index best practices, avoiding costly operations, pagination techniques, full‑text search, join optimization, and advanced directives such as FORCE INDEX and STRAIGHT_JOIN.
This article explains MySQL’s logical architecture, query processing stages, client‑server protocol, query cache, parsing, optimization, execution engine, and provides practical performance‑tuning advice such as index design, data‑type choices, covering indexes, limit pagination, and handling UNION and JOIN operations.
This article examines various MySQL pagination strategies—including simple LIMIT clauses, sub‑query optimizations, ID‑range queries, and temporary‑table methods—by testing them on a table with millions of rows and presenting detailed timing results that reveal significant speed improvements.
This guide explains MongoDB 4.2's wildcard indexes, showing how to create single‑field and full‑field wildcard indexes, how they index dynamic schemas, their query behavior for objects and arrays, supported index types, and important limitations such as sparsity and $exists handling.
This guide explains each column returned by MySQL's EXPLAIN output, details join types, possible keys, extra information, and shows how to use EXPLAIN EXTENDED, SHOW PROFILE, and optimizer trace to analyze and optimize SQL queries effectively.
This article explains why using indexes does not guarantee fast MySQL queries, examines full index scans, index selectivity, the cost of back‑table lookups, and demonstrates advanced techniques such as index condition push‑down and virtual columns to improve performance.
This article explains how MySQL's EXPLAIN command reveals the execution plan of a query, detailing each column such as id, select_type, table, type, possible_keys, key, key_len, ref, rows, extra and filtered, while also covering its limitations and a practical case study.
This article provides a detailed collection of SQL Server commands and techniques covering database creation, table and index management, common DML statements, advanced set operators, various join types, grouping, pagination, maintenance tasks, replication setup, linked‑server usage, and a synchronization stored procedure.
This article provides a comprehensive overview of OLAP systems, classifying them by data volume and modeling approach, comparing MOLAP, ROLAP, HOLAP and HTAP, reviewing popular open‑source products, and detailing architectural, query‑optimization, vectorization, storage and resource‑management techniques that affect analytical warehouse performance.
This article examines why full‑table scans on massive MySQL tables are slow, explains standard LIMIT‑based pagination, and presents several optimization strategies—including sub‑queries, ID‑range filtering, and temporary tables—backed by concrete performance measurements.
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 describes how JD.com’s Algorithmic Intelligence team built a ClickHouse‑based real‑time analytics engine, covering ClickHouse fundamentals, MergeTree table design, Kafka‑Flink data pipelines, JDBC batch loading, query‑optimization techniques, and monitoring for handling billions of rows with sub‑second response times.
This article explains how MySQL stores data using pages, page directories, and multi‑page structures, demonstrates why MySQL silently sorts rows on insert, and shows how these mechanisms combine into the B+‑tree index that optimizes query performance and supports clustering, non‑clustering, and composite indexes.
This article explains how MySQL processes a SELECT statement—from client request, cache check, parsing, optimization, and execution—to return results, using a sample User table, a detailed query example, and a step‑by‑step walkthrough of the parser, optimizer, and executor components.
This article explains various MongoDB pagination strategies—including skip/limit, range queries, and sorting—covers performance monitoring with profiling, details index creation and design principles, and describes how to interpret explain plans for efficient query optimization.
This article presents a 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.
A MySQL join between a large and a small table ran for seconds because differing character sets forced implicit conversions that disabled the index, and the article shows how to diagnose the issue with EXPLAIN, fix it by aligning charsets, and avoid similar pitfalls.
This article explains MySQL's logical architecture, query processing steps, and the underlying principles of query optimization, covering topics such as client‑server protocol, query cache, parsing, optimizer cost model, index structures, and practical performance‑tuning strategies for efficient database operations.
This article explains why large OFFSET values in MySQL LIMIT queries cause severe performance degradation and demonstrates how rewriting the query to fetch primary keys first and then joining reduces execution time from seconds to milliseconds, supported by buffer‑pool analysis and practical test results.
The article explains how using LIMIT with a large offset on a MySQL table forces the engine to scan hundreds of thousands of index rows and cluster‑index pages, dramatically increasing I/O, and shows a sub‑query join technique that reduces the scan to just the needed rows, cutting execution time from minutes to fractions of a second while also illustrating the impact on the InnoDB buffer pool.
This article explains the concept of Hash Join in MySQL 8.0, compares it with Nested Loop joins, shows how to enable or force it with server variables or hints, and presents performance benchmarks that demonstrate its speed advantages on large datasets.
This article explains why using an index does not guarantee a fast query in MySQL, analyzes how slow‑query detection works, demonstrates full‑index scans, discusses index selectivity, the cost of row‑lookup (back‑table) operations, and shows how virtual columns and index condition push‑down can improve performance.
This article explains how MySQL executes COUNT(*) and COUNT(column) queries on InnoDB tables, showing example results, detailing each layer's processing steps, the handling of NULL values, and the subtle performance differences between the two forms.
This article explains what database indexes are, why they are needed, the various index types and their characteristics, guidelines for choosing columns to index, and compares the performance of hash and B‑tree indexes in MySQL.
This article explains MySQL's three‑layer logical architecture, the end‑to‑end query execution flow—including client/server protocol, query cache, parsing, cost‑based optimization, execution engine, and result delivery—followed by practical performance‑tuning advice on schema design, data types, index creation, and specific query optimizations such as COUNT(), JOINs, LIMIT pagination, and UNION handling.
This article explains how to use MySQL's EXPLAIN command, describes each column in its output—including id, select_type, table, type, possible_keys, key, key_len, ref, rows, filtered, and Extra—provides syntax examples, discusses join type rankings, and outlines the command's limitations for query performance analysis.
MySQL 8.0.18 adds the EXPLAIN ANALYZE feature, which runs a query and reports actual execution times, row counts, and loop iterations, enabling developers to compare estimated costs with real performance and fine‑tune their queries for better efficiency.
This article investigates a MySQL slow query caused by a subquery that triggers a full‑table scan, explains MySQL's query rewrite behavior, and shows how Django's lazy ORM evaluation can worsen performance, offering practical fixes using explicit query execution or join rewrites.
This comprehensive guide answers 100 common MySQL interview questions, covering index types and trade‑offs, transaction concepts and isolation levels, table design best practices, storage engine differences, and various performance‑related tips for developers.
This article explains why MySQL can exhaust disk space when implicit temporary tables grow too large, describes how memory limits and storage‑engine choices affect their creation, and provides practical configuration and query‑optimization techniques to avoid service interruptions.
This article recounts a detailed MySQL interview, explaining index fundamentals, B‑Tree versus hash structures, clustered and covering indexes, composite indexes with left‑most prefix rules, and advanced optimizations like index condition pushdown, offering practical insights for database professionals.
The article explains MySQL 8.0’s new iterator‑based executor, detailing the underlying iterator model, its advantages over the legacy executor, the specific RowIterator classes introduced, supported query features, and future enhancements for more advanced algebraic operations.
This article examines frequent MySQL performance problems such as inefficient LIMIT pagination, implicit type conversion, sub‑query updates, mixed ordering, EXISTS usage, condition push‑down limits, early result set reduction, and intermediate result set optimization, providing rewritten SQL examples that dramatically improve execution speed.
This article examines the core challenges of query optimization in relational databases—accurate statistics, massive plan spaces, and efficient plan management—and explains how OceanBase addresses them through logical/physical row concepts, real‑time statistics, distributed two‑stage planning, adaptive caching, and plan evolution mechanisms.
This article explains why a MySQL statement may run slowly—whether only occasionally or every time—by examining redo‑log flushing, lock contention, missing or misused indexes, optimizer mis‑estimates, and provides practical commands to identify and resolve each issue.
This article explains why traditional MySQL pagination (ORDER BY … LIMIT N,M) becomes painfully slow on tens of millions of rows, analyzes the root causes, and presents concrete optimizations such as covering‑index delayed joins, bookmark‑based scans, and query rewrites that can cut execution time to a third of the original.
This article explains MySQL's execution stages, defines SemiJoin, details how IN subqueries can be transformed into SemiJoin queries, outlines conversion constraints, provides example transformations, and describes the various execution strategies MySQL may use for SemiJoins.
This article explains MySQL's internal architecture and the step‑by‑step execution flow of SELECT and UPDATE statements, covering server layers, storage engines, parsers, optimizers, executors, and the role of binlog and redo log in ensuring data consistency.
This article explains MySQL's logical architecture, query processing steps, and the inner workings of its optimizer, cache, and execution engine, then offers practical performance‑tuning advice on schema design, index creation, and specific query patterns to help developers make informed optimization decisions.
This guide presents 19 practical MySQL optimization tips—including using EXPLAIN, avoiding SELECT *, limiting IN lists, preferring UNION ALL, improving pagination, leveraging full‑text indexes, and proper join strategies—to help developers write faster, more efficient queries.
Zhihu built a self‑service analytics platform on Druid, introduced a multi‑level Redis caching strategy, split long‑duration queries across multiple brokers, and added automatic cache invalidation to dramatically improve query latency and resource usage for massive daily request volumes.