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 MySQL's logical architecture, query execution steps, client‑server protocol, query cache behavior, cost‑based optimizer mechanics, and practical index design strategies—including B+Tree fundamentals, covering indexes, and common pitfalls—to help developers achieve measurable performance improvements.
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 the inner workings of relational databases, covering their architecture, indexing, time‑complexity analysis, query optimization, merge sort, client and query managers, caching strategies, transaction handling, ACID properties, concurrency control, and lock management.
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 introduces SQL fundamentals, explains DDL/DML/DCL categories, and provides practical PostgreSQL query optimization techniques—including index selection, condition rewriting, sub‑query removal, expression and partial indexes, DDL decomposition, CTE usage, and key configuration parameters—to help developers and DBAs improve performance and maintainability.
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.
This article explains MySQL's logical architecture, query processing steps, and the inner workings of the optimizer, then provides practical performance‑tuning advice covering data types, index design, cache usage, and specific query patterns such as COUNT, JOIN, LIMIT pagination and UNION.
This article examines TiDB, a world‑class open‑source distributed NewSQL database, comparing its transaction and query behavior with MySQL, discussing underlying Percolator model, server‑side prepared statements, batch processing techniques, and practical optimization strategies for developers.
This article explains how relational optimizers validate, analyze, and transform SQL statements into executable code, detailing the four-step optimization process, query‑analysis factors, cost‑based access‑path selection, table scans, index lookups, and the role of data prefetching in improving performance.
When paginating millions of rows in MySQL, avoid large OFFSET scans by using delayed joins or bookmark techniques that first fetch primary‑key values via covering indexes, then retrieve the needed rows, dramatically reducing I/O and query latency.
This article explains how large data volumes and differing environments can degrade MySQL query performance, demonstrates using EXPLAIN to analyze index usage and row scans, and offers practical steps such as adding filters, pagination, and code adjustments to improve speed.
This article explains why protecting database systems is critical, reviews existing SQL‑analysis tools, and dives deep into MySQL's lexical and syntax parsing techniques—including Bison‑generated parsers, core data structures, and practical applications such as useless‑condition removal and SQL feature generation—to help DBAs automate and optimize their workflows.
This article uses a dialogue‑driven story to explain how PostgreSQL gathers statistics, computes selection rates, evaluates various physical scan and join paths, and combines CPU, I/O, and communication costs—including startup versus total cost—to choose the most efficient execution plan.
This article explains MySQL's logical architecture, query execution flow, and the inner workings of its optimizer, then provides practical performance‑tuning advice—including index design, query cache handling, and specific query‑type optimizations—to help developers make informed decisions for real‑world workloads.
This article demystifies MySQL query optimization by explaining the server's logical architecture, the end‑to‑end query processing flow, the role of the client/server protocol, query cache, parsing, optimizer, execution engine, and result delivery, and then offers concrete performance‑tuning recommendations on schema design, data types, indexing strategies, and specific query patterns such as COUNT(), JOIN, LIMIT pagination, and UNION.
This article explains how MySQL indexes work, why queries can become slow, how to interpret the EXPLAIN output—including id, select_type, table, type, possible_keys, key, key_len, ref, rows, and extra columns—and provides practical examples and optimization cases to improve query performance while balancing the cost of maintaining indexes.
This article presents thirty practical SQL tuning guidelines—ranging from avoiding certain WHERE‑clause operators and functions to proper index usage and set‑based alternatives—that help prevent full table scans and improve overall database performance.
This article analyzes why two similar MySQL SELECT statements return different rows by examining index structures, covering index behavior, the optimizer's choice of secondary versus primary keys, and experimental verification using EXPLAIN statements and index modifications.
This article examines a high‑volume Oracle query scenario, explains why using multiple IN clauses on millions of rows leads to costly full‑table scans, and presents three alternative strategies—including a single‑SQL join, UNION ALL view, and temporary‑table approaches—along with practical examples and performance considerations.
The article examines SparkSQL’s predicate‑pushdown behavior for left outer joins, detailing four rules that show when pushing join‑condition filters to the left or right tables yields correct, faster results and when it produces incorrect outcomes, highlighting both performance gains and subtle errors.
This article presents five practical MySQL index usage questions, each with multiple‑choice options, followed by detailed answers and explanations that illustrate when an index is appropriate, how function usage affects it, and the impact on query performance.
Attending VLDB 2017 in Munich, the report summarizes the conference’s broad coverage of database research—from new hardware‑accelerated prototypes and Spark‑based big‑data processing to Oracle and SAP HANA case studies, keynotes, notable papers, and reflections on industry trends and Chinese contributions.
This article explains how PostgreSQL transforms and rewrites query trees, why a completed transform‑rewrite tree may not be optimal, and details the subquery_planner’s role, tuple_fraction handling, and the step‑by‑step logical optimization process used to generate efficient execution plans.
This article explains MySQL's logical architecture, the step‑by‑step query execution process, and provides practical performance‑tuning advice—including schema design, data‑type choices, index strategies, and specific query optimizations such as COUNT, JOIN, LIMIT pagination, and UNION—backed by code examples and diagrams.
This article outlines essential database design principles and a comprehensive set of query optimization techniques, covering row size limits, appropriate data types, field length choices, index usage, avoiding costly WHERE clause patterns, and best practices for temporary tables, transactions, and result set handling to boost SQL performance.
The article examines why a MySQL DELETE statement with a composite index on biz_date and status still triggers a full‑table scan, shows EXPLAIN output, tests forced index usage, and ultimately demonstrates that narrowing the date range in the query dramatically reduces rows examined and improves performance.
This article walks through diagnosing a sluggish Oracle search query with many optional filters, removing unnecessary joins and function calls, applying conditional MyBatis joins, and creating composite indexes to dramatically lower the execution plan cost from 13902 to under 100.
This guide outlines best practices for SQL Server database design, covering recommended field types, primary key and null policies, index creation rules, low‑selectivity column handling, and a comprehensive set of query‑writing conventions to improve performance and maintainability.
This article explains MySQL's logical architecture, query processing steps, client/server protocol, query cache behavior, parsing, optimizer cost model, execution engine, result delivery, and provides practical performance‑tuning advice on schema design, index creation, and specific query patterns.
This guide explains how MySQL decides whether to use an index for range queries, LIKE patterns, and ORDER BY operations, detailing key_len calculation, index key vs filter vs table filter concepts, and practical examples for BETWEEN and sorting optimization.
This article explains the inner workings of relational databases, covering their core components, query optimization flow, indexing, merge sort, transaction handling, concurrency control, and lock management, while illustrating concepts with examples and diagrams.
A colleague handed over a seemingly simple SQL Server query that ran in five seconds for early dates but never returned for later dates, and the article walks through the diagnosis, execution‑plan analysis, statistics update, and final fix that restored performance.
This article explains the logical and physical execution order of SQL statements, offers practical tips for returning only needed data, reducing redundant work, using temporary tables wisely, optimizing subqueries and indexes, and provides concrete code examples to improve query performance.
This article explains SQL Server's logical and physical query execution order, offers practical techniques such as converting loops to batch operations, using temporary tables, optimizing indexes, and avoiding common pitfalls, all illustrated with code samples and performance measurements.
This article explains SQL Server's logical query execution sequence, details the physical step‑by‑step processing, and provides practical optimization advice such as avoiding SELECT *, using proper indexes, handling temporary tables, rewriting subqueries, and safely managing transactions to improve performance and maintainability.
This article walks through common traps in Oracle's Cost‑Based Optimizer (CBO), explains why query transformations, FILTER operations, and inaccurate statistics can cripple performance, and provides practical solutions—including NULL‑AWARE anti‑joins, hint usage, SQLT tooling, and SQL rewrites—to dramatically speed up problematic queries.
This article explains SQL join types, the three normal forms, table creation and alteration commands, data integrity constraints, practical query‑optimization tips, the roles and differences of clustered and non‑clustered indexes, and the distinctions between stored procedures and functions.
This article provides practical guidelines for designing efficient database schemas and optimizing SQL queries, covering row size limits, data type choices, index usage, common pitfalls in WHERE clauses, and best practices for temporary tables and transaction handling to improve overall database performance.
This article presents five useful Oracle SQL techniques—including string concatenation, DISTINCT, WHERE clauses, flexible COUNT usage, and selecting only necessary columns—to improve query readability, eliminate duplicate rows, and enhance overall database performance.
This article describes Hulu’s real‑world OLAP query optimization, covering the fundamentals of OLAP, comparisons of row‑ and column‑based storage formats, detailed indexing mechanisms of Parquet, ORC and CarbonData, and the specific schema, shuffle, block size, speculation and GC tuning techniques that enabled CarbonData to dramatically accelerate wide‑table queries on SparkSQL.
This article presents a comprehensive collection of practical SQL performance tuning guidelines, covering index usage, query rewriting, avoiding costly operators, proper data types, efficient joins, and view creation to help developers write faster, more resource‑efficient database queries.
After a typhoon delayed work, the author tackled a 347‑line Oracle SQL that took over four minutes to return results, dissected its massive subqueries and views, applied three rounds of restructuring with WITH clauses and driver‑table tactics, and ultimately reduced the total runtime to just 3.5 seconds while dramatically speeding up deep data extraction.
This article examines a high‑volume Oracle query scenario, critiques the current multi‑IN approach, and presents three alternative solutions—including a single‑SQL join, a UNION ALL view, and the use of global temporary tables—detailing their performance implications and practical trade‑offs.
This article explains the various timeout scenarios in SQL Server‑backed web applications—including ASP.NET, IIS, WebService, SSMS, remote login, and linked‑server timeouts—provides diagnostic queries such as the Connectivity Ring Buffer, and offers concrete tuning steps and best‑practice recommendations to resolve and prevent them.
This guide presents a concise, experience‑based checklist for optimizing SQL SELECT statements, covering index verification, dataset reduction, column selection, unnecessary table removal, outer join elimination, and computed column cleanup, each illustrated with real‑world examples and visual aids.
This article demonstrates how InnoDB returns rows in primary‑key order during full‑table scans, while queries that use a secondary index follow the leaf‑node order of that index, and it compares performance implications of index‑only scans versus clustered‑index scans.
This article presents a comprehensive set of practical strategies for improving database query performance, covering index design, SQL statement refinements, Java backend considerations, hardware tuning, and storage‑procedure usage to accelerate operations on large‑scale relational databases.
This guide shows how to use Oracle 12c's DBMS_STATS.SEED_COL_USAGE and REPORT_COL_USAGE to automatically identify useful column groups, create extended statistics, and verify the impact on execution plans with concrete SQL examples.
This guide presents thirty practical SQL optimization techniques—ranging from index usage and avoiding costly operators to rewriting expressions and minimizing temp objects—to help developers prevent full table scans and boost database performance.
This guide explains how to set up MySQL master‑slave replication, configure binlog, create users, synchronize data, troubleshoot common issues, and apply query‑performance optimizations such as limiting result sets, using covering indexes, and refactoring complex joins.
This article explains the theory behind SQL Server's parallel execution, illustrates its performance gains with Amdahl's Law, lists operators that block parallelism, discusses configuration settings, warns of deadlocks and thread starvation, and presents practical MapReduce‑style optimizations for real‑world workloads.
A high‑load MySQL server was investigated, revealing that a seemingly simple subquery caused full table scans, but rewriting it as a JOIN or using an equality subquery enabled index usage, and newer MySQL versions have fixed the optimizer bug.
This comprehensive guide enumerates over forty common causes of slow SQL Server queries and provides detailed optimization techniques—including indexing strategies, hardware upgrades, partitioning, cursor handling, and configuration tweaks—to dramatically improve query performance and reduce resource bottlenecks.
This article analyzes the characteristics of log data, explains why HBase is chosen for log storage, discusses the shortcomings of self‑built indexes, and presents optimization strategies such as rowKey design, filter usage, coprocessor integration, and third‑party indexing to improve query performance.
A simple MySQL query on a million‑row picture table was sped up by adding a composite covering index, reducing average execution time from about 40 ms to 30 ms and eliminating the need for a table lookup.
This article explains SparkSQL's query processing pipeline—from parsing and logical planning through optimization and physical execution—highlighting why it often outperforms Hive on MapReduce by reducing I/O, minimizing shuffle stages, and reusing JVMs.
This article explains MySQL's indexing mechanisms—including BTREE and hash structures, page layout, index types, and practical query patterns—while showing how to interpret EXPLAIN output and avoid common pitfalls for efficient database performance.
Adding the right indexes to a complex 12‑table query dramatically cut I/O but unexpectedly increased execution time, and the article explains how an undocumented SQL Server hint forced parallelism to bring the runtime back down.
An expert MySQL case study compares two similar queries—one using a materialized subquery and the other with an OR condition that triggers index reads—showing the former runs about 40 times slower, and explains the impact of materialization, semi‑join optimization, and index usage on performance.
