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.
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 technical guide explores Oracle 12c's optimizer enhancements, covering adaptive query optimization, adaptive plans and join methods, hybrid hash distribution, dynamic and adaptive statistics, automatic re‑optimization, statistics feedback, new histogram types, and online statistics collection, with practical examples and visual illustrations.
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.
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.
An expert panel from the High‑Efficiency Operations community answers eleven pressing MySQL 5.7 questions, covering test hardware, GA timeline, GIS speed, virtual column indexing, materialized view plans, 2‑D geo support, performance monitoring, Oracle’s market strategy, geojson handling, multi‑source replication, buffer‑pool locking, and InnoDB Memcached consistency.
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.
