Skip to content
Tagged articles

SQL Optimization

367 articles · Page 1 of 4
Ops Community
Ops Community
Nov 9, 2026 · Databases

MySQL Slow Query Analysis & SQL Optimization: A Practical DBA Guide

This comprehensive guide covers MySQL slow query analysis from log configuration to index design, EXPLAIN plan interpretation, SQL rewriting techniques, and production troubleshooting workflows with real-world examples and concrete metrics.

EXPLAINSQL Optimizationdatabase tuning
0 likes · 84 min read
MySQL Slow Query Analysis & SQL Optimization: A Practical DBA Guide
Architect's Guide
Architect's Guide
Oct 22, 2026 · Databases

Why Database Indexes Speed Up Queries: Binary Search & Disk I/O Explained

This article explains why database indexes accelerate queries by detailing disk storage mechanics, demonstrating binary search on 100,000 records reducing block scans from 20,000 to 14, covering clustered vs non-clustered indexes, index trade-offs, common failure cases like OR conditions, and practical SQL optimization techniques.

B+ TreeDatabase IndexingInterview questions
0 likes · 14 min read
Why Database Indexes Speed Up Queries: Binary Search & Disk I/O Explained
Architect's Guide
Architect's Guide
Oct 11, 2026 · Databases

Why Database Indexes Speed Up Queries: Binary Search & Clustered Indexes Explained

This article explains how database indexes accelerate queries by leveraging sorted data structures and binary search, detailing disk I/O mechanics, clustered vs. non-clustered indexes, index trade-offs on write performance, and common SQL optimization pitfalls like OR conditions and leading wildcards that cause index invalidation.

Query PerformanceSQL Optimizationbinary search
0 likes · 13 min read
Why Database Indexes Speed Up Queries: Binary Search & Clustered Indexes Explained
Architect's Guide
Architect's Guide
Oct 9, 2026 · Databases

SQL Optimization: From 30,248s to 0.001s

A step-by-step MySQL query optimization case study showing how indexing, join rewriting, composite indexes, and covering indexes reduced execution time from 30,248 seconds to 0.001 seconds with execution plan analysis.

Composite IndexCovering IndexIndexing
0 likes · 12 min read
SQL Optimization: From 30,248s to 0.001s
Linux Tech Enthusiast
Linux Tech Enthusiast
Sep 28, 2026 · Databases

8 SQL Anti-Patterns That Kill Performance (and How to Fix Them)

This article details eight common SQL anti-patterns — including OFFSET pagination, implicit type conversion, correlated subqueries in updates, mixed sorting, EXISTS clauses, blocked condition pushdown, late filtering, and unoptimized intermediate results — with execution plans and rewrites that reduce query times from seconds to milliseconds.

CTEPaginationQuery Performance
0 likes · 16 min read
8 SQL Anti-Patterns That Kill Performance (and How to Fix Them)
liandk
liandk
Aug 26, 2026 · Databases

Master MySQL EXPLAIN: Full Field Breakdown of type, key, rows, Extra for Real‑World Tuning

The article explains that MySQL’s EXPLAIN command acts as a SQL health check, detailing each of its 12 fields—especially type, key, rows, and Extra—while providing real‑world examples, a step‑by‑step diagnostic workflow, and common pitfalls to help developers reliably tune and troubleshoot queries.

Database IndexingEXPLAINPerformance Tuning
0 likes · 10 min read
Master MySQL EXPLAIN: Full Field Breakdown of type, key, rows, Extra for Real‑World Tuning
Xiaolin Talks Programming
Xiaolin Talks Programming
Aug 21, 2026 · Backend Development

Arthas in Production: Diagnosing Spring Boot CPU Spikes, Memory Leaks & Latency Spikes

This article demonstrates using Arthas to diagnose a Spring Boot service with 100% CPU, memory leaks, and latency spikes, showing step-by-step commands like dashboard, thread, heapdump, trace, and watch to pinpoint dead loops, unbounded caches, and missing SQL indexes, plus emergency hot-patching with redefine.

ArthasCPU profilingJVM troubleshooting
0 likes · 17 min read
Arthas in Production: Diagnosing Spring Boot CPU Spikes, Memory Leaks & Latency Spikes
Java Tech Workshop
Java Tech Workshop
Aug 18, 2026 · Backend Development

Beyond Adding Indexes: From Disk Pages to B+Tree – Master MySQL Index Design and Operation

This article explains why indexes speed up queries by reducing disk I/O, dives into MySQL's page structure and B+Tree evolution, compares clustered and secondary indexes, clarifies composite index rules, lists common index‑misuse scenarios, and provides seven practical guidelines for designing efficient MySQL indexes.

B+ TreeComposite IndexDatabase Performance
0 likes · 20 min read
Beyond Adding Indexes: From Disk Pages to B+Tree – Master MySQL Index Design and Operation
Architect's Guide
Architect's Guide
Aug 8, 2026 · Databases

Why Do Indexes Speed Up Database Queries?

The article explains how database indexes, built on sorted data and binary search, dramatically reduce query time compared to full table scans, while also covering storage fundamentals, the cost of excessive indexes, clustered indexes, index failure scenarios, and practical SQL optimization techniques.

SQL Optimizationbinary searchclustered index
0 likes · 12 min read
Why Do Indexes Speed Up Database Queries?
Dabaoshi
Dabaoshi
Jul 26, 2026 · Databases

Practical MySQL SQL Optimization: How to Locate and Rewrite Slow Queries

The article explains how to make MySQL do less work by reducing scanned rows, avoiding back‑and‑forth lookups with covering indexes, and minimizing sorting or temporary tables, while detailing common index pitfalls, deep pagination tricks, COUNT usage, ORDER BY/filesort handling, JOIN strategies, and step‑by‑step methods for finding and fixing slow SQL statements.

Covering IndexEXPLAINPagination
0 likes · 17 min read
Practical MySQL SQL Optimization: How to Locate and Rewrite Slow Queries
Xiaolin Talks Programming
Xiaolin Talks Programming
Jul 14, 2026 · Backend Development

ORM Performance Tuning in Spring Boot: Fixing N+1 Queries, Dynamic SQL Optimization & Fetch Strategies

This guide covers root-cause analysis of N+1 queries in JPA and MyBatis, APM-based detection methods, fetch strategy optimization using EntityGraph and JOIN FETCH, MyBatis resultMap refactoring, lazy-loading boundaries, second-level cache trade-offs, keyset pagination, and production-grade SQL interceptors with code review checklists.

Fetch StrategyJPAMyBatis
0 likes · 21 min read
ORM Performance Tuning in Spring Boot: Fixing N+1 Queries, Dynamic SQL Optimization & Fetch Strategies
Raymond Ops
Raymond Ops
Jul 11, 2026 · Databases

How to Diagnose and Optimize Production Database Connection Pool Exhaustion

This article walks through the principles of database connection pools, common causes of pool exhaustion such as leaks, mis‑configuration, slow SQL and DB limits, and provides a step‑by‑step methodology with monitoring, thread‑stack analysis, and concrete configuration and SQL optimizations to restore stability.

Connection PoolHikariCPJava
0 likes · 29 min read
How to Diagnose and Optimize Production Database Connection Pool Exhaustion
DeWu Technology
DeWu Technology
Jul 8, 2026 · Databases

DeWu’s OceanBase Deployment: Multi‑Model Database Selection, Performance Gains, and Migration Lessons

DeWu, a leading fashion e‑commerce platform, evaluated and adopted OceanBase as a multi‑model database to replace a heterogeneous stack of MySQL, TiDB, MongoDB, HBase, ClickHouse, and others, achieving up to 200× query speedups, 40%+ storage savings, high‑availability with RPO = 0, and a streamlined operations model while documenting migration challenges and future roadmap.

Database MigrationDeWuMulti-Model Database
0 likes · 19 min read
DeWu’s OceanBase Deployment: Multi‑Model Database Selection, Performance Gains, and Migration Lessons
Linux Tech Enthusiast
Linux Tech Enthusiast
Jul 4, 2026 · Databases

Four MySQL Scripts for Diagnosing and Optimizing Your Queries

This article introduces four command‑line tools—MySQLTuner.pl, tuning‑primer.sh, pt‑variable‑advisor, and pt‑query‑digest—explaining how to download, run them, and interpret their reports to assess MySQL performance, configuration, and query efficiency.

SQL Optimizationmysqlmysqltuner
0 likes · 8 min read
Four MySQL Scripts for Diagnosing and Optimizing Your Queries
Architect's Guide
Architect's Guide
Jun 15, 2026 · Databases

Chat2DB Review: Alibaba’s Open‑Source Multi‑Database Client with AI‑Driven SQL Features

Chat2DB is a free open‑source multi‑database client that adds AI capabilities such as natural‑language‑to‑SQL, SQL‑to‑natural‑language, and performance suggestions; this guide walks through downloading, installing, configuring OpenAI keys, using its four main menus, and evaluating its SQL generation, explanation, and optimization functions.

AIChat2DBOpenAI
0 likes · 11 min read
Chat2DB Review: Alibaba’s Open‑Source Multi‑Database Client with AI‑Driven SQL Features
IoT Full-Stack Technology
IoT Full-Stack Technology
May 29, 2026 · Backend Development

How We Cut a 30‑Second API Call to Under 1 Second on 2 Million Records

In a high‑concurrency transaction system, the author diagnosed a 30‑second API latency caused by costly SQL scans and Java Map creation on over two million rows, then applied SQL aggregation, moved counting logic into PostgreSQL, and introduced a Caffeine cache, ultimately reducing the response time to under 0.8 seconds while highlighting relational‑database limits for massive data.

Caffeine CacheJavaMyBatis
0 likes · 10 min read
How We Cut a 30‑Second API Call to Under 1 Second on 2 Million Records
Architect's Guide
Architect's Guide
May 23, 2026 · Databases

SQL Optimization: Cutting Query Time from 30,248 s to 0.001 s

This article walks through a MySQL 5.6 case study where a sub‑query that originally took over eight hours is accelerated to a few milliseconds by analyzing the execution plan, adding single‑column and composite indexes, rewriting the query as EXISTS or JOIN, and applying best‑practice indexing techniques.

IndexingQuery PerformanceSQL Optimization
0 likes · 13 min read
SQL Optimization: Cutting Query Time from 30,248 s to 0.001 s
Architect's Guide
Architect's Guide
May 22, 2026 · Databases

How Indexes Speed Up Database Queries: A Clear Explanation

The article explains how database indexes work by comparing them to a book’s table of contents, describes underlying storage mechanisms, demonstrates binary search on sorted records, and discusses the benefits, trade‑offs, and common pitfalls of using clustered and non‑clustered indexes.

Database IndexesQuery PerformanceSQL Optimization
0 likes · 12 min read
How Indexes Speed Up Database Queries: A Clear Explanation
IT Services Circle
IT Services Circle
May 12, 2026 · Databases

How to Diagnose and Resolve Online Slow SQL That Causes CPU Spikes

When a MySQL‑driven application shows sudden CPU spikes, this guide walks through a step‑by‑step process—using top/htop, SHOW PROCESSLIST, enabling slow‑query logs, analyzing EXPLAIN output, killing offending queries, adding appropriate indexes, rewriting joins, and establishing preventive monitoring—to quickly identify and fix the root cause.

CPU spikeIndexingPerformance Tuning
0 likes · 12 min read
How to Diagnose and Resolve Online Slow SQL That Causes CPU Spikes
Aikesheng Open Source Community
Aikesheng Open Source Community
May 11, 2026 · Artificial Intelligence

SCALE April 2026 Large‑Model SQL Capability Ranking Unveiled

The SCALE April 2026 report adds four new models—DeepSeek‑V4‑Pro, DeepSeek‑V4‑Flash, GPT‑5.5 and Claude Opus 4.7—to its SQL capability leaderboard, evaluates them across SQL understanding, optimization and dialect conversion, and highlights each model’s strengths, weaknesses, and recommended deployment scenarios.

AI benchmarkDialect ConversionLarge Language Models
0 likes · 17 min read
SCALE April 2026 Large‑Model SQL Capability Ranking Unveiled
ITPUB
ITPUB
May 6, 2026 · Databases

The Hidden MySQL File That Grows on Its Own—Why 90% of DBAs Miss It

A sudden disk‑usage alarm reveals MySQL's ibtmp1 file ballooning to 192 GB, prompting a step‑by‑step investigation that explains the file’s purpose, shows how to clear and cap its size, and demonstrates how inefficient SQL queries can trigger such runaway growth.

Performance TuningSQL OptimizationTemporary tables
0 likes · 8 min read
The Hidden MySQL File That Grows on Its Own—Why 90% of DBAs Miss It
Su San Talks Tech
Su San Talks Tech
May 3, 2026 · Databases

How to Speed Up a 10‑Table JOIN Query in MySQL (JD Interview Scenario)

When a production MySQL query joins ten tables and exceeds 30 seconds, a systematic approach—starting with EXPLAIN analysis, profiling, and configuration checks—combined with layered optimizations such as indexing, join order adjustment, query splitting, temporary tables, materialized views, OLAP engines, and vertical partitioning—can dramatically improve performance.

IndexingOLAPPerformance Tuning
0 likes · 17 min read
How to Speed Up a 10‑Table JOIN Query in MySQL (JD Interview Scenario)
Architecture & Thinking
Architecture & Thinking
Apr 16, 2026 · Databases

Mastering Database Performance: From Slow Queries to Deadlock Resolution

This guide presents a systematic, end-to-end diagnostic workflow for high-concurrency database environments, covering slow-SQL detection, execution-plan analysis, lock-conflict monitoring, deadlock investigation, and practical optimization techniques, illustrated with real-world cases, commands, and tool integrations to swiftly resolve performance bottlenecks.

Database DiagnosticsDeadlockLock Monitoring
0 likes · 18 min read
Mastering Database Performance: From Slow Queries to Deadlock Resolution
JD Tech
JD Tech
Apr 9, 2026 · Databases

How DongSQL Solves E‑Commerce DB Overload, Flash‑Sale, and Plan Drift

This article analyzes JD's self‑developed database kernel DongSQL, describing its architecture, the challenges of overload, flash‑sale write spikes, cache consistency, execution‑plan drift, and thread congestion, and explains how hint‑based limiting, RETURNING syntax, statement outlines, and a built‑in thread pool address each issue.

HintSQL Optimizationdatabase
0 likes · 22 min read
How DongSQL Solves E‑Commerce DB Overload, Flash‑Sale, and Plan Drift
dbaplus Community
dbaplus Community
Mar 29, 2026 · Databases

Why MySQL’s ibtmp1 Swells to 192 GB and How to Stop It

A sudden disk‑usage alarm reveals MySQL’s ibtmp1 temporary tablespace ballooning to 192 GB, and the article walks through why it happens, how to safely clear it, set size limits, and pinpoint the inefficient SQL that caused the explosion.

Performance TuningSQL Optimizationdisk usage
0 likes · 7 min read
Why MySQL’s ibtmp1 Swells to 192 GB and How to Stop It
dbaplus Community
dbaplus Community
Mar 4, 2026 · Databases

Master PostgreSQL Execution Plans to Supercharge Your SQL Performance

This guide explains what PostgreSQL execution plans are, how they are generated, and how to interpret them using EXPLAIN and EXPLAIN ANALYZE, then demonstrates three real‑world case studies that show step‑by‑step optimizations with indexes, configuration tweaks, and query rewrites to dramatically improve query speed.

Database PerformanceIndexingPostgreSQL
0 likes · 16 min read
Master PostgreSQL Execution Plans to Supercharge Your SQL Performance
dbaplus Community
dbaplus Community
Feb 23, 2026 · Databases

How to Diagnose and Optimize a Minute‑Long MySQL Join Query

This article walks through a real‑world MySQL slow‑query case, explains the inner workings of Join and Order by algorithms, shows how to interpret EXPLAIN and Optimizer_trace output, and demonstrates how adding an index can transform a costly Block Nested‑Loop Join into a fast Index Nested‑Loop Join, dramatically improving performance.

EXPLAINJoin AlgorithmsORDER BY
0 likes · 13 min read
How to Diagnose and Optimize a Minute‑Long MySQL Join Query
IT Learning Made Simple
IT Learning Made Simple
Feb 13, 2026 · Databases

Designing Databases as Intelligent Warehouses: A Supermarket Analogy

This article explains core database design concepts—from relational vs. NoSQL choices and normalization rules to indexing strategies, ACID transactions, SQL optimization, and sharding—using supermarket‑style analogies and concrete exam‑style examples to help readers build fast, safe, and scalable data stores.

IndexingSQL OptimizationTransactions
0 likes · 38 min read
Designing Databases as Intelligent Warehouses: A Supermarket Analogy
Architect's Tech Stack
Architect's Tech Stack
Jan 22, 2026 · Databases

Unlock MySQL 8.0: 8 Hidden Optimization Tricks You Must Know

This article reveals eight powerful, lesser‑known MySQL 8.0 features—including window functions, descending indexes, generated columns, invisible indexes, optimizer hints, resource groups, LATERAL JOIN, and multi‑valued JSON indexes—showing how they dramatically improve query performance and simplify complex SQL tasks.

LATERAL JOINResource GroupSQL Optimization
0 likes · 12 min read
Unlock MySQL 8.0: 8 Hidden Optimization Tricks You Must Know
Big Data Tech Team
Big Data Tech Team
Jan 5, 2026 · Big Data

Top 10 Data Warehouse Interview Questions Every 2026 Engineer Must Master

This article compiles the most frequently asked interview questions for 2026 data‑warehouse development engineers, covering core concepts, layer architecture, SQL optimization, window functions, Hive vs Spark, data skew solutions, modeling metrics, slowly changing dimensions, scheduling tools, data quality monitoring, and real project experience.

Data ModelingHiveInterview questions
0 likes · 8 min read
Top 10 Data Warehouse Interview Questions Every 2026 Engineer Must Master
Top Architect
Top Architect
Jan 2, 2026 · Databases

Boost MySQL Performance: Master Pagination, Indexes, JOINs, and Query Rewrite Techniques

This article walks through common MySQL performance pitfalls such as inefficient pagination, implicit type conversion, costly UPDATE/DELETE subqueries, mixed sorting, and EXISTS clauses, and demonstrates practical rewrites—including index usage, JOIN conversion, condition push‑down, early LIMIT, and WITH‑clauses—to transform slow queries into millisecond‑level executions.

IndexPaginationQuery Rewrite
0 likes · 13 min read
Boost MySQL Performance: Master Pagination, Indexes, JOINs, and Query Rewrite Techniques
Lakehouse Research Base
Lakehouse Research Base
Jan 2, 2026 · Databases

StarRocks Production Incident: Continuous Report Refresh Triggers Cluster Crash & Recovery

This article details a StarRocks cluster crash caused by users continuously refreshing reconciliation dashboards, the emergency response including query timeout reduction and compute node scaling, root cause analysis highlighting storage-compute separation benefits, and long-term preventive measures like business reporting processes, Multi-Warehouse isolation, and query governance.

Cluster ScalingMulti-WarehouseSQL Optimization
0 likes · 17 min read
StarRocks Production Incident: Continuous Report Refresh Triggers Cluster Crash & Recovery
ITPUB
ITPUB
Dec 20, 2025 · Databases

Why MySQL IN Clauses Hit Limits and How to Optimize Them

This article explains the reasons behind MySQL IN‑clause parameter limits, the performance impact of large IN lists, and provides practical optimization techniques such as splitting queries in application code and using temporary tables with EXISTS to improve efficiency.

IN ClauseSQL Optimizationmysql
0 likes · 5 min read
Why MySQL IN Clauses Hit Limits and How to Optimize Them
Aikesheng Open Source Community
Aikesheng Open Source Community
Dec 17, 2025 · Databases

How SQLFlash Stands Up to the SCALE Benchmark: Deep Dive into AI‑Powered SQL Optimization

This report evaluates the AI‑driven SQLFlash tool against the upgraded SCALE benchmark dataset, presenting core metrics on syntax compliance, logical equivalence, and optimization depth, and analyzes strengths, limitations, and future improvement directions for production‑grade SQL tuning.

AI modelsDatabase PerformanceLLM Evaluation
0 likes · 10 min read
How SQLFlash Stands Up to the SCALE Benchmark: Deep Dive into AI‑Powered SQL Optimization
Aikesheng Open Source Community
Aikesheng Open Source Community
Nov 26, 2025 · Databases

Why INSERT…SELECT Slows Down on OceanBase and How to Fix It

During an Oracle‑to‑OceanBase migration, a client encountered extremely slow INSERT…SELECT batch jobs due to a global unique non‑partitioned index, and the article walks through extracting logical and physical plans, pinpointing the bottleneck operators, and demonstrates converting the index to a partitioned one to cut execution time from over 30 minutes to 14 minutes.

INSERT SELECTOceanBaseSQL Optimization
0 likes · 16 min read
Why INSERT…SELECT Slows Down on OceanBase and How to Fix It
Architect's Tech Stack
Architect's Tech Stack
Nov 14, 2025 · Databases

8 MySQL Optimizations to Slash Query Times from Seconds to Milliseconds

This article presents eight practical MySQL performance‑tuning techniques—including smarter LIMIT usage, avoiding implicit type conversion, rewriting updates and deletes with JOIN, handling mixed ordering, replacing EXISTS with JOIN, pushing conditions down, early range reduction, and using WITH clauses—to transform slow queries into millisecond‑level executions.

Performance TuningQuery RewriteSQL Optimization
0 likes · 14 min read
8 MySQL Optimizations to Slash Query Times from Seconds to Milliseconds
Code Ape Tech Column
Code Ape Tech Column
Nov 10, 2025 · Databases

How to Quickly Identify and Optimize MySQL Slow Queries

This guide explains how to enable MySQL slow‑query logging, set appropriate thresholds, locate problematic SQL statements, analyze execution plans with EXPLAIN, and apply index or query rewrites to dramatically improve performance.

EXPLAINSQL Optimizationmysql
0 likes · 10 min read
How to Quickly Identify and Optimize MySQL Slow Queries
Aikesheng Open Source Community
Aikesheng Open Source Community
Oct 29, 2025 · Databases

Why Does the Same SQL Run Faster on Oracle Than on OceanBase? Uncovering Execution Plan Discrepancies

A customer observed a SQL statement that runs twice as fast on Oracle as on OceanBase, prompting a detailed analysis of execution plans, leader distribution, and distributed query behavior, followed by practical hint and table‑group optimizations that reduce the runtime from several seconds to sub‑second levels.

OceanBaseSQL OptimizationTable Group
0 likes · 18 min read
Why Does the Same SQL Run Faster on Oracle Than on OceanBase? Uncovering Execution Plan Discrepancies
Xiao Liu Lab
Xiao Liu Lab
Oct 27, 2025 · Databases

Boost MySQL Performance: 6 Essential Config Tweaks for 5000+ QPS

Even when CPU and memory appear idle, MySQL can suffer connection timeouts, query stalls, and 100% CPU spikes during peak traffic, but by adjusting six key configuration parameters and following solid SQL‑optimization practices you can reliably support thousands of queries per second.

Database ConfigurationPerformance TuningSQL Optimization
0 likes · 6 min read
Boost MySQL Performance: 6 Essential Config Tweaks for 5000+ QPS
Huolala Tech
Huolala Tech
Oct 17, 2025 · Big Data

How HuoLala Accelerated User Profiling 30× Faster with Apache Doris

This article details how HuoLala built a high‑performance user profiling platform on Apache Doris, redesigning data models, leveraging bitmap storage, and applying query‑level optimizations to achieve up to 30‑fold speed gains, lower memory usage, and scalable real‑time analytics.

Apache DorisBitmapData Modeling
0 likes · 17 min read
How HuoLala Accelerated User Profiling 30× Faster with Apache Doris
Big Data Tech Team
Big Data Tech Team
Oct 10, 2025 · Big Data

12 Essential Hive SQL Optimization Tricks to Boost Query Performance

This article presents twelve practical Hive SQL tuning techniques—ranging from avoiding COUNT(DISTINCT) to configuring parallel execution, reducer settings, and strict mode—to help data engineers reduce data skew, eliminate small files, improve resource utilization, and significantly accelerate query execution in large‑scale data warehouse environments.

HiveQuery PerformanceSQL Optimization
0 likes · 11 min read
12 Essential Hive SQL Optimization Tricks to Boost Query Performance
Su San Talks Tech
Su San Talks Tech
Sep 25, 2025 · Databases

Boost MySQL Query Speed: Indexing, Subqueries vs Joins Explained

This article walks through a MySQL 5.6 scenario where a sub‑query to find students scoring 100 runs for hours, demonstrates how adding single‑column indexes cuts execution time dramatically, explores join‑based rewrites, composite indexes, left‑most prefix rules, covering indexes and sorting optimizations, and provides practical performance‑tuning tips.

IndexingPerformance TuningSQL Optimization
0 likes · 12 min read
Boost MySQL Query Speed: Indexing, Subqueries vs Joins Explained
Architect
Architect
Sep 24, 2025 · Databases

How Indexes Can Speed Up MySQL Queries by 30,000×: A Practical Guide

This article walks through a MySQL 5.6 scenario with three tables, demonstrates why a sub‑query that scans millions of rows runs for hours, and shows step‑by‑step how adding single‑column, composite, and covering indexes, as well as rewriting the query as a join, reduces execution time from over eight hours to a few milliseconds.

IndexingQuery PerformanceSQL Optimization
0 likes · 12 min read
How Indexes Can Speed Up MySQL Queries by 30,000×: A Practical Guide
Big Data Tech Team
Big Data Tech Team
Sep 15, 2025 · Interview Experience

Top Data Warehouse Engineer Interview Questions & Answers Revealed

This article compiles three interview rounds for a data warehouse engineer role, covering fundamental concepts, practical skills, and leadership thinking with detailed Q&A on ETL, Hadoop components, schema design, data quality, data lake vs. warehouse, ACID properties, cloud solutions, SQL optimization, real‑time processing, security, and team management.

ETLHadoopInterview questions
0 likes · 12 min read
Top Data Warehouse Engineer Interview Questions & Answers Revealed
php Courses
php Courses
Sep 15, 2025 · Databases

Eliminate the N+1 Query Anti‑Pattern: Strategies to Boost Database Performance

This article explains why executing many small database queries in a short time harms performance, shows how to detect the N+1 problem with monitoring and logs, and presents refactoring techniques such as eager loading, batch operations, aggregation, caching, and hand‑crafted SQL to dramatically improve speed, scalability, and code quality.

Batch OperationsDatabase PerformanceEager Loading
0 likes · 9 min read
Eliminate the N+1 Query Anti‑Pattern: Strategies to Boost Database Performance
MaGe Linux Operations
MaGe Linux Operations
Sep 7, 2025 · Databases

Master MySQL Slow Query Analysis: Proven SQL Optimization Techniques to Boost Performance

This comprehensive guide walks you through diagnosing MySQL slow queries, from identifying root causes and configuring slow‑query logs to applying advanced indexing, query‑rewriting, and monitoring techniques—complete with real‑world case studies that demonstrate how to cut query times from seconds to milliseconds.

IndexingSQL Optimizationmonitoring
0 likes · 28 min read
Master MySQL Slow Query Analysis: Proven SQL Optimization Techniques to Boost Performance
IT Services Circle
IT Services Circle
Aug 26, 2025 · Databases

Mastering Database Indexes: 10 Essential Questions Every Developer Should Know

This article demystifies database indexes by explaining their purpose, inner workings, and best‑practice design, covering common pitfalls such as slow queries, over‑indexing, NULL handling, composite index ordering, covering indexes, sorting, grouping, and maintenance across different database systems.

Composite IndexCovering IndexPerformance Tuning
0 likes · 11 min read
Mastering Database Indexes: 10 Essential Questions Every Developer Should Know
Big Data Technology Tribe
Big Data Technology Tribe
Aug 12, 2025 · Databases

Why Lakehouse Architecture Is Redefining Modern Data Platforms

This article explains the evolution from traditional data warehouses and data lakes to the unified Lakehouse architecture, detailing its design, benefits, challenges, and research directions for delivering high‑performance SQL and advanced analytics on open‑format storage.

Metadata LayerSQL Optimizationbig data
0 likes · 20 min read
Why Lakehouse Architecture Is Redefining Modern Data Platforms
macrozheng
macrozheng
Aug 7, 2025 · Databases

Boost MySQL Performance: 8 Proven SQL Optimization Techniques

This article explores eight common MySQL performance pitfalls—from inefficient LIMIT clauses and implicit type conversions to suboptimal JOIN updates, mixed sorting, EXISTS usage, condition pushdown, early result limiting, and intermediate result set handling—offering rewritten queries and execution plan analyses that dramatically improve execution times.

Database IndexesQuery RewriteSQL Optimization
0 likes · 13 min read
Boost MySQL Performance: 8 Proven SQL Optimization Techniques
Tencent Tech
Tencent Tech
Jul 4, 2025 · Databases

How Tencent Cloud’s TCDataAgent Cracked the BIRD‑Bench NL2SQL Challenge

Tencent Cloud’s self‑developed TCDataAgent secured the global third place on the demanding BIRD‑Bench NL2SQL benchmark by leveraging database‑aware constraint verification, post‑training techniques, and real‑data SQL optimization, achieving a 75.74 score and significant accuracy gains over existing methods.

BIRD BenchDatabase AINL2SQL
0 likes · 5 min read
How Tencent Cloud’s TCDataAgent Cracked the BIRD‑Bench NL2SQL Challenge
Aikesheng Open Source Community
Aikesheng Open Source Community
Jul 2, 2025 · Databases

How to Speed Up Slow SQL Queries: Choosing Driver Tables and Join Strategies

This article analyzes a slow SQL query by examining driver tables, execution plans, and table row counts, then demonstrates three optimization approaches—setting a small table as driver, setting a large table as driver, and switching to hash joins—showing their impact on execution time and providing practical guidelines for selecting the best strategy.

Driver TableHash JoinOceanBase
0 likes · 16 min read
How to Speed Up Slow SQL Queries: Choosing Driver Tables and Join Strategies
StarRocks
StarRocks
Jun 17, 2025 · Databases

How to Ace the StarRocks SRCA Certification: Key Topics and Study Strategies

This guide outlines the StarRocks SRCA certification exam, highlights essential study resources, breaks down the core topics such as architecture, data import/export, SQL optimization and performance tuning, and offers practical tips, mock‑exam details, and personal experience to help candidates succeed.

Database CertificationPerformance TuningSQL Optimization
0 likes · 9 min read
How to Ace the StarRocks SRCA Certification: Key Topics and Study Strategies
IT Xianyu
IT Xianyu
Jun 12, 2025 · Databases

Why Mastering MySQL Slow Query Log Is Essential for Performance

This guide explains what the MySQL slow query log is, why it matters for performance, how to check its status, enable it temporarily or permanently in Docker‑based deployments, troubleshoot command‑not‑found errors, read the log file, and filter entries to focus on the truly slow queries.

Database PerformanceSQL Optimizationmysql
0 likes · 6 min read
Why Mastering MySQL Slow Query Log Is Essential for Performance
Aikesheng Open Source Community
Aikesheng Open Source Community
May 28, 2025 · Databases

Analyzing and Optimizing Slow OceanBase SQL Queries Using TopSQL and Execution Plan Rewrites

This article investigates a slow OceanBase SQL query by examining CPU and TopSQL metrics, dissecting the execution plan, identifying batch join inefficiencies, rewriting the SQL for better performance, and demonstrating a reduction in execution time from over 2600 seconds to under one second.

Batch JoinDatabase PerformanceOceanBase
0 likes · 18 min read
Analyzing and Optimizing Slow OceanBase SQL Queries Using TopSQL and Execution Plan Rewrites
Aikesheng Open Source Community
Aikesheng Open Source Community
May 27, 2025 · Databases

MySQL Execution Plans in Tree Format

This article introduces MySQL's tree‑style execution plan output using EXPLAIN FORMAT=TREE, explains its advantages over traditional and JSON formats, and demonstrates its practical use through eight detailed SQL examples with full plan analysis and performance insights.

SQL OptimizationTree Formatdatabase
0 likes · 12 min read
MySQL Execution Plans in Tree Format
Su San Talks Tech
Su San Talks Tech
May 12, 2025 · Databases

How to Diagnose and Fix Slow MySQL Queries: A Surgeon’s Guide

This article walks you through diagnosing slow MySQL queries using EXPLAIN, fixing index issues, reordering joins, forcing optimal indexes, monitoring index usage, and applying systematic governance such as SQL sanitization and sharding to dramatically improve performance.

EXPLAINPerformance TuningSQL Optimization
0 likes · 9 min read
How to Diagnose and Fix Slow MySQL Queries: A Surgeon’s Guide
Top Architect
Top Architect
May 10, 2025 · Backend Development

Optimizing a High‑Concurrency Backend Interface: Reducing Response Time from 30 seconds to 0.8 seconds

This article presents a real‑world case study of a high‑traffic backend API that originally took 30 seconds to process over two million records, detailing problem diagnosis, SQL and Java code analysis, and a series of optimizations—including SQL rewrites, database‑side aggregation, and Caffeine caching—that ultimately cut the response time to under one second.

BackendCaffeine CacheMyBatis
0 likes · 13 min read
Optimizing a High‑Concurrency Backend Interface: Reducing Response Time from 30 seconds to 0.8 seconds
Top Architecture Tech Stack
Top Architecture Tech Stack
May 10, 2025 · Databases

MySQL Query Optimization Guidelines and Best Practices

This article presents a comprehensive set of MySQL optimization techniques, covering the avoidance of SELECT *, proper use of specific fields, index-friendly query patterns, join strategies, handling of NULL and default values, efficient use of UNION, batch inserts, and numerous other performance‑enhancing tips for database developers.

Query PerformanceSQL Optimizationdatabase tuning
0 likes · 17 min read
MySQL Query Optimization Guidelines and Best Practices
Java Architect Essentials
Java Architect Essentials
Apr 7, 2025 · Databases

Practical MySQL Query Optimizations: LIMIT, Implicit Conversion, Join Rewrite, Mixed Sorting, EXISTS, Predicate Push‑down, Early Row Limiting, and Intermediate Result Push‑down

This article presents a series of MySQL performance‑tuning techniques—including smarter LIMIT usage, avoiding implicit type conversion, rewriting joins, handling mixed ordering, replacing EXISTS with joins, pushing predicates into subqueries, early row limiting, and using WITH clauses—to dramatically reduce query execution time across common scenarios.

Predicate PushdownQuery PerformanceSQL Optimization
0 likes · 13 min read
Practical MySQL Query Optimizations: LIMIT, Implicit Conversion, Join Rewrite, Mixed Sorting, EXISTS, Predicate Push‑down, Early Row Limiting, and Intermediate Result Push‑down
vivo Internet Technology
vivo Internet Technology
Apr 2, 2025 · Databases

Case Study of Slow SQL Governance in an Activity Middleware System

The case study details how an activity‑middleware platform tackled slow SQL problems by cleaning historic data, introducing sharding and Elasticsearch off‑loading, optimizing queries with proper indexes and simplified joins, and automating nightly deletions, ultimately cutting daily slow queries from thousands to double‑digit levels and boosting system stability.

Database PerformanceIndexingSQL Optimization
0 likes · 17 min read
Case Study of Slow SQL Governance in an Activity Middleware System
Baidu Geek Talk
Baidu Geek Talk
Mar 24, 2025 · Big Data

How Turing Data Finder Transforms Growth Analysis with a Unified Data Platform

The article provides a detailed technical overview of the Turing Data Finder (TDF) platform, describing its background, core components, data schema, ingestion workflow, and a suite of growth‑analysis features such as event, retention, funnel, path, component, distribution, and attribution analysis, while also outlining performance‑optimisation techniques and future development directions.

Data EngineeringSQL OptimizationTuring Data Finder
0 likes · 17 min read
How Turing Data Finder Transforms Growth Analysis with a Unified Data Platform
Java Tech Enthusiast
Java Tech Enthusiast
Mar 23, 2025 · Databases

MySQL Query Optimization Techniques: LIMIT, Implicit Conversion, Joins and More

The article shows how common MySQL performance pitfalls—such as large LIMIT offsets, implicit type conversions, sub‑query‑based updates or deletes, mixed ORDER BY, EXISTS clauses, and misplaced predicates—can be rewritten into index‑friendly forms using filters, joins, early limits, UNION ALL, and CTEs to achieve execution times that drop from seconds to milliseconds.

LIMITQuery PerformanceSQL Optimization
0 likes · 14 min read
MySQL Query Optimization Techniques: LIMIT, Implicit Conversion, Joins and More
The Dominant Programmer
The Dominant Programmer
Mar 21, 2025 · Databases

Top MySQL Interview Questions Explained: Ace Your Database Interview

This article compiles the most frequently asked MySQL interview questions, covering fundamentals, storage engines, SQL optimization, indexing, transaction handling, replication, sharding, and performance troubleshooting to help readers master both theory and practical usage.

Interview questionsPerformance TuningSQL Optimization
0 likes · 8 min read
Top MySQL Interview Questions Explained: Ace Your Database Interview
Java Tech Enthusiast
Java Tech Enthusiast
Mar 11, 2025 · Databases

Performance Analysis of MySQL 5.7 to 8.0 Upgrade and ORDER BY Optimization

After upgrading from MySQL 5.7 to 8.0, a client observed that ORDER BY queries on wide SELECTs became dramatically slower because MySQL 8.0 deprecated the max_length_for_sort_data setting, causing the optimizer to perform full‑field sorting instead of index‑based sorting; adding an index on the ordered column restores performance.

SQL Optimizationdatabasemysql
0 likes · 6 min read
Performance Analysis of MySQL 5.7 to 8.0 Upgrade and ORDER BY Optimization
ITPUB
ITPUB
Mar 1, 2025 · Artificial Intelligence

Can DeepSeek AI Replace Your DBA? Real-World Database Scenarios Tested

This article examines DeepSeek, a Chinese AGI‑focused AI model, explains prompt‑engineering techniques, and evaluates its performance across database architecture, development, and operations tasks through concrete Q&A examples, SQL plan analysis, and shell‑script generation, while also discussing its broader impact on professionals, vendors and enterprises.

AIDeepSeekSQL Optimization
0 likes · 10 min read
Can DeepSeek AI Replace Your DBA? Real-World Database Scenarios Tested
dbaplus Community
dbaplus Community
Feb 20, 2025 · Artificial Intelligence

Can DeepSeek AI Replace DBA Tasks? Real-World Database Scenarios Tested

This article examines DeepSeek, a Chinese AGI‑focused AI model, and demonstrates how prompt engineering can enable it to assist database architects, development DBAs, and operations DBAs across various real‑world scenarios, while also discussing its broader impact on individuals, vendors, and enterprises.

AI for DBAsDeepSeekSQL Optimization
0 likes · 10 min read
Can DeepSeek AI Replace DBA Tasks? Real-World Database Scenarios Tested
Raymond Ops
Raymond Ops
Feb 20, 2025 · Databases

Master MySQL Slow Query Optimization: EXPLAIN Tips & Practical SQL Tuning

This guide walks through identifying slow MySQL queries using EXPLAIN, explains each output field, and presents practical optimization techniques—including proper index usage, pagination tricks, join improvements, and ORDER/GROUP BY tuning—to dramatically boost query performance.

EXPLAINPerformance TuningSQL Optimization
0 likes · 8 min read
Master MySQL Slow Query Optimization: EXPLAIN Tips & Practical SQL Tuning
Big Data Technology Architecture
Big Data Technology Architecture
Feb 8, 2025 · Big Data

How AI Can Accelerate Data Engineering: Practical DeepSeek Use Cases and Tips

This article shows how AI tools like DeepSeek can dramatically speed up data‑engineering tasks—such as fixing long‑running SQL queries, building real‑time data pipelines with Flink, and deciphering legacy stored procedures—while offering concrete prompts, real‑world case studies, and five time‑saving techniques.

Data EngineeringDeepSeekSQL Optimization
0 likes · 6 min read
How AI Can Accelerate Data Engineering: Practical DeepSeek Use Cases and Tips
dbaplus Community
dbaplus Community
Feb 3, 2025 · Databases

How to Diagnose and Fix Extreme ClickHouse Load Spikes in Production

A production ClickHouse cluster suddenly showed blacked‑out dashboards due to CPU load soaring above 2,700%, and this guide walks through step‑by‑step diagnostics using system tables, a simple query to spot heavy SQL, and practical remediation actions to restore normal load levels.

ClickHouseDatabase PerformanceSQL Optimization
0 likes · 7 min read
How to Diagnose and Fix Extreme ClickHouse Load Spikes in Production
Aikesheng Open Source Community
Aikesheng Open Source Community
Jan 14, 2025 · Databases

Understanding the Extra Column in MySQL EXPLAIN Output

This article explains the meaning of the Extra column in MySQL EXPLAIN results, covering common values such as Using index, Backward index scan, Select tables optimized away, and Zero limit, and shows how to interpret and optimize each case with concrete examples.

EXPLAINSQL Optimizationdatabase
0 likes · 15 min read
Understanding the Extra Column in MySQL EXPLAIN Output
Alibaba Cloud Big Data AI Platform
Alibaba Cloud Big Data AI Platform
Jan 9, 2025 · Big Data

How Dynamic Filters Supercharge MaxCompute Joins and Cut CPU by 70%

MaxCompute’s dynamic filter and dynamic partition pruning features dramatically accelerate cross‑period join queries by generating runtime filters that prune irrelevant data before the shuffle, reducing scanned data volume by over 95%, cutting CPU usage by 70% and slashing query latency in large‑scale merchant billing workloads.

Dynamic FilterJoin PerformanceMaxCompute
0 likes · 11 min read
How Dynamic Filters Supercharge MaxCompute Joins and Cut CPU by 70%
58 Tech
58 Tech
Dec 19, 2024 · Big Data

Architecture Evolution and Implementation of the Intelligent Acceleration Engine in the 58 Big Data Platform

The article details the background, architectural analysis, multi‑tenant redesign, engine selection enhancements, compatibility adaptations, stability fixes, containerized deployment, performance optimizations, and measurable business outcomes of the Intelligent Acceleration Engine upgrade using Apache Kyuubi and StarRocks within the 58 big data platform.

Apache KyuubiContainerizationMulti-tenancy
0 likes · 12 min read
Architecture Evolution and Implementation of the Intelligent Acceleration Engine in the 58 Big Data Platform
Top Architect
Top Architect
Dec 16, 2024 · Databases

MySQL Performance Tuning Tools: mysqltuner.pl, tuning-primer.sh, pt-variable-advisor, and pt-query-digest

This article introduces four open‑source MySQL performance tools—mysqltuner.pl, tuning‑primer.sh, pt‑variable‑advisor, and pt‑query‑digest—explaining their purpose, how to download and run them, and how to interpret the diagnostic reports they generate for database optimization.

Database ToolsPerformance TuningSQL Optimization
0 likes · 12 min read
MySQL Performance Tuning Tools: mysqltuner.pl, tuning-primer.sh, pt-variable-advisor, and pt-query-digest
JD Tech
JD Tech
Dec 13, 2024 · Databases

MySQL Join Algorithms and Optimization Techniques

This article explains how multi‑table joins in MySQL can become performance bottlenecks and walks through the four join algorithms—Simple Nested‑Loop, Block Nested‑Loop, Hash, and Index Nested‑Loop—showing their execution plans, code examples, and step‑by‑step optimizations using indexes and join buffers to dramatically improve query speed.

IndexingJoin AlgorithmsPerformance Tuning
0 likes · 19 min read
MySQL Join Algorithms and Optimization Techniques
JD Tech Talk
JD Tech Talk
Dec 9, 2024 · Databases

Mastering MySQL Join Algorithms: From Simple Loops to Hash Joins

This article explores MySQL's join processing methods—Simple Nested‑Loop, Block Nested‑Loop, Hash, and Index Nested‑Loop joins—demonstrates how to create test tables, analyze execution plans, and apply practical optimizations such as indexing and query rewriting to dramatically improve multi‑table query performance.

Database PerformanceIndexingJoin Algorithms
0 likes · 21 min read
Mastering MySQL Join Algorithms: From Simple Loops to Hash Joins
JD Cloud Developers
JD Cloud Developers
Dec 9, 2024 · Databases

Mastering MySQL Join Algorithms: From Simple Loops to Hash Joins

This article explains how MySQL processes multi‑table joins, compares Simple Nested‑Loop, Block Nested‑Loop, Hash, and Index Nested‑Loop join algorithms, and demonstrates practical optimization techniques using indexes, join buffers, and query rewrites to dramatically improve performance on large datasets.

Database PerformanceIndexingJoin Algorithms
0 likes · 20 min read
Mastering MySQL Join Algorithms: From Simple Loops to Hash Joins
Top Architecture Tech Stack
Top Architecture Tech Stack
Dec 5, 2024 · Databases

Advanced MySQL Query Optimization Techniques: LIMIT, Implicit Conversion, Join Updates, Mixed Sorting, EXISTS, Predicate Pushdown, Early Range Reduction, and CTEs

This article explains common MySQL performance pitfalls such as large‑offset LIMIT queries, implicit type conversion, sub‑query updates, mixed sorting, inefficient EXISTS clauses, predicate push‑down limitations, and demonstrates how rewriting with proper indexes, JOINs, early range reduction, and WITH (CTE) statements can reduce execution time from seconds to milliseconds.

CTEPredicate PushdownSQL Optimization
0 likes · 12 min read
Advanced MySQL Query Optimization Techniques: LIMIT, Implicit Conversion, Join Updates, Mixed Sorting, EXISTS, Predicate Pushdown, Early Range Reduction, and CTEs
dbaplus Community
dbaplus Community
Nov 24, 2024 · Databases

Boost MySQL Performance: Proven SQL Optimization Techniques

This article walks through practical MySQL performance tuning methods—including pagination, join, subquery, ORDER BY, GROUP BY, and COUNT optimizations—illustrated with real‑world data volumes, step‑by‑step SQL examples, EXPLAIN analyses, index creation, and measurable query‑time improvements.

IndexingORDER BYPerformance Tuning
0 likes · 14 min read
Boost MySQL Performance: Proven SQL Optimization Techniques
Baidu Tech Salon
Baidu Tech Salon
Nov 20, 2024 · Big Data

Optimizing Multi‑Dimensional User Count Computation in Feed Using Data Tagging

By deduplicating logs and assigning compact numeric tags to each user‑dimension combination, the data‑tagging method replaces costly lateral‑view expansions with a user‑level aggregation, cutting shuffle volume from terabytes to gigabytes and reducing runtime from 49 minutes to 14 minutes, enabling scalable multi‑dimensional user‑count analysis for Baidu Feed.

HivePerformance TuningSQL Optimization
0 likes · 14 min read
Optimizing Multi‑Dimensional User Count Computation in Feed Using Data Tagging
Architecture Digest
Architecture Digest
Nov 9, 2024 · Databases

MySQL Query Optimization Techniques and Common Pitfalls

This article examines frequent MySQL performance problems such as inefficient LIMIT pagination, implicit type conversion, sub‑query updates, mixed sorting, misuse of EXISTS, condition push‑down limitations, early result narrowing, intermediate result push‑down, and demonstrates how rewriting queries with JOINs, derived tables, and WITH clauses can dramatically improve execution speed.

IndexLIMITSQL Optimization
0 likes · 11 min read
MySQL Query Optimization Techniques and Common Pitfalls
macrozheng
macrozheng
Nov 7, 2024 · Backend Development

9 Proven Techniques to Supercharge Pagination Query Performance

This article presents nine practical strategies—including adding default filters, limiting page size, reducing joins, optimizing indexes, using straight_join, archiving data, leveraging count(*), querying ClickHouse, and implementing read‑write splitting—to dramatically improve the speed and scalability of pagination APIs in MySQL‑based back‑ends.

ClickHouseDatabase PerformanceIndexing
0 likes · 11 min read
9 Proven Techniques to Supercharge Pagination Query Performance
Architect's Guide
Architect's Guide
Oct 20, 2024 · Backend Development

Optimizing a High‑Concurrency Data Processing API: Reducing Response Time from 30 seconds to 0.8 seconds

This article presents a real‑world case study of a high‑concurrency transaction‑interception API handling over two million records, detailing the diagnosis of a 30‑second latency, SQL and Java‑level bottlenecks, and a series of optimizations—including PostgreSQL array aggregation, query refactoring, and Caffeine caching—that reduced response time to under one second.

SQL Optimization
0 likes · 10 min read
Optimizing a High‑Concurrency Data Processing API: Reducing Response Time from 30 seconds to 0.8 seconds
Spring Full-Stack Practical Cases
Spring Full-Stack Practical Cases
Oct 18, 2024 · Databases

Master MySQL Performance: 20 Essential SQL Optimization Techniques

This article presents a comprehensive guide for Java engineers on MySQL query optimization, covering index usage, avoiding SELECT *, proper JOINs, efficient WHERE clauses, LIMIT, EXISTS vs IN, function indexes, connection pooling, batch processing, data types, execution plan analysis, materialized views, and regular performance monitoring, all illustrated with practical code examples.

Performance TuningSQL Optimizationdatabase
0 likes · 19 min read
Master MySQL Performance: 20 Essential SQL Optimization Techniques