The MySQL optimizer can select an inappropriate index due to inaccurate statistics or sampling errors, but you can correct this by refreshing statistics with ANALYZE TABLE, increasing sample pages, or explicitly forcing the right index using the FORCE INDEX hint.
Discover how to set up MySQL InnoDB ReplicaSet and configure MySQL Router 8.2 for automatic read/write splitting, enabling reads to be routed to replicas and writes to the primary without code changes, complete with step-by-step commands, configuration snippets, and visual verification.
This article provides a hands‑on guide to MySQL index optimization, deeply exploring EXPLAIN execution plans, new features in MySQL 8/8.4, practical index design principles, step‑by‑step SQL examples, common pitfalls, advanced techniques like functional and descending indexes, and comprehensive monitoring and troubleshooting strategies.
This guide explains what PostgreSQL execution plans are, how they are generated, and how to interpret them using EXPLAIN and EXPLAIN ANALYZE, then demonstrates three real‑world case studies that show step‑by‑step optimizations with indexes, configuration tweaks, and query rewrites to dramatically improve query speed.
This guide demonstrates how MySQL Router 8.2 can automatically route read and write traffic using an InnoDB ReplicaSet, showing the minimal architecture, configuration steps, command examples, and the resulting read‑write split behavior without requiring application changes.
Using a 600k‑record IP range dataset, we built identical tables in ClickHouse and Doris, and a Redis skip‑list store, then ran three Flink‑Kafka streaming jobs to compare query latency across the three databases under varying traffic rates, revealing Redis as fastest, ClickHouse second, Doris slowest.
This article explains why excessive MySQL joins—often inherited from legacy or Oracle‑migration code—cause performance and maintenance problems, and presents nine concrete optimization techniques including SQL splitting, temporary tables, redundant fields, index tuning, query rewriting, result‑set reduction, configuration tweaks, big‑data tools, and materialized summary tables.
This guide walks through the SQL query processing workflow, explains how parsing, optimization, and execution plans affect performance, and provides concrete techniques—such as selecting specific columns, avoiding DISTINCT, using proper indexes, preferring EXISTS over COUNT, limiting result sets, and replacing HAVING with WHERE—to dramatically speed up queries while keeping statements concise.
This guide explains how MySQL processes queries in three index‑related stages—Index Key, Index Filter, and Table Filter—showing why keeping operations in the index layer dramatically improves performance and how to design and diagnose indexes effectively.
The article recounts a MySQL production incident where an INT auto‑increment column reached its maximum value, causing insert failures, and walks through analysis, three remediation options, a stored‑procedure cleanup, a conversion to BIGINT, performance monitoring, and lessons on concurrency and schema design.
The article analyzes a common Java interview question about using JOINs, explaining performance bottlenecks, scalability issues in distributed and micro‑service architectures, and offers practical guidelines and best‑practice alternatives for handling data associations efficiently.
This report evaluates the AI‑driven SQLFlash tool against the upgraded SCALE benchmark dataset, presenting core metrics on syntax compliance, logical equivalence, and optimization depth, and analyzes strengths, limitations, and future improvement directions for production‑grade SQL tuning.
This guide explains SQLite's essential PRAGMA commands, compares synchronous modes, distinguishes persistent from session‑level settings, and details key Linux‑Windows differences, providing concrete initialization scripts to optimize speed, reliability, and disk usage.
This article explains SQLite's PRAGMA commands, compares synchronous modes and WAL settings, distinguishes persistent from session‑level pragmas, and highlights key Linux‑Windows differences, providing practical examples and a step‑by‑step initialization checklist for reliable database operation.
Using random UUID primary keys forces PostgreSQL to write to unpredictable index pages, causing heavy CPU usage, large index size, and dramatically higher insert latency, while switching to a sequential bigint key restores performance and reduces write amplification.
Learn how to prevent six frequent MySQL problems—including index misuse, transaction isolation anomalies, inefficient pagination, charset and collation errors, risky foreign-key cascades, and misconfigured connection pools—through detailed explanations, code examples, and practical mitigation strategies.
This article explains the performance drawbacks of using UUIDs as primary keys in MySQL—including larger indexes, random inserts, and slower comparisons—details why data updates trigger index refreshes, and offers practical optimization techniques such as ordered UUIDs, binary storage, hybrid auto‑increment keys, and table partitioning.
This comprehensive guide explains why MySQL indexes often become ineffective, outlines ten typical failure scenarios with concrete EXPLAIN examples, and provides practical solutions, scripts, best‑practice recommendations, and monitoring techniques to dramatically improve slow‑query performance.
Redis 6.0 adds a multithreaded network I/O model, client caching, ACL and RESP3, and explains why earlier versions were single‑threaded, how the new IO threads cooperate with the main thread, and the practical steps to enable and tune multithreading for better performance.
A reproducible evaluation of StarRocks using the open‑source Coffee‑shop Benchmark shows that across 500 M, 1 B and 5 B row scales, StarRocks completes 17 complex join and aggregation queries 2–10× faster and with significantly lower cost than ClickHouse, Snowflake and Databricks, demonstrating superior performance and cost efficiency for analytical workloads.
This article explains the differences between MySQL's RR and RC isolation levels, details master‑slave replication and binlog formats, shows why RR is the default, and why many internet companies now prefer RC for higher concurrency despite its phantom‑read trade‑offs.
This article presents a detailed performance benchmark comparing MySQL 9.0 and PostgreSQL 17.0, measuring data‑ingestion latency, throughput, saturation, CPU and memory usage, as well as query efficiency, and concludes which open‑source database delivers superior write and read performance.
StarRocks 4.0 introduces FlatJSON, a columnar storage and execution engine that converts high‑frequency JSON fields into native columns, dramatically reducing I/O and CPU costs and enabling JSON queries to run with performance close to that of traditional columnar data.
This article explains why MySQL master‑slave replication can fall behind, outlines common causes of data inconsistency, and presents four comprehensive solutions—including architecture tweaks, multi‑threaded replication, configuration tuning, and Percona Toolkit verification—to achieve fast, stable, and accurate data synchronization.
In a microservice‑based e‑commerce platform, a seemingly harmless slow MySQL query exhausted the database connection pool, blocked Redis connections, and caused a chain reaction that filled the Redis connection pool, leading to widespread service timeouts and a full‑scale system avalanche during a high‑traffic promotion.
This article compares PostgreSQL and MySQL across architecture, indexing, query optimization, data types, concurrency control, and real‑world benchmarks, showing why PostgreSQL generally outperforms MySQL in high‑throughput, complex‑query, and data‑intensive applications.
This article compares PostgreSQL and MySQL across architecture, indexing, complex query optimization, data‑type support, transaction handling, and real‑world benchmark results, showing why PostgreSQL is generally the better choice for high‑concurrency, data‑intensive applications.
This article walks through the evolution from binary search trees to balanced trees, B‑trees and finally B+ trees, explaining how MySQL's InnoDB and MyISAM storage engines implement these structures to reduce disk I/O and boost query performance.
This article explains how to verify whether MySQL uses an index with EXPLAIN, outlines eight common reasons why an index may be ignored—including better alternatives, low selectivity, stale statistics, full‑table scans, structural limitations, function usage, invisible indexes—and provides practical commands and hints to resolve each issue.
Index Condition Pushdown (ICP) in MySQL pushes filter conditions to the storage engine, dramatically cutting data transferred to the server layer, speeding up queries especially when using composite indexes, and requiring no changes to the original SQL statements.
This article explains the core purpose of MySQL indexes, enumerates twelve typical situations that cause index failure, provides concrete SQL examples and corrections, and shows how to use EXPLAIN to diagnose and verify index usage for better query performance.
This article explains why executing many small database queries in a short time harms performance, shows how to detect the N+1 problem with monitoring and logs, and presents refactoring techniques such as eager loading, batch operations, aggregation, caching, and hand‑crafted SQL to dramatically improve speed, scalability, and code quality.
This article explains why using IN and NOT IN in SQL queries often leads to poor performance and incorrect results, demonstrates common pitfalls with real examples, and shows safer alternatives such as EXISTS, NOT EXISTS, and JOIN to write efficient and reliable database queries.
This article explains what materialized views are, outlines their advantages such as query acceleration, lightweight ETL, and lake‑warehouse integration, classifies them by sync mode, table count, and refresh strategy, and highlights their limitations and best‑practice recommendations for production use.
In Redis's single‑threaded model, commands with O(n) or higher complexity—such as KEYS, HGETALL, LRANGE, SMEMBERS, and DEL on large keys—can block the server during traffic spikes, but using incremental scans, key splitting, and async deletion can eliminate the risk and keep services responsive.
Valkey 8.0 introduces asynchronous IO threads, data prefetch, and memory access amortization (MAA), offloading many tasks from the main thread and dramatically increasing single‑node throughput to up to 1 million requests per second, while also improving memory utilization and overall system efficiency.
A thorough benchmark shows that for typical read‑heavy, single‑server web applications SQLite can be up to twenty times faster than MySQL, while also offering simpler deployment, lower cost, and adequate scalability, though MySQL still wins in high‑concurrency write‑intensive scenarios.
This article compares MySQL's three common storage engines—InnoDB, MyISAM, and Memory—by examining their core features, locking mechanisms, transaction support, durability, foreign‑key capabilities, typical use cases, and provides concrete CREATE TABLE examples and a side‑by‑side feature matrix to help developers choose the right engine.
This article explores the origins of the "no more than three‑table JOIN" rule, explains MySQL's join algorithm limitations, shows real‑world performance pain points in distributed architectures, and presents practical solutions such as step‑wise queries, denormalization, and materialized views to safely break the restriction.
VIDEX is an open‑source MySQL virtual‑index framework that enables cost‑based what‑if analysis without building real indexes, integrates with AI‑driven NDV and cardinality estimators, supports modular deployment, and has been validated on TPC‑H benchmarks to closely match InnoDB query plans.
This guide explains what the MySQL slow query log is, why it matters for performance, how to check its status, enable it temporarily or permanently in Docker‑based deployments, troubleshoot command‑not‑found errors, read the log file, and filter entries to focus on the truly slow queries.
This article explains why excessive MySQL joins can cause performance problems and provides a comprehensive set of techniques—including query splitting, temporary tables, denormalization, index tuning, query rewriting, result‑set reduction, configuration tweaks, big‑data tools, and summary tables—to effectively optimize multi‑table join queries.
This article explains the MySQL Connection Control Plugins introduced in 5.7.17, how improper configuration can lead to connection backlog and database hangs, and provides step‑by‑step installation, parameter tuning, testing procedures, and mitigation strategies to resolve the issue.
This article investigates a slow OceanBase SQL query by examining CPU and TopSQL metrics, dissecting the execution plan, identifying batch join inefficiencies, rewriting the SQL for better performance, and demonstrating a reduction in execution time from over 2600 seconds to under one second.
This article walks through a mini‑project that creates a student table, adds indexes, populates it with 100 k rows, and then demonstrates six concrete scenarios—such as leading wildcards, arithmetic on indexed columns, functions, type mismatches, composite‑index misuse, and encoding differences—that cause MySQL indexes to become ineffective, showing the EXPLAIN output for each case.
A real‑world MySQL data‑migration case study shows how using INSERT INTO SELECT without proper indexing caused a full table scan, OOM errors, and data loss, and explains how to avoid the pitfall by adding appropriate indexes and understanding transaction locking.
This article explains MySQL's memory architecture, why memory usage often stays high after spikes, and provides practical steps—including connection checks, slow query analysis, workload scaling, and switching to jemalloc—to diagnose and reduce memory consumption on 360's internal cloud platform.
This article explains Redis's single‑threaded network model, the role of IO multiplexing, the multithreading features introduced in Redis 6.0, and how to configure IO threads to improve throughput and latency.
Although foreign keys enforce referential integrity, many enterprises avoid them because they can slow inserts, cause blocking cascade updates, trigger database update storms in high‑concurrency environments, and complicate sharding, leading developers to prefer redundant fields and handle relationships at the application layer.
This article explains OceanBase's full‑link tracing feature, covering its definition, internal working principle, trace‑log structure, span and tag details, configuration steps, and how to view trace data via the show‑trace command or the OCP visual interface, helping DBAs quickly diagnose performance issues.
This article explains the three MySQL EXPLAIN output formats—TRADITIONAL, JSON, and TREE—demonstrates how the JSON format provides richer cost and data‑read information, compares query costs of sample SQL statements, and offers practical optimization suggestions for poorly performing joins.
The case study details how an activity‑middleware platform tackled slow SQL problems by cleaning historic data, introducing sharding and Elasticsearch off‑loading, optimizing queries with proper indexes and simplified joins, and automating nightly deletions, ultimately cutting daily slow queries from thousands to double‑digit levels and boosting system stability.
This article examines MySQL's cost estimation process, explains why added indexes may still be ineffective, discusses the impact of memory fragmentation, and presents practical techniques such as prefix index handling, index merging, and resource monitoring to improve slow‑query performance.
During a New Year holiday, an Oracle database suffered severe hangs due to a GC buffer busy acquire wait event caused by a faulty heartbeat network cable, leading to index unusable errors and extensive troubleshooting steps that reveal common causes and preventive measures.
The article explains that using IN and NOT IN in SQL can lead to poor performance, index misuse, and incorrect results especially with NULL values, and recommends replacing them with EXISTS/NOT EXISTS or JOIN constructs for more reliable and efficient queries.
This article explains three Redis-based methods—Hash, Bitmap, and HyperLogLog—for tracking website user visits, detailing how each structure works, their implementation steps, memory and accuracy trade‑offs, and guidance on choosing the best approach for different traffic scenarios.
This article explains why the SQL keywords IN and NOT IN often lead to poor performance and unexpected results, demonstrates the problems with large tables and NULL values, and recommends using EXISTS, NOT EXISTS, or JOIN alternatives for more reliable and efficient queries.
This article explains how to read MySQL's EXPLAIN EXTRA column, covering SEMI JOIN hints such as FirstMatch, Start/End temporary, LooseScan, and the Range‑checked‑for‑each‑record optimization, with concrete examples and command‑line output to illustrate each case.
A production ClickHouse cluster suddenly showed blacked‑out dashboards due to CPU load soaring above 2,700%, and this guide walks through step‑by‑step diagnostics using system tables, a simple query to spot heavy SQL, and practical remediation actions to restore normal load levels.
This article explains how MySQL's indexing evolved from simple binary search trees through AVL and red‑black trees to B‑trees and finally B+ trees, highlighting the performance and I/O advantages that make B+ trees the preferred index structure for disk‑based databases.
This article explains how to analyze MySQL slow queries by examining response time, scanned rows, and returned rows, demonstrates EXPLAIN output with and without indexes, and covers index types, column ordering, left‑most prefix rules, and sorting strategies to improve database performance.
The article compares MySQL and PostgreSQL multi‑table join capabilities, explains why MySQL’s nested‑loop joins can be less efficient for complex queries, and discusses how decomposing joins into separate single‑table queries or moving logic to the service layer can improve performance, scalability, and caching.
Redis has unveiled a multi‑threaded query engine that dramatically increases query throughput and lowers latency for vector similarity searches, offering up to 16× performance gains and enabling real‑time Retrieval‑Augmented Generation (RAG) workloads in generative AI applications.
This article explains how ByteHouse integrates high‑performance GIS capabilities into its OLAP engine, describes its spatial indexing architecture, showcases benchmark results against ClickHouse, StarRocks, PostGIS and DuckDB using the NYC Taxi dataset, and outlines when to choose ByteHouse versus other spatial database solutions.
This article explains MySQL EXPLAIN type column, detailing the inefficiencies of type=ALL, how adding ORDER BY can change it to index, and demonstrates the use of INDEX_MERGE for UNION and INTERSECT queries, including practical examples and cost comparisons to improve query performance.
This article recounts a 2012 incident where a backend API slowed dramatically after hidden BLOB columns were added, and explains why using SELECT *—which blocks index‑only scans, forces extra I/O, increases deserialization, network, and client processing costs, and hampers schema maintenance—should be avoided in favor of explicit column lists.
This article explains the hidden performance costs of traditional OFFSET pagination in MySQL, analyzes why deep paging becomes a bottleneck, and presents keyset (cursor) pagination and other optimization techniques as efficient alternatives for handling massive or frequently changing datasets.
The article recounts a 2012 incident where a seemingly fast backend API became sluggish after hidden blob columns were added, explains how SELECT * forces full table scans, extra deserialization, network overhead, and unpredictable performance, and advises selecting only needed columns for optimal efficiency.
This article demonstrates how to use MySQL's EXPLAIN statement to examine execution plans, interpret key fields such as possible_keys, key, rows, and Extra, and apply index adjustments—including adding and forcing indexes—to improve query performance while weighing sorting costs.
This article analyzes why MyBatis-Plus generates duplicate primary keys in clustered Docker/K8S environments due to worker‑id and datacenter‑id collisions, introduces Seata's improved Snowflake algorithm that decouples from the OS clock, explains its implementation with Java code, and shows how to integrate it as a global ID generator to improve database performance and avoid page splits.
This article explores MySQL's join processing methods—Simple Nested‑Loop, Block Nested‑Loop, Hash, and Index Nested‑Loop joins—demonstrates how to create test tables, analyze execution plans, and apply practical optimizations such as indexing and query rewriting to dramatically improve multi‑table query performance.
This article explains how MySQL processes multi‑table joins, compares Simple Nested‑Loop, Block Nested‑Loop, Hash, and Index Nested‑Loop join algorithms, and demonstrates practical optimization techniques using indexes, join buffers, and query rewrites to dramatically improve performance on large datasets.
This guide enumerates thirty common business scenarios that cause SQL performance problems, explains the root causes, offers concrete optimization strategies, and provides detailed before‑and‑after query examples to help developers dramatically improve database efficiency.
The article explains why multi‑table JOINs in MySQL can degrade performance, readability, and index usage, and it presents optimization strategies such as query decomposition, data redundancy, wide tables, and introduces the hash join algorithm with detailed build and probe phases, including disk‑based handling.
This article presents nine practical strategies—including adding default filters, limiting page size, reducing joins, optimizing indexes, using straight_join, archiving data, leveraging count(*), querying ClickHouse, and implementing read‑write splitting—to dramatically improve the speed and scalability of pagination APIs in MySQL‑based back‑ends.
This comprehensive guide walks through ClickHouse write‑performance optimization, covering hardware choices, system and application‑level tuning, async insert settings, Buffer engine configuration, storage compression, real‑world case studies, monitoring queries, and actionable best‑practice recommendations.
Tencent Cloud's TDSQL shattered world records in both TPC‑DS (OLAP) and TPC‑C (OLTP) benchmarks, achieving a 7260 M QphDS score at a cost of 37.52 CNY/kQphDS, and the article explains the three self‑developed technologies—MPP execution, parallel execution framework, and columnar‑vectorized engine—that made this performance possible.
This article examines a real‑world MySQL deadlock incident, explains the inner workings of shared, exclusive, gap, next‑key, and insert‑intention locks, and shows how concurrent delete‑then‑insert operations can saturate connection pools and degrade overall database performance.
Redis has launched an enhanced, multi‑threaded query engine that dramatically increases throughput and reduces latency for vector similarity searches, enabling vertical scaling and better support for real‑time RAG applications while maintaining sub‑10 ms response times.
This article explains the fundamental concepts of MySQL index structures, detailing how clustered (primary) indexes store data physically in order, how non‑clustered (secondary) indexes reference primary keys, and compares their performance implications and practical usage scenarios.
This article explains Doris's data partitioning, the four join methods (Broadcast, Shuffle, Bucket Shuffle, and Colocate), runtime filter mechanisms and types, join reorder strategies, and practical tuning recommendations to improve query performance in the Doris MPP database.
This article walks through a real‑world MySQL InnoDB deadlock case, detailing log analysis, reproducing the issue with test data, explaining gap and insert‑intention locks, and presenting a practical solution that checks existence before updating or inserting to prevent deadlocks.
GaussDB for MySQL introduces a Limit‑Offset pushdown optimization that moves offset calculation to the InnoDB storage engine, eliminating unnecessary row transfers and index‑back‑table lookups, thereby dramatically reducing query latency for large offsets as demonstrated by TPC‑H benchmarks.
This article explains the MySQL Index Merge access method, details the underlying merge‑sort algorithm, enumerates the three index‑merge strategies, analyzes source‑code snippets, presents a real‑world case where index merge hurts performance, and offers practical optimization recommendations.
This article presents a typical multi‑layer system architecture, examines the theoretical performance limits of each component—including load balancers, HTTP servers, Redis, message queues, relational databases and network bandwidth—and evaluates whether the combined stack can sustain one million concurrent requests.
This guide explains why slow queries occur in high‑traffic applications, outlines practical ways to monitor and fix them, and dives deep into using MySQL's EXPLAIN command to analyze index usage, interpret its output columns, and systematically improve query performance.
This article analyzes why a scalar subquery in an OceanBase 3.2.3.3 SQL statement runs over 1000 seconds, explains the execution plan details, and presents a rewrite using WITH and LEFT JOIN to replace SUBPLAN FILTER, reducing cost from 788 million to 3.6 million and execution time to 10 seconds.
GaiaDB 4.0 introduces a zero‑ETL parallel query engine that leverages multi‑core CPUs to run OLTP and OLAP workloads on a single cluster, dramatically improving complex query performance while offering configurable parallelism and a rich set of parallel operators.
This article explains why slow queries occur in high‑traffic applications, outlines effective index‑optimization steps, and demonstrates how to use MySQL's EXPLAIN command to analyze execution plans, interpret each column, and improve database performance.
This article compares MongoDB’s document‑oriented storage with PostgreSQL’s relational model for generative AI workloads, detailing how JSON, JSONB, and BSON affect insert and read performance across a series of benchmarks on identical hardware.
During a load‑test where QPS and TPS appear normal, the CPU usage climbs unexpectedly, leading to a step‑by‑step investigation that reveals excessive row reads caused by accumulating task records, and presents practical fixes and deeper insights into database read behavior.
This article explains nine common reasons why MySQL indexes become ineffective and offers concrete solutions—including query rewrites, data type alignment, index redesign, and maintenance tips—to help developers improve database query performance.
This article recounts a costly MySQL data‑migration failure caused by an unchecked INSERT INTO SELECT operation, analyzes why full‑table scans and transaction isolation led to payment‑record loss, and offers practical safeguards such as proper indexing and careful use of the statement.
This article recounts a real-world MySQL data migration incident where using INSERT INTO SELECT caused out‑of‑memory errors and data loss, analyzes why full table scans and transaction locking led to failures, and offers practical recommendations such as indexing and avoiding bulk inserts for large tables.
A developer’s costly mistake using MySQL’s INSERT INTO SELECT for large data migration led to OOM, full-table scans, and lost payments, revealing how transaction locks and missing indexes can cause production failures and how to prevent them.
A large‑scale MySQL table migration using INSERT INTO SELECT caused hidden full‑table scans and lock contention, leading to data loss and a costly OOM incident, but adding the proper index and understanding transaction isolation prevented the failure.
Optimizing SQL query performance hinges on confirming that the database engine fully leverages indexes, and the EXPLAIN statement provides a detailed execution plan—including key_len values—that shows whether each index field is completely used, enabling developers to identify and fix inefficient index usage.
This article explains MySQL's cost‑based optimizer, demonstrates how to calculate query costs for full‑table, covering, ref and range scans using actual source‑code constants, and applies the model to resolve index‑conflict cases in a store‑goods table, offering practical optimization guidelines and future tool ideas.
This article traces the evolution of tree‑based index structures—from simple binary search trees through AVL and red‑black trees to B‑trees and B+‑trees—explaining their strengths and limitations and why MySQL adopts B+‑trees to minimize disk I/O and boost query performance.
This article explains MySQL's architecture, how to view storage engines and variables, demonstrates creating various indexes, describes why SQL optimization is needed, details the EXPLAIN plan keywords and index types, and provides practical best‑practice tips for single‑ and multi‑table query performance improvements.
