This article explains InnoDB's transaction and index principles, covering ACID properties, transaction logs, MVCC, locking mechanisms, and B+ tree index structures to help backend developers understand how InnoDB ensures data consistency and high concurrency.
This article explains MySQL index fundamentals, demonstrates performance issues with slow queries, and walks through practical optimization cases using EXPLAIN, covering index creation, covering indexes, and composite indexes, and best practices for when to add or avoid indexes.
This article recounts a blockchain finance company's rapid user growth, the decision to move MySQL from Docker Swarm to Alibaba Cloud RDS, the ensuing CPU overload caused by missing indexes, and the step‑by‑step troubleshooting and optimization that restored system stability.
This article recounts a real‑world MySQL migration from Docker Swarm containers to an Alibaba Cloud RDS instance, detailing the original architecture, migration steps, backup scripts, a critical index‑missing outage, and the practical indexing strategies used to restore performance.
This article reviews common MySQL pitfalls—including engine choice, table partitioning, index design, slow‑query causes and optimizations, diagnostic tools, and how to recover from accidental data loss—offering practical guidance for developers to improve performance and reliability.
This article explains how QA engineers can identify and analyze MySQL slow query logs, configure slow query settings, and apply index optimization principles to improve database performance, providing practical commands, guidelines, and tool recommendations.
This article explains how to quickly locate and fix online MySQL problems such as partial update failures, InnoDB deadlocks, and lock‑wait timeouts by analyzing server logs, understanding InnoDB row‑lock behavior, optimizing indexes, adjusting transaction settings, and using a custom log‑collection script.
This article walks through a real‑world monitoring project that must store over 400 million rows per day in SQL Server 2012, detailing the performance bottlenecks, bulk‑copy tuning, schema redesign, partitioning strategies, index removal and creation, and query‑optimisation techniques that finally achieve sub‑second query times and meet strict latency requirements.
This article walks through practical MySQL optimization techniques—including configuration file tuning, key InnoDB parameters, effective index design, and the use of Percona and MySQL Tuner tools—to help you speed up queries and eliminate performance bottlenecks in production environments.
This article explains how MySQL processes ORDER BY, compares conventional, optimized, and priority‑queue sorting algorithms, shows how to leverage indexes, and provides practical tuning tips such as adjusting max_length_for_sort_data and sort_buffer_size to avoid costly temporary files and random I/O.
The article investigates why a seemingly indexed MySQL DELETE query still performs a full‑table scan, analyzes execution plans and statistics, demonstrates forcing the index, and finally rewrites the query to limit the date range, achieving a much faster operation.
A recent order‑fulfillment outage was traced to a MySQL query that used the primary‑key index because an ORDER BY id ASC LIMIT 350 forced a full scan, but tweaking the ORDER BY or removing it reduced execution time from 20 seconds to under one second.
This article explains common MongoDB connection problems, authentication mechanisms, read‑preference tuning, index types and creation, query‑plan analysis, pagination techniques, and array handling, offering practical tips to improve performance and reliability for developers.
This article explains eight typical situations in which Oracle SQL statements cannot use indexes, illustrates each case with concrete examples and step‑by‑step rewrites, and offers practical solutions—including predicate adjustments, function indexes, and query‑transformation fixes—to improve database performance.
This article explains eight common reasons why Oracle SQL statements fail to use indexes, provides concrete rewrite techniques, shows how optimizer bugs can block index usage, and outlines practical steps for enforcing SQL development standards through automated audit tools.
This article explains how to identify and analyze MySQL slow queries using tools like SHOW VARIABLES, EXPLAIN, mysqldumpslow, and profiling, and provides practical recommendations for configuring server variables, optimizing indexes, and improving query performance through systematic testing and best‑practice SQL design.
The article recounts a performance bottleneck in a Windows service that processed only about 12 records per second, analyzes why a simple SELECT TOP 1 query without proper indexes caused 89 ms latency, and demonstrates step‑by‑step how adding appropriate clustered and non‑clustered indexes reduced the execution time to virtually zero, while also discussing broader indexing strategies and common LEFT JOIN pitfalls.
This article demonstrates how PostgreSQL's Partial Index feature can be used together with BTREE and BRIN indexes to dramatically reduce storage while maintaining high query performance on both low‑frequency and high‑frequency data in a heavily skewed dataset.
This article explains what bookmark (key) lookups are in SQL Server, shows how they appear in execution plans, and presents three practical indexing techniques—clustered indexes, covering indexes with INCLUDE, and index intersection—to remove the extra I/O overhead.
This guide explains how to choose optimal MySQL data types, use enums, handle dates, and design efficient indexes—including B‑tree, prefix, and covering indexes—while also covering storage engine differences, maintenance commands, and normalization strategies to boost database performance.
This article reports a hands‑on experiment comparing single‑column and composite covered indexes in MySQL, measuring their storage overhead and insert latency on a million‑row table, and reflects on the importance of data‑driven reasoning versus intuition.
This article walks through practical Oracle SQL optimization methods—explaining execution plans, indexes, statistics, hints, and partitioning—using concrete examples and step‑by‑step case studies that show how to dramatically reduce query runtimes and CPU load.
This guide presents practical recommendations for MySQL and Oracle development, covering table design, appropriate data types, indexing best practices, and efficient SQL patterns to improve performance, maintainability, and security.
Although a users table has an index on the create_time column, a query spanning a broad date range triggers a full table scan, but narrowing the range enables the optimizer to use the index, illustrating how MySQL’s cost‑based optimizer decides between index lookup and table scan.
Distinct in MySQL works like GROUP BY but returns only one row per group; it can use index scans like GROUP BY, falls back to a temporary table when indexes aren't sufficient, and unlike GROUP BY it avoids sorting, making index optimization crucial for large result sets.
This article explains why GROUP BY requires sorting like ORDER BY, how a suitable index can satisfy both operations, and outlines the precise conditions—including single‑table queries, left‑most index prefixes, constant values, and limited aggregates—that allow MySQL to execute GROUP BY using an index scan.
This article explains how to identify table and index fragmentation in MySQL using the Data_free column, and provides two effective repair methods—altering the table engine and running OPTIMIZE TABLE—while warning about resource consumption and recommending a monthly maintenance schedule.
