1 views collected around this technical thread.
This article presents a comprehensive set of MySQL optimization techniques, covering the avoidance of SELECT *, proper use of specific fields, index-friendly query patterns, join strategies, handling of NULL and default values, efficient use of UNION, batch inserts, and numerous other performance‑enhancing tips for database developers.
This article presents a series of MySQL performance‑tuning techniques—including smarter LIMIT usage, avoiding implicit type conversion, rewriting joins, handling mixed ordering, replacing EXISTS with joins, pushing predicates into subqueries, early row limiting, and using WITH clauses—to dramatically reduce query execution time across common scenarios.
MySQL's Multi-Range Read (MRR) optimization reduces random disk I/O by scanning indexes, sorting keys, and retrieving rows sequentially, offering advantages like sequential data access and batch key processing, while outlining its limitations, applicable scenarios, usage conditions, and configuration via optimizer_switch variables and buffer settings.
The article shows how common MySQL performance pitfalls—such as large LIMIT offsets, implicit type conversions, sub‑query‑based updates or deletes, mixed ORDER BY, EXISTS clauses, and misplaced predicates—can be rewritten into index‑friendly forms using filters, joins, early limits, UNION ALL, and CTEs to achieve execution times that drop from seconds to milliseconds.
This article walks through a MySQL performance case study, showing how a slow sub‑query can be accelerated by adding single‑column and composite indexes, rewriting the query with EXISTS or JOIN, and applying best‑practice indexing strategies such as covering indexes and left‑most prefix rules.
This article details how JD's BIP procurement system tackled massive query‑performance challenges by segmenting order data, leveraging the JED distributed MySQL solution, introducing JimKV for hot‑data caching, and offloading complex searches to Elasticsearch, resulting in dramatically reduced load and faster user experiences.
This article presents a comprehensive case study of how JD's procurement system (BIP) tackled massive data volume and complex query challenges by redesigning data models, introducing heterogeneous storage for inbound orders, leveraging JED and JimKV, and offloading complex searches to Elasticsearch, resulting in dramatically reduced database load and improved user experience.
This article explains common MySQL performance pitfalls such as large‑offset LIMIT queries, implicit type conversion, sub‑query updates, mixed sorting, inefficient EXISTS clauses, predicate push‑down limitations, and demonstrates how rewriting with proper indexes, JOINs, early range reduction, and WITH (CTE) statements can reduce execution time from seconds to milliseconds.
Baidu MEG’s TDE‑ClickHouse optimization in the Turing 3.0 ecosystem boosts query speed up to 10×, halves latency, enables billion‑row bulk imports in under two hours, and migrates to a cloud‑native, ZooKeeper‑free architecture supporting 350 k CPU cores, 10 PB storage, and sub‑3‑second responses for 150 k daily BI queries.
This article presents practical MySQL performance tuning methods, covering LIMIT pagination, implicit type conversion pitfalls, rewriting joins for updates/deletes, mixed-order sorting, replacing EXISTS with joins, predicate pushdown, early result limiting, and intermediate result pushdown, each illustrated with SQL examples and execution plan analyses.
This article explains MySQL index fundamentals—including hash tables, ordered arrays, and B+‑tree structures—covers index maintenance, demonstrates how indexes improve query execution, discusses best practices such as left‑most prefix, covering indexes, index push‑down, and unique indexes, and provides practical tips for index selection, avoiding index loss, and using EXPLAIN for performance tuning.
This article provides a comprehensive guide to MySQL indexing, covering index data structures, maintenance mechanisms, query execution, optimization techniques such as left‑most prefix, index push‑down, unique indexes, index selection, and common pitfalls that cause index inefficiency.
This article presents a series of MySQL performance‑enhancing techniques—including smarter LIMIT usage, avoiding implicit type conversion, rewriting UPDATE/DELETE with JOINs, mixed‑order sorting tricks, replacing EXISTS with JOINs, condition push‑down, early result limiting, and intermediate result push‑down—to dramatically reduce query execution time.
This article walks through a MySQL 5.6 scenario with course, student, and score tables, demonstrates why a naïve sub‑query is extremely slow, and shows step‑by‑step how adding single‑column, composite, and covering indexes, as well as rewriting the query to joins, can reduce execution time from hours to milliseconds.
This article reviews MySQL's execution process and presents a comprehensive list of practical SQL optimization strategies—including indexing, query rewriting, avoiding costly operators, using batch operations, and leveraging EXPLAIN—to improve database performance and maintainability.
This article demonstrates how to dramatically improve MySQL query performance by analyzing execution plans, adding appropriate single‑column and multi‑column indexes, rewriting subqueries as joins, and applying covering indexes, showing step‑by‑step measurements and visual explanations.
This article explains how MySQL's optimizer calculates execution costs for different indexes, why it may select a full table scan instead of an apparently better index, and shows how to use EXPLAIN and optimizer trace to reveal and improve the chosen execution plan.
The article examines why using application‑layer association instead of MySQL JOIN can improve cache efficiency, reduce lock contention, simplify scaling, and enhance query performance, while also outlining scenarios where JOIN is still beneficial and offering practical alternatives for large‑scale data retrieval.
This article explains five common MySQL query patterns—using functions, leading wildcards with LIKE, OR operators, inequality comparisons, and NULL checks—that prevent the database engine from utilizing indexes, and provides example SQL statements illustrating each case.
This article explains how MySQL implements partition pruning, describing the conditions under which the optimizer can skip irrelevant partitions, the internal data structures such as read_partitions and lock_partitions, the role of SEL_TREE and SEL_ARG, and provides a detailed example with code to illustrate the pruning process.