SQL performance

Selected Java Interview Questions

May 20, 2025 · Databases

Understanding Index Condition Pushdown (ICP) in MySQL: Theory, Usage, and Performance Comparison

Index Condition Pushdown (ICP) is a MySQL 5.6 feature that pushes part of WHERE filtering to the storage engine, reducing row reads and I/O; this article explains its principles, activation, usage examples, performance testing, and conditions under which ICP can be applied.

Database OptimizationICPIndex Condition Pushdown

0 likes · 10 min read

Understanding Index Condition Pushdown (ICP) in MySQL: Theory, Usage, and Performance Comparison

Aikesheng Open Source Community

May 13, 2025 · Databases

Visualizing MySQL Execution Plans with Flame Graphs

This article explains how to use Flame Graphs to visualize MySQL execution plans, discusses the limitations of traditional EXPLAIN output, introduces the EXPLAIN ANALYZE feature in MySQL 8.0, provides sample SQL and command‑line usage, and shows how the visual tool helps quickly identify performance bottlenecks.

Database OptimizationExplain AnalyzeFlame Graph

0 likes · 11 min read

Visualizing MySQL Execution Plans with Flame Graphs

JD Tech Talk

Apr 21, 2025 · Databases

Optimizing Supply Chain Planning Systems: ClickHouse ReplacingMergeTree and Local Join Solutions

This article discusses solutions to system bottlenecks in supply chain planning business development, focusing on ClickHouse ReplacingMergeTree table creation, local join optimization, and real-time data synchronization between TiDB and ClickHouse to improve query performance and system stability.

ClickHouseDatabase OptimizationLocal Join

0 likes · 10 min read

Optimizing Supply Chain Planning Systems: ClickHouse ReplacingMergeTree and Local Join Solutions

Aikesheng Open Source Community

Mar 20, 2025 · Databases

How to Interpret the rows Value in MySQL EXPLAIN Output

This article explains what the rows column in MySQL EXPLAIN means, demonstrates three situations—when a small rows value indicates good performance, when it does not, and when rows is misleading—using concrete SQL examples, force‑index tricks, and multi‑table join analyses to show why rows alone cannot reliably judge query efficiency.

EXPLAINIndexesMySQL

0 likes · 10 min read

How to Interpret the rows Value in MySQL EXPLAIN Output

DataFunSummit

Jan 9, 2025 · Big Data

Spark SQL Window Function Optimizations: Concepts, Techniques, and Q&A

This article explains Spark SQL's window function fundamentals, introduces two key optimizations—Offset Window Frame and Infer Window Group Limit—and provides a detailed Q&A covering implementation details, execution plan impacts, and underlying architecture.

Apache SparkBig DataOptimization

0 likes · 13 min read

Spark SQL Window Function Optimizations: Concepts, Techniques, and Q&A

Ctrip Technology

Nov 21, 2024 · Big Data

Performance Governance and Optimization of Ctrip's Nova Data Reporting Platform

This article details the performance challenges of Ctrip's Nova data reporting platform and describes a series of governance measures—including multi‑dimensional data caching, materialized view acceleration, query strategy optimization, and SQL quality improvements—that collectively reduced average query latency by over 50% and stabilized the system.

Big DataCachingQuery Optimization

0 likes · 26 min read

Performance Governance and Optimization of Ctrip's Nova Data Reporting Platform

Efficient Ops

Aug 9, 2023 · Databases

Elevating DBA Skills: Practical Insights from a Bank’s Database Team

This article explores how bank DBAs continuously improve their capabilities through standardized daily inspections, automated environment provisioning, careful upgrade planning, development support, and disciplined incident response, while also encouraging a broader understanding of diverse database technologies and core relational concepts.

AutomationBackup and RecoveryDatabase Administration

0 likes · 9 min read

Elevating DBA Skills: Practical Insights from a Bank’s Database Team

macrozheng

Mar 20, 2023 · Backend Development

Boost MyBatis Batch Insert Speed: Avoid foreach Pitfalls and Use ExecutorType.BATCH

This article explains why MyBatis foreach‑based batch inserts become extremely slow with thousands of rows, demonstrates how the generated giant SQL statement hurts performance, and shows how switching to ExecutorType.BATCH or limiting rows per INSERT dramatically reduces insertion time.

ExecutorType.BATCHJavaMyBatis

0 likes · 9 min read

Boost MyBatis Batch Insert Speed: Avoid foreach Pitfalls and Use ExecutorType.BATCH

Laravel Tech Community

Feb 16, 2023 · Databases

DISTINCT vs GROUP BY in MySQL: Performance and Usage Guide

This article explains the differences between DISTINCT and GROUP BY in MySQL, showing that with an index they have similar efficiency, while without an index DISTINCT is usually faster because GROUP BY may trigger sorting and temporary tables, and it provides syntax, examples, and recommendations.

DISTINCTGROUP BYIndex

0 likes · 8 min read

DISTINCT vs GROUP BY in MySQL: Performance and Usage Guide

Ctrip Technology

Oct 20, 2022 · Databases

Practical Slow Query Optimization for MySQL at Ctrip

This article describes Ctrip's practical approach to identifying and optimizing MySQL slow queries, covering background, improved SQL review workflow, execution plan analysis, common index problems, query rewriting, pagination issues, resource contention, and best‑practice recommendations for long‑term performance.

Database OptimizationIndexingMySQL

0 likes · 10 min read

Practical Slow Query Optimization for MySQL at Ctrip

DataFunSummit

Sep 27, 2022 · Big Data

Apache Spark Adaptive Query Execution and Kyuubi Optimization Practices for Data Warehousing

This article presents a detailed overview of Apache Spark's Adaptive Query Execution evolution, its optimization techniques, and performance gains, followed by an in‑depth discussion of Apache Kyuubi's architecture, security integrations, cloud‑native capabilities, and practical Rebalance + Z‑Order strategies that enhance data‑warehouse task efficiency and query performance.

Adaptive Query ExecutionApache SparkBig Data Optimization

0 likes · 19 min read

Apache Spark Adaptive Query Execution and Kyuubi Optimization Practices for Data Warehousing

Qunar Tech Salon

Sep 1, 2021 · Databases

Implementing Transparent Table Partitioning in PostgreSQL: Strategies, Code Samples, and Performance Gains

This article explains why and when to use table partitioning in PostgreSQL, describes inheritance‑based and declarative partitioning methods (list, range, hash), provides complete SQL and C code examples for creating and maintaining partitions, compares version differences, and shows a real‑world performance improvement case from Qunar.

C ExtensionsDatabase AdministrationPostgreSQL

0 likes · 24 min read

Implementing Transparent Table Partitioning in PostgreSQL: Strategies, Code Samples, and Performance Gains

Laravel Tech Community

Jun 28, 2021 · Databases

Understanding and Optimizing MySQL ORDER BY Execution

This article explains how MySQL processes ORDER BY clauses, illustrates the execution steps with a full‑field index, discusses the impact of sort_buffer and max_length_for_sort_data, and shows how adding composite and covering indexes can eliminate filesort and improve query performance.

Index OptimizationMySQLORDER BY

0 likes · 6 min read

Understanding and Optimizing MySQL ORDER BY Execution

Architecture Digest

Jan 24, 2021 · Databases

Debugging a High‑Load SQL Query: Index Miss and Temporary Fix

A production MySQL database suffered CPU overload due to a high‑volume query that bypassed an index because a leftmost column was missing, leading to full‑table scans, and the issue was resolved by adding a new composite index and correcting validation annotations in the Java code.

Bug FixCode ReviewIndexing

0 likes · 8 min read

Debugging a High‑Load SQL Query: Index Miss and Temporary Fix

Ctrip Technology

Oct 15, 2020 · Databases

AppTrace: An Automated System for Detecting and Analyzing Slow SQL in Application Performance Testing

The article introduces AppTrace, a system that automatically captures, analyzes, and alerts on slow SQL statements during testing, aiming to resolve 60%‑80% of application performance issues by leveraging BTrace‑based tracing, execution‑plan comparison, and risk‑based alerting within Ctrip’s backend services.

AppTraceBTraceDatabase Monitoring

0 likes · 9 min read

AppTrace: An Automated System for Detecting and Analyzing Slow SQL in Application Performance Testing

Aikesheng Open Source Community

Sep 15, 2020 · Databases

Using MySQL 8.0 Statement Digest and Digest Text Functions for SQL Summarization

The article explains MySQL 8.0's new statement_digest() and statement_digest_text() functions, showing how they generate normalized SQL texts and SHA2 hashes to group similar queries, and demonstrates practical applications in performance schema analysis and query‑rewrite plugins with concrete code examples.

Database OptimizationSQL performanceStatement Digest

0 likes · 8 min read

Using MySQL 8.0 Statement Digest and Digest Text Functions for SQL Summarization

Top Architect

Aug 3, 2020 · Databases

Case Study: Insert‑Into‑Select Migration Failure and Resolution in MySQL

This article analyzes a real‑world MySQL incident where using INSERT INTO SELECT to migrate billions of rows caused table‑wide locking and payment failures, explains the underlying locking behavior, and presents a solution using proper indexing and FORCE INDEX to avoid full‑table scans.

INSERT INTO SELECTIndexingMySQL

0 likes · 7 min read

Case Study: Insert‑Into‑Select Migration Failure and Resolution in MySQL

Aikesheng Open Source Community

Jun 15, 2020 · Databases

Diagnosing Slow SQL Execution in DBLE Due to Incorrect Stringhash Partition Configuration

An investigation of a DBLE performance issue revealed that identical sharding keys with the same stringhash rule failed to improve query speed because differing hashSlice configurations prevented proper partitioning, and after correcting the rule.xml function settings, the SQL executed efficiently with sub‑second response times.

DBLEDatabase ConfigurationMySQL

0 likes · 4 min read

Diagnosing Slow SQL Execution in DBLE Due to Incorrect Stringhash Partition Configuration

macrozheng

Jun 8, 2020 · Databases

Why MySQL’s Index Choice Can Miss the Best Plan – A Deep Cost Analysis

This article examines how MySQL estimates execution costs for queries, explains why COUNT(*) can be optimal, demonstrates index selection with real‑world examples, and shows how optimizer_trace can reveal mismatches between estimated and actual performance.

EXPLAINIndex OptimizationMySQL

0 likes · 10 min read

Why MySQL’s Index Choice Can Miss the Best Plan – A Deep Cost Analysis

Aikesheng Open Source Community

Apr 22, 2020 · Databases

Why Does the Same SQL Run Fast on MariaDB but Slow on MySQL 5.7?

A production database migration from MariaDB 10.4 to MySQL 5.7 caused a previously fast SQL query to become extremely slow due to differing column collations that prevented index usage, and the article explains how to diagnose the issue and resolve it by aligning collations or using explicit conversion.

CollationDatabase OptimizationIndex

0 likes · 3 min read

Why Does the Same SQL Run Fast on MariaDB but Slow on MySQL 5.7?