A MySQL 5.6 database with three large tables suffered a 30,000‑second query when searching for students scoring 100 in a specific subject; by analyzing the execution plan, adding indexes on filter columns, and rewriting the query as a join, execution time dropped to under a second.
This article walks through a real‑world MySQL performance case where a nested sub‑query on millions of rows took over eight hours, explains why the plan used full table scans, and shows step‑by‑step index creation and query rewrites that cut the execution time to a millisecond.
This article examines common MySQL performance pitfalls such as inefficient LIMIT usage, implicit type conversion, suboptimal joins, mixed sorting, and unpushed predicates, and demonstrates eight practical rewriting techniques—including using max values, JOINs, WITH clauses, and early range reduction—to dramatically accelerate query execution.
The article explains why the Alibaba Java Development Manual restricts joins to three tables, discusses MySQL’s join algorithm limitations, and offers practical alternatives such as query decomposition, denormalization, and using IN or hash joins to improve performance.
Zhuanzhuan’s adoption of TiDB addressed sharding challenges and massive data storage needs, and the team shares six common issues encountered in large‑scale online deployments—including performance diagnosis, cluster management, log inconsistencies, slow‑SQL impact, optimizer limitations, and transaction conflicts—along with their standardized solutions for deployment, monitoring, alerting, and business rollout.
Understanding MySQL indexes—from hash tables, ordered arrays, and B+ trees—to their practical use cases, including clustered and secondary indexes, reveals why they boost query speed, when they may hinder performance, and how factors like selectivity, dirty page flushing, type mismatches, and function usage affect query efficiency.
OceanBase’s distributed relational database achieved the top TPC‑C benchmark score by leveraging cloud‑native resources, optimized storage, advanced transaction engines, and extensive SQL tuning, with detailed insights into preparation, system architecture, performance testing phases, ACID compliance, and storage optimizations that set new industry standards.
This article, the third in a five‑part series on OceanBase’s record‑breaking TPC‑C results, explains the SQL‑level challenges faced during the benchmark and details the optimizations—including stored procedures, LLVM‑based compilation, array binding, prepared statements, and updatable views—that enabled the performance gains.
This article demonstrates how to dramatically improve MySQL query performance by analyzing execution plans, adding appropriate single‑column and composite indexes, converting slow subqueries to efficient joins, and applying covering indexes and ordering optimizations, with detailed examples and timing results.
This article presents practical MySQL performance tricks—including smarter LIMIT usage, avoiding implicit type conversion, rewriting UPDATE/DELETE with JOIN, handling mixed ordering, replacing EXISTS with JOIN, pushing down conditions, narrowing result sets early, and leveraging WITH clauses—to dramatically reduce query execution times.
This article provides an in‑depth analysis of Stream SQL theory, incremental query algorithms, materialized view maintenance, optimizer cost models, time handling, windowing, and the practical capabilities and limitations of Apache Flink’s streaming SQL engine.
This article explains how to use MySQL's EXPLAIN and EXPLAIN EXTENDED commands to analyze query execution plans, interpret each column such as id, select_type, type, and extra, and apply practical optimization techniques like indexing, avoiding full‑table scans, and forcing index usage to improve SQL performance.
This guide presents nineteen essential MySQL optimization techniques—including effective use of EXPLAIN, proper handling of IN and EXISTS, avoiding SELECT *, limiting rows, optimizing ORDER BY, leveraging UNION ALL, employing full‑text indexes, and advanced join strategies—to help developers write faster, more efficient queries.
This article provides a comprehensive guide to improving Hive query performance by covering column and partition pruning, predicate pushdown, replacing ORDER BY with SORT BY, using GROUP BY instead of DISTINCT, fine‑tuning join operations, and optimizing MapReduce parameters such as mapper/reducer counts, file merging, compression, JVM reuse, parallel execution, strict mode, and storage formats.
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 guide outlines practical interview preparation techniques for Java backend roles, covering essential frameworks, distributed systems, database optimization, core Java concepts, Linux troubleshooting, and how to showcase real project experience to stand out from other candidates.
Through a step‑by‑step case study of a high‑traffic OLTP system, this article explains how evolving deletion logic—from simple full‑table scans to indexed intermediate tables and parallel processing—reduced daily expired‑data cleanup from several hours to under an hour, while addressing ORA‑01555 and clustering‑factor issues.
This article explains the fundamentals of relational optimization, the role of the optimizer, how CPU and I/O costs are estimated, and why accurate database statistics are essential for effective SQL performance tuning.
This article explains the fundamentals of relational optimization, the role of the cost‑based optimizer, how CPU and I/O costs are estimated, and why accurate database statistics are essential for generating efficient SQL access paths and improving overall query performance.
This guide shares actionable strategies for Java backend interview preparation, covering essential Java fundamentals, framework mastery, distributed system concepts, database optimization, Linux troubleshooting, and how to showcase real project experience to stand out from other candidates.
This article examines MySQL database fundamentals, including storage architecture, block-based data access, disk I/O bottlenecks, and detailed explanations of B+Tree and hash indexes in MyISAM and InnoDB, while offering practical guidance on index design, selectivity, left‑most prefix rules, and using EXPLAIN for SQL optimization.
This article summarizes UnionPay’s evolution of a distributed MySQL middleware platform, detailing its multi‑stage architecture, 2‑phase commit transaction handling, deadlock detection, SQL parsing optimizations, complex query strategies, and deployment considerations for high‑availability and performance.
This article explains 58.com’s database architecture, covering availability through replication and dual‑master setups, read‑performance enhancements with indexing, read replicas and caching, consistency solutions, rapid horizontal scaling methods, and a review of Codd’s twelve rules for relational design.
This article shares practical Java backend engineering advice—including module layout, code‑level conventions, SQL execution‑plan understanding, index usage, transaction handling, connection‑pool tuning—as well as personal work‑habit reflections and career‑development suggestions for developers.
Facing a 20‑million‑row master table and millions of rows in up to ten child tables, the article examines the inefficiencies of repeated IN‑clause queries and presents three alternative strategies—including a single‑SQL join, a UNION ALL view, and the use of global temporary tables—detailing their trade‑offs and experimental results.
SparkSql optimizes inner‑join queries by pushing predicates to the scan phase, allowing filters connected with AND to be applied before the join without changing results, while OR‑connected filters can be unsafe except when they involve the join key or partitioned tables which use partition pruning.
This guide walks through practical MySQL performance improvements—including using EXPLAIN, limiting IN lists, selecting specific columns, applying LIMIT 1, avoiding ORDER BY RAND(), preferring UNION ALL, proper index usage, forcing indexes, handling range queries, and optimizing JOINs—to help developers write faster, more efficient SQL statements.
This article explains how Oracle's logical structures—blocks, segments, tablespaces, and rowids—affect SQL performance, presents detailed experiments on block capacity, row migration, segment handling, tablespace behavior, and provides practical optimization guidelines based on observed logical read reductions.
This article compiles a comprehensive set of MySQL interview questions covering replication mechanisms, differences between MyISAM and InnoDB, character type nuances, transaction logging, binlog formats, performance tuning, backup strategies, monitoring, consistency checks, and handling large datasets, providing concise answers for each topic.
This article explains Oracle's architecture—including the SGA, Shared Pool, Data Buffer, and Log Buffer—shows how each component influences SQL parsing, bind variables, result caching, direct‑path inserts, batch commits and logging, and provides step‑by‑step experiments and practical techniques for diagnosing and tuning performance bottlenecks.
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 explains Oracle database performance tuning by introducing the five key performance reports—AWR, ASH, ADDM, AWRDD, and AWRSQRPT—detailing how to obtain them, what metrics to focus on, and how to apply them in real‑world case studies for both simple and complex optimization scenarios.
This article examines common scenarios where duplicate rows prevent creating unique constraints, presents eight SQL‑based methods—including array functions, window functions, NOT IN/EXISTS, combined IN/NOT IN, EXISTS/NOT EXISTS, single‑statement deletes, and table‑copy approaches—provides test data, performance comparisons, and practical guidance for DBA operations.
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 article compiles 38 practical Oracle SQL tuning techniques—from ordering tables and rewriting WHERE clauses to using indexes, avoiding SELECT *, leveraging DECODE, replacing HAVING with WHERE, and preferring TRUNCATE over DELETE—each illustrated with clear examples to help developers write faster, more efficient queries.
When migrating applications from Oracle to MySQL, many teams encounter severe performance problems such as CPU saturation, high logical and physical reads, and slow queries, and this article outlines the root causes—including lack of parallelism, limited join algorithms, IN‑list limits, subquery inefficiencies, missing materialized views, and unsupported function indexes—and provides concrete remediation strategies for each issue.
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.
This article examines common MySQL performance pitfalls such as inefficient LIMIT usage, implicit type conversion, subquery‑driven updates, mixed ordering, EXISTS clauses, condition push‑down, early result narrowing, and intermediate result push‑down, and provides rewritten SQL examples that dramatically reduce execution time from seconds to milliseconds.
After migrating a core system from AIX to Linux and upgrading Oracle from 11.2.0.3 to 11.2.0.4, a critical SQL that previously ran in milliseconds began taking hundreds of seconds, prompting a detailed investigation of execution plans, CBO limitations, and three successive rewrite strategies that restored performance.
Analyzing reading statistics of 23 original articles published before the 2017 Chinese New Year, this report reveals that SQL on Hadoop, Lambda architecture, and Docker+Jenkins were the three hottest big‑data topics, while also discussing the rise of Kappa, SQL optimization importance, and ongoing innovation in the field.
This guide presents comprehensive Oracle database performance optimization techniques—including data structure design, application architecture, SQL tuning, memory and CPU adjustments, I/O balancing, and essential tools—providing actionable steps and example queries for DBAs to improve system responsiveness and efficiency.
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 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 guide explains how to clean redundant fields, restructure a massive MySQL table, batch‑fetch data with LIMIT, rewrite slow queries, and compare three insertion methods—including prepared statements and bulk inserts—to cut migration time from hours to under 20 minutes.
An experienced DBA shares a real-world case of optimizing a nightly Oracle reporting pipeline, detailing how partitioned tables, truncate operations, table compression, disabling logging, and parallel execution reduced a 20‑hour data extraction to under one hour, and compiles community‑sourced tuning tips for large‑scale data warehouses.
The article explains MySQL's external and internal temporary tables, describes ten scenarios where internal temporary tables are created—including hints, derived tables, ORDER BY, GROUP BY, IN‑subqueries, UNION and aggregates—and provides example SQL statements with EXPLAIN JSON output to illustrate performance impacts.
This guide presents practical MySQL and Oracle development recommendations, covering table design, indexing, and SQL optimization techniques such as using appropriate data types, avoiding redundant indexes, employing efficient query patterns, and leveraging specific Oracle hints to improve performance and maintainability.
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.
This article explains how to read DB2 execution plans by focusing on data‑access methods and join strategies, walks through real‑world examples of full‑table scans, index scans, various join types, semi‑joins, and join‑order selection, and answers common practitioner questions.
A slow Oracle OLTP query that consumed 56% of DB resources and averaged 28 seconds per execution was dramatically accelerated to millisecond response time by forcing the proper index, refreshing stale table statistics, and ultimately creating a cn + c_date composite index.
This guide walks through identifying MySQL CPU overload caused by excessive temporary tables and thread creation, then details configuration tweaks and SQL optimizations—including adjusting tmp_table_size, thread_cache_size, and join/sort buffers—to restore normal performance.
This article explains MySQL’s internal architecture, covering the client connector, connection management, SQL processing layers—including parsing, optimization, and caching—and the pluggable storage engine, as well as system management tools such as backup, recovery, security, and replication.
This article explains the fundamentals of B+Tree indexes, why they matter for SQL performance, and presents three concise indexing techniques—single, composite, and covering indexes—to reduce resource consumption, I/O, and sorting overhead in relational databases.
