Although many developers hear the advice to avoid using SELECT * in SQL, this article delves into the underlying reasons—such as increased parsing cost, unnecessary data transfer, extra I/O, and loss of covering-index optimization—while also explaining index concepts and best practices for MySQL performance.
This article explains how to analyze and tune MySQL join queries with EXPLAIN, addressing issues like using temporary tables and filesort, and provides practical techniques—including STRAIGHT_JOIN and index strategies—to achieve sub‑second response times on tables with millions of rows.
This article explains how to use EXPLAIN to analyze MySQL join queries, identifies causes of temporary tables and filesorts, and provides practical techniques—including driver‑table selection, STRAIGHT_JOIN usage, and index strategies—to optimize two‑ and multi‑table joins so that million‑row results return within two seconds.
This article explains MySQL's Multi-Range Read (MRR) optimization, demonstrates its performance impact through experiments, shows how to enable or disable MRR via optimizer_switch, and provides guidance on tuning related parameters such as mrr_cost_based and read_rnd_buffer_size for optimal query execution.
This article presents a comprehensive guide to MySQL performance tuning, covering bulk‑insert optimizations, efficient INSERT statements, GROUP BY and ORDER BY improvements, OR‑condition handling, nested query replacement, and the use of SQL hints such as USE INDEX, IGNORE INDEX, and FORCE INDEX, with practical code examples and benchmark results.
This article explains how to configure JVM memory, choose appropriate garbage‑collector settings, tune HBase region split policies, optimize BlockCache implementations, and select suitable compaction strategies to improve HBase performance on clusters of various sizes.
This article chronicles the ten‑year evolution of OceanBase, a distributed relational database created by Sun Zhenkun and his team, detailing early challenges, technical breakthroughs, benchmark victories like TPC‑C world records, and its impact on Alibaba’s financial systems.
A developer used INSERT‑INTO‑SELECT to migrate millions of rows, unintentionally locking the source table and causing massive payment failures, but adding an index and forcing its use resolved the issue and restored safe migration.
The article examines why MySQL queries become slow, detailing system‑level issues such as network latency, I/O bottlenecks, and CPU settings, alongside database‑level problems like missing indexes, type conversion, poor execution plans, lock contention, skewed data, and inefficient SQL patterns, and offers optimization strategies.
This article examines MySQL column attributes—especially NULL vs NOT NULL handling, the impact of NULL on concatenation, COUNT and indexing, as well as AUTO_INCREMENT behaviors, lock modes, step/offset settings, and overflow scenarios—to help developers write more efficient and reliable SQL.
This article examines why MySQL instances are often targeted by the Linux OOM killer, explains proper InnoDB buffer pool sizing, introduces memory leak concepts, and demonstrates how to use Valgrind's Memcheck tool to detect leaks and improve database memory management.
This article explains how to inspect and clear Linux memory buffers, interprets the fields shown by the free command, and then dives into SQL join types, performance‑impacting factors such as join_buffer_size, and practical optimization techniques like indexing and denormalization.
This article explains how to identify slow MySQL queries using the slow‑query log and SHOW PROCESSLIST, configure logging parameters, and analyze query performance with EXPLAIN, covering key output fields, common values, and practical code examples for effective SQL optimization.
This article explains how MySQL computes replication delay using show_slave_status, analyzes the relationship between binlog write timestamps and event generation, enumerates common causes of lag, and provides a detailed examination of the system‑lock state in the slave SQL thread with code examples.
This article explains how Linux memory statistics (free, top) report buffer/cache usage, how to clear it with sync and drop_caches, and then dives into SQL join concepts, types, optimization techniques, and MySQL join algorithms such as nested loop and block nested loop, highlighting their impact on performance.
This article explains MySQL index design principles, demonstrates the execution steps of a range query on a sample table, and discusses common index optimizations such as covering indexes, the left‑most prefix rule, index condition push‑down, implicit type conversion, as well as why the optimizer may choose a wrong index and how to correct it.
The article introduces HotRing, a hotspot‑aware in‑memory key‑value store from Alibaba's Tair team, explains the extreme traffic challenges of Alibaba's Double‑11 peak, details the design of ordered‑ring hashing, dynamic hotspot detection, lock‑free concurrency and rehashing, and shows how these innovations achieve up to 2.58× higher throughput than existing KV systems.
This article explains the essential role of MySQL REDO logs in ensuring data durability and performance, describes what would happen without logging, details various REDO log record formats, and outlines the multiple moments when InnoDB flushes logs to disk.
This article explores common MySQL performance pitfalls such as inefficient LIMIT usage, implicit type conversion, subquery handling, and mixed sorting, and demonstrates eight practical rewrite strategies—including index‑friendly pagination, JOIN replacements, condition pushdown, and WITH clauses—to dramatically reduce query execution times from seconds to milliseconds.
This article explains how MySQL executes COUNT(*) and COUNT(column) queries on InnoDB tables, showing example results, detailing each layer's processing steps, the handling of NULL values, and the subtle performance differences between the two forms.
This article analyzes ten typical situations that cause MySQL indexes to become ineffective—such as OR conditions, missing quotes, leading wildcards in LIKE, composite‑index ordering, built‑in functions, arithmetic operations, inequality operators, NULL checks, charset mismatches, and optimizer choices—and provides concrete examples, visual explanations, and practical solutions to restore index usage.
This article explains MySQL's three‑layer logical architecture, the end‑to‑end query execution flow—including client/server protocol, query cache, parsing, cost‑based optimization, execution engine, and result delivery—followed by practical performance‑tuning advice on schema design, data types, index creation, and specific query optimizations such as COUNT(), JOINs, LIMIT pagination, and UNION handling.
ScyllaDB, a high‑performance C++ implementation of Apache Cassandra, introduces row‑level repair to replace the traditional partition‑level repair, reducing data transfer and I/O by operating on individual rows; the presentation details its architecture, multi‑stage process, experimental results, and the resulting six‑fold speedup.
This article explains how MySQL composite (multi‑column) indexes work, the left‑most prefix rule, demonstrates queries where the index is used or ignored, and lists common situations that cause index failure along with practical optimization tips.
This article explains MySQL's logical architecture, query execution steps, client‑server protocol, query cache behavior, cost‑based optimizer mechanics, and practical index design strategies—including B+Tree fundamentals, covering indexes, and common pitfalls—to help developers achieve measurable performance improvements.
MySQL 8.0 introduces Hash Join for dramatically faster large‑dataset queries, supports in‑memory and spill‑to‑disk processing, can replace Block Nested‑Loop joins, and adds EXPLAIN ANALYZE with detailed runtime statistics and tree‑style output, highlighting the platform’s rapid evolution.
This article explains how MySQL's InnoDB engine marks rows as deleted without freeing disk space, demonstrates the issue with 100,000 rows, and shows how the OPTIMIZE TABLE or ALTER TABLE commands can rebuild the table to reclaim space and improve performance.
This article investigates a MySQL slow query caused by a subquery that triggers a full‑table scan, explains MySQL's query rewrite behavior, and shows how Django's lazy ORM evaluation can worsen performance, offering practical fixes using explicit query execution or join rewrites.
An objective performance comparison between YugabyteDB (v1.3.0.0) and CockroachDB (v19.1.3) was conducted using the official test scenarios, covering write, read, mixed, secondary index, and transactional workloads on a three‑node Docker environment, with analysis of results and conclusions on their relative strengths.
This article analyzes Redis 6's newly introduced multithreaded I/O feature, explains its design and code implementation, presents benchmark configurations and results that show near‑doubling of GET/SET throughput, and discusses practical considerations and limitations of the approach.
This article investigates Alibaba's rule against joining more than three tables by designing MySQL and Oracle experiments that generate massive student‑teacher‑course data, run multi‑table join queries, compare indexed versus non‑indexed scenarios, and reveal the performance limits that drive the guideline.
This article introduces SQL fundamentals, explains DDL/DML/DCL categories, and provides practical PostgreSQL query optimization techniques—including index selection, condition rewriting, sub‑query removal, expression and partial indexes, DDL decomposition, CTE usage, and key configuration parameters—to help developers and DBAs improve performance and maintainability.
This article explains how SQL optimizers choose join methods and table order, compares nested loop and merge joins, outlines factors influencing table selection, and offers practical guidance for improving multi‑table query performance.
This article walks you through using MySQL's EXPLAIN command to analyze query execution plans, explains each column in the output, shows how to interpret IDs, select types, tables, access types, possible and actual indexes, key lengths, row estimates, and extra information, and provides practical code examples for better performance tuning.
This article explains why MySQL master‑slave replication lag occurs, why it matters for data consistency and high‑availability, and provides six real‑world case studies with root‑cause analysis and practical solutions to eliminate the delay.
During a client’s upgrade test, a database’s compressed tables exhibited severe slowdown under concurrent full‑table scans, which we traced to CPU cache line false sharing in the decompression code; using Linux perf tools we identified the hotspot, aligned memory, and restored performance.
This article walks through MySQL's ORDER BY execution, detailing the full‑field sorting process, when MySQL switches to row‑id sorting due to large row sizes, and how composite and covering indexes can optimize or eliminate the sorting step.
This article explains MySQL's three lock types, the storage engines that use them, detailed MyISAM and InnoDB lock behaviors, gap‑lock pitfalls, optimization tips, and how to monitor lock contention with built‑in status variables.
This article investigates why slow‑query logs show numerous slow queries without visible SQL statements, reproduces the problem using MySQL client, Perl and PHP simulations, compares local (emulated) and server‑side PDO prepare modes, and provides practical recommendations for configuring prepared statements to avoid hidden performance bottlenecks.
This article explains how MySQL 8.0 redesigns the redo log with lock‑free mechanisms, describes the asynchronous thread architecture, details the mtr workflow and synchronization structures, and lists the new tunable parameters that improve write performance and checkpoint efficiency.
This article examines the common causes of MySQL master‑slave replication delay, explains how to monitor the issue with SHOW SLAVE STATUS, and provides practical solutions for six typical scenarios ranging from heavy write traffic to hardware mismatches.
To resolve a MySQL single‑core CPU bottleneck, examine and tune slow queries and indexes, eliminate heavy lock contention, break large transactions into smaller ones, and employ tools like perf top to pinpoint performance hotspots, as recommended by Tencent Cloud Database expert Liu Di.
The article shares practical lessons from dozens of multi‑industry SQL audit projects, outlining the audit scope, four core scenarios—production optimization, peak‑load reduction, pre‑production interception, and development compliance—along with detailed processes, common pain points, and strategies for effective implementation.
This article explains how relational optimizers validate, analyze, and transform SQL statements into executable code, detailing the four-step optimization process, query‑analysis factors, cost‑based access‑path selection, table scans, index lookups, and the role of data prefetching in improving performance.
This article walks through a real‑world ERP database performance case, detailing the symptoms, metric analysis, root‑cause investigation across hardware, environment, and code, and a three‑phase optimization strategy that dramatically reduced latency and improved system stability.
This article explains how MySQL decides whether an index can be used for range queries, LIKE patterns, and sorting, detailing key_len calculation, index key vs. filter vs. table filter concepts, and practical examples with BETWEEN and ORDER BY.
This article details Alibaba's journey from the 2017 pilot of storage‑compute separation to the 2018 large‑scale deployment of the DBFS user‑space file system, highlighting innovations such as zero‑copy I/O, RDMA integration, adaptive page cache, asynchronous I/O, atomic writes, online resize, and hardware‑software co‑design that enabled elastic, high‑performance database operations during the Double‑11 shopping festival.
This article demystifies MySQL query optimization by explaining the server's logical architecture, the end‑to‑end query processing flow, the role of the client/server protocol, query cache, parsing, optimizer, execution engine, and result delivery, and then offers concrete performance‑tuning recommendations on schema design, data types, indexing strategies, and specific query patterns such as COUNT(), JOIN, LIMIT pagination, and UNION.
This article explains how MySQL indexes work, why queries can become slow, how to interpret the EXPLAIN output—including id, select_type, table, type, possible_keys, key, key_len, ref, rows, and extra columns—and provides practical examples and optimization cases to improve query performance while balancing the cost of maintaining indexes.
The article explains how to generate and interpret Oracle Automatic Workload Repository (AWR) reports, detailing key sections such as DB Time, Cache Sizes, Load Profile, parsing behavior, instance efficiency percentages, shared pool statistics, and top wait events to diagnose database performance issues.
This article presents thirty practical SQL tuning guidelines—ranging from avoiding certain WHERE‑clause operators and functions to proper index usage and set‑based alternatives—that help prevent full table scans and improve overall database performance.
The article explains MySQL’s thread‑pool technology—its motivation, architecture of listener, worker, timer threads and priority queues, key configuration variables, how to enable it, and common pitfalls such as memory leaks, monitoring timeouts, and group‑level saturation, offering practical guidance for DBAs.
This article examines a high‑volume Oracle query scenario, explains why using multiple IN clauses on millions of rows leads to costly full‑table scans, and presents three alternative strategies—including a single‑SQL join, UNION ALL view, and temporary‑table approaches—along with practical examples and performance considerations.
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 provides a comprehensive technical overview of InnoDB's bulk index build feature introduced in MySQL 5.7, detailing its design, fast index build phases, bottom‑up construction, dirty‑page flushing, compressed‑table handling, fill‑factor configuration, and potential future optimizations.
The article explains the limitations of relational databases for large‑scale, highly connected data, introduces NoSQL and graph database models, demonstrates how graph queries efficiently retrieve multi‑degree social connections, and showcases Neo4j’s performance advantages over traditional RDBMS.
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 explains how Percona Server 5.7 implements a thread‑pool model for MySQL, detailing the activation parameters, internal architecture, functional threads, timer mechanisms, and several performance optimizations such as priority scheduling and special handling for long‑running commands.
This article explains MongoDB indexing fundamentals, covering B‑Tree structures, index selectivity, unique and compound indexes, practical code examples, and guidelines to optimize query performance and reduce I/O operations.
This article explains MySQL's lock types across storage engines, details MyISAM and InnoDB locking behaviors, describes the four lock queues, outlines gap‑lock characteristics, and provides practical optimization and monitoring techniques to reduce lock contention.
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.
This article explains what raw (裸) devices are, how to identify and use them with Oracle on Unix, their performance advantages, when they should be employed, and practical tips for configuration, backup, and related storage considerations.
This guide walks you through installing Ubuntu, Docker, and the Percona Monitoring and Management (PMM) server, configuring a PMM client, and connecting it to MySQL to enable real‑time performance metrics and slow‑query analysis.
This article explains what MySQL's slow query log is, when it is generated, the key configuration parameters, how to interpret its output, useful analysis utilities, and best practices for cleaning and backing up the log files.
This article explains the inner workings of MySQL B‑Tree indexes, how the optimizer chooses the cheapest execution plan, demonstrates practical examples of index matching, cost calculation, and EXPLAIN analysis, and provides step‑by‑step troubleshooting techniques for real‑world performance issues.
This article provides a comprehensive guide to Oracle performance tools—including AWR, ASH, ADDM, AWRDD, and AWRSQRPT—explaining how to obtain and interpret their reports, key metrics to monitor, and real‑world case studies that illustrate effective database optimization for both simple and complex scenarios.
This article compiles a DBAplus online session that presents a systematic SQL‑optimization methodology, rewrite tricks, common pitfalls, and a series of concrete Oracle examples—including CASE WHEN, ROWNUM pagination, hint usage, column selection, function‑call reduction, trigger avoidance, and ROWID access—plus a detailed Q&A that clarifies practical tuning questions.
This guide outlines practical principles for creating Oracle indexes, compares B‑Tree and bitmap index types, explains their creation syntax, highlights performance characteristics, and lists common situations where indexes may be ignored or unsuitable.
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 what constitutes a database performance problem, outlines essential metrics such as response time, throughput, baseline and bottleneck identification, and presents practical tuning methods—including workload reduction, resource configuration, and optimization balance—to help engineers systematically improve database efficiency.
An in‑depth comparison of MySQL’s default storage engines—MyISAM and InnoDB—covers their evolution, core features, performance benchmarks, locking mechanisms, transaction support, file structures, and practical guidance on choosing the right engine for different workloads.
This article explains SQL Server’s simple and forced parameterization modes, shows how automatic parameterization can cause plan reuse and inaccurate row‑count estimates, and demonstrates how to detect and resolve these issues using OPTION(RECOMPILE) and proper indexing.
When a critical Oracle query suddenly slowed down on regional databases while the central server remained fast, I diagnosed the issue, discovered execution‑plan differences caused by missing predicate push‑down, attempted a plan‑binding solution, faced unexpected failures, and ultimately rewrote the SQL using a WITH clause to meet a tight deadline.
This article recaps a DBAplus community session where a seasoned DBA shares common operational scenarios, diagnostic techniques, lock‑handling strategies, and step‑by‑step SQL optimization methods, while unveiling the DPM platform that automates inspection, data collection, and performance analysis for Oracle databases.
This guide explains how to launch SQL Server 2008 Profiler, describes its key columns such as CPU, Duration, Reads, Writes, and shows how to filter traces by SPID, enabling performance comparison and session-specific monitoring.
ScyllaDB, a Cassandra‑compatible NoSQL database, achieves over tenfold performance improvements through a thread‑per‑core design, asynchronous I/O, custom caching, self‑tuning schedulers, a user‑space TCP/IP stack, and LLVM‑JIT query execution, making it a compelling study for high‑performance database engineering.
During high‑concurrency flash‑sale events, Oracle’s log file sync became a performance bottleneck; the article analyzes storage, OS, and Oracle Disk Manager factors, presents AWR metrics, demonstrates tuning steps—including disabling adaptive log file sync and enabling ODM—and shows measurable latency reductions.
This article examines a high‑volume Oracle query scenario, critiques the current multi‑IN approach, and presents three alternative solutions—including a single‑SQL join, a UNION ALL view, and the use of global temporary tables—detailing their performance implications and practical trade‑offs.
This article enumerates Oracle's most common wait events, explains the underlying reasons each event occurs, lists the associated parameters, provides sample queries for inspection, and offers practical guidance on diagnosing and mitigating performance issues caused by these waits.
This comprehensive guide explains Oracle's Automatic Workload Repository (AWR), how snapshots are collected and retained, and provides step‑by‑step instructions for interpreting the most common AWR report sections, from cache sizes and load profiles to SQL statistics and RAC metrics, helping DBAs pinpoint bottlenecks and improve performance.
This guide walks through Oracle's memory architecture, explains SGA and PGA components, details essential memory parameters with recommended values, and provides step‑by‑step SQL checks to diagnose and tune performance on both 32‑bit and 64‑bit systems.
This article explains Oracle 12c's key memory‑related enhancements—including the extended database cache (Smart Flash Cache), PGA aggregate limit, In‑Memory column store with compression, SIMD processing, temperature‑based replacement, and big‑table cache—detailing their parameters, operation, and monitoring methods for improved database performance.
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.
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.
