This article explains how DBA‑focused MySQL query optimization can be accelerated using a Python tool that automates EXPLAIN analysis, statistics collection, optimizer switches, and profiling, while providing step‑by‑step guidance, configuration details, and sample command usage.
This article shares practical MongoDB indexing guidelines—including compound index rules, naming limits, cardinality considerations, TTL and geospatial indexes, lock behavior, and real‑world case studies—to help DBAs and developers design efficient, safe indexes for production workloads.
This guide walks DBAs through evaluating current database resources, establishing performance baselines, building business pressure models, conducting realistic stress tests, and applying a seven‑layer optimization roadmap—from statement tweaks to hardware upgrades and business‑level adjustments—to ensure the system can handle ten‑fold or hundred‑fold growth.
This article explains Oracle cursor concepts, details the key V$SQL and related views, highlights crucial columns for performance tuning, and describes important parameters such as open_cursor and cursor_sharing, helping DBAs optimize SQL execution and manage shared pool memory effectively.
This guide explains why data delay hurts MySQL replication, how MySQL 5.7’s logical‑clock multi‑threading improves throughput, and provides a detailed, command‑line walkthrough to configure, start, and verify multi‑threaded replication on a slave server.
This article critically examines Uber’s claim that MySQL outperforms PostgreSQL by dissecting the alleged PostgreSQL limitations, presenting detailed performance tests, and clarifying the true costs of write amplification, replication, and upgrade processes for both databases.
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 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 MySQL’s indexing fundamentals, the left‑most prefix rule, B+‑tree structure, disk I/O considerations, and provides step‑by‑step methods and real‑world cases for diagnosing and optimizing slow queries in production systems.
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.
Optimistic locking replaces heavyweight pessimistic locks with a version‑based approach, allowing concurrent reads and only checking a version or timestamp during updates, which reduces lock contention, improves performance under high concurrency, but requires careful handling of external updates to avoid dirty data.
Database PerformanceOptimistic Lockingconcurrency control
This article explains the advantages and disadvantages of database indexes, outlines practical principles for their use, demonstrates how to analyze index effectiveness with MySQL's EXPLAIN command, and lists common situations that cause index loss, providing clear guidance for performance optimization.
SQL Server’s parallel query execution leverages multiple CPUs to accelerate CPU‑bound workloads, and this article explains the underlying hardware prerequisites, scheduler/task/worker model, serial vs parallel plans, exchange operators, parallel joins, CXPACKET waits, and configuration settings such as cost threshold and max degree of parallelism.
This article explains Oracle's parallel query feature, covering its benefits, resource costs, required conditions, various data‑distribution methods such as broadcast, replicate and hash, how to read parallel execution plans, and practical monitoring techniques to avoid performance pitfalls.
The article explains how a seemingly harmless SQL statement, when executed millions of times on an IBM Power780 system with a 1 TB configuration, can overload the library cache mutex, causing the database to stall, and provides the original script, reproduction steps, and practical advice to avoid such catastrophic contention.
This article explains three practical techniques—batching multiple rows into a single INSERT, wrapping inserts in transactions, and inserting records in primary‑key order—to significantly improve MySQL InnoDB insert performance, backed by detailed test results and important configuration tips.
This article examines the performance bottlenecks of traditional relational databases, explains how SQL Server 2014's In‑Memory OLTP (Hekaton) uses latch‑free Bw‑tree structures, optimistic timestamp‑based concurrency, and native compilation to achieve orders‑of‑magnitude speedups, and discusses its practical limits and real‑world case studies.
This article explains how to optimize Oracle distributed queries that involve remote tables by minimizing remote calls, reducing result set size, and improving execution plans through techniques such as collocated inline views, CBO behavior, and driving_site hints, illustrated with detailed examples and performance measurements.
This article explains MySQL's query cache mechanism, covering its workflow, configuration parameters, cache‑management commands, internal data structures, experimental verification steps, and practical recommendations for when to enable or disable the feature.
This article explains how implicit lock requests in PostgreSQL can lead to surprising lock waits, demonstrates debugging steps with lock_debug and a reporting script, and offers practical tips like lock_timeout and statement_timeout to prevent prolonged blocking scenarios.
This guide explains how to access and use Oracle SQL Monitor—a free feature in Oracle Enterprise Edition—to capture SQL execution details, analyze performance bottlenecks, compare estimated and actual rows, and apply targeted optimizations using both the EM console and command‑line scripts.
This article explains the inner workings of the DB2 optimizer, its four-step processing flow, cost‑based decision making, and detailed examples comparing full‑table and index scans, followed by practical tuning tips and a Q&A session for real‑world query optimization.
This article explains how MySQL versions up to 5.5 process and optimize views, outlines the exact conditions required for view merge rewriting, compares derived table handling with the SQL standard, and provides concrete test cases to illustrate performance pitfalls.
An Oracle RAC 11g installation on AIX failed intermittent queries with ORA‑04030 errors, and a deep dive revealed that missing shell limits for the grid user capped process memory at 128 MB, causing PGA allocation failures despite ample system resources.
This article explains how MySQL's query cache works, why it uses the full SQL statement as a key, and lists ten typical situations—such as using SQL_NO_CACHE, volatile functions, or locks—that prevent a query from being cached.
This article explains how to achieve asynchronous MySQL queries in PHP by leveraging mysqlnd's async functions and PHP generators, compares synchronous and asynchronous execution times, provides a complete coroutine‑based example, and discusses performance considerations and limitations.
In this expert Q&A, two leading Chinese database specialists discuss why MySQL’s major releases lag behind Oracle’s, examine the influence of Alibaba on MySQL’s domestic growth, and explore the broader implications for the Chinese open‑source database ecosystem.
Chinese Tech IndustryDatabase PerformanceDatabase Versioning
MySQL’s Query Cache stores the results of SELECT statements by hashing the query, keeping the result set in memory, and invalidating cached entries whenever related tables change, allowing identical queries to be served instantly without re‑execution.
Learn practical techniques to reduce nested loop iterations, prioritize inner-loop optimization, ensure join columns are indexed, and configure join buffers effectively, so your SQL joins run with minimal resource consumption and maximum performance.
MySQL uses only the Nested Loop Join algorithm, iterating each row of the left table and comparing it with every row of the right table, and when multiple tables are involved the intermediate results are repeatedly joined, making proper indexing crucial for performance.
The article analyzes a slow PostgreSQL query that joins multiple tables to fetch flight numbers, compares several alternative query formulations with their execution plans, and presents a final solution using array aggregation to achieve sub‑millisecond performance despite existing indexes.
This article presents a practical performance comparison between TokuDB and InnoDB, covering compression ratio, insert speed, online DDL operations, CPU usage, IOPS, and response time, and concludes with the advantages and drawbacks of adopting TokuDB in production.
In this interview, veteran Oracle DBA Shi Yuedong shares his career journey, personal philosophy on opportunity, a hands‑on performance‑diagnosis case at Lenovo, experiments revealing Oracle 12.1's default statistics sampling rate, and thoughtful perspectives on big data versus data‑warehouse evolution.
This article explains what MySQL table partitioning and sharding (splitting tables) are, compares their implementation, data handling, performance impact, and difficulty, and shows how they can complement each other for high‑traffic, large‑data scenarios.
By adding a lightweight diagnostic box that displays each page’s database query count and total execution time, developers can quickly spot inefficient queries, compare development and production performance, and make informed optimizations, ultimately improving website speed and reliability.