BestHub
Sign in
Tagged articles
68 articles
Page 1 of 1
Ops Community
Ops Community
May 19, 2026 · Databases

10 Common MySQL Index Failure Scenarios – Which Ones Affect You?

This article examines ten typical situations where MySQL indexes become ineffective, explains the underlying reasons such as function usage, implicit type conversion, wildcard LIKE patterns, OR conditions, left‑most prefix violations, small tables, stale statistics, NULL handling, GROUP BY/DISTINCT, and ORDER BY, and provides concrete SQL examples and step‑by‑step remediation techniques to restore optimal query performance.

Database TuningIndex OptimizationSQL Performance
0 likes · 32 min read
10 Common MySQL Index Failure Scenarios – Which Ones Affect You?
Architecture & Thinking
Architecture & Thinking
Apr 24, 2026 · Databases

SQL Index Failure Scenarios: Essential Pitfalls and Fixes Every Developer Must Know

This article systematically examines why MySQL indexes can become ineffective—covering function calls, implicit casts, LIKE patterns, OR conditions, composite‑index left‑most rules, data distribution, and ORDER/GROUP mismatches—while providing concrete code examples, production case studies, and actionable optimization techniques.

B+TreeDatabase TuningIndex Optimization
0 likes · 26 min read
SQL Index Failure Scenarios: Essential Pitfalls and Fixes Every Developer Must Know
Java Architect Handbook
Java Architect Handbook
Apr 6, 2026 · Databases

Why MySQL Indexes Still Slow Queries and How to Fix Them

This guide explains the six common reasons why MySQL indexes may fail to improve query speed, shows how interviewers evaluate index knowledge, and provides concrete SQL examples, EXPLAIN analysis, and practical optimization techniques such as redesigning indexes, using covering indexes, avoiding implicit type conversion, and tuning database configuration.

Data SkewDatabase InterviewIndex Optimization
0 likes · 15 min read
Why MySQL Indexes Still Slow Queries and How to Fix Them
dbaplus Community
dbaplus Community
Mar 26, 2026 · Databases

Six Fatal MySQL Index Traps and How to Avoid Them

A real‑world incident of soaring QPS reveals six common MySQL indexing pitfalls—type mismatches, function usage, left‑most prefix violations, implicit charset conversion, range query side effects, and optimizer mis‑selection—and provides concrete SQL fixes and verification tools to keep queries fast and reliable.

Database TuningIndex OptimizationQuery Pitfalls
0 likes · 6 min read
Six Fatal MySQL Index Traps and How to Avoid Them
Ray's Galactic Tech
Ray's Galactic Tech
Dec 30, 2025 · Databases

Why MySQL Indexes Fail and 14 Proven Fixes for Faster Queries

This article explains how MySQL indexes work, enumerates eight common reasons they become ineffective, adds six advanced pitfalls, and provides concrete diagnostic steps and production‑grade optimization techniques to keep queries fast and reliable.

Database TuningIndex OptimizationQuery Planning
0 likes · 9 min read
Why MySQL Indexes Fail and 14 Proven Fixes for Faster Queries
Ray's Galactic Tech
Ray's Galactic Tech
Dec 9, 2025 · Databases

30 Essential MySQL Index Optimization Techniques You Must Know

This guide presents 30 practical MySQL index optimization techniques, covering creation principles, usage tips, performance enhancements, and maintenance strategies, with concrete SQL examples for each rule to help developers improve query efficiency and reduce write overhead.

Database TuningIndex OptimizationQuery Planning
0 likes · 9 min read
30 Essential MySQL Index Optimization Techniques You Must Know
Top Architect
Top Architect
Nov 11, 2025 · Databases

Master MySQL Indexes: Types, Structures, and Performance Trade‑offs

This article explains MySQL index fundamentals, covering what indexes are, their advantages and drawbacks, underlying data structures such as hash tables and B‑/B+‑trees, and the classification of clustered, non‑clustered, primary, secondary, unique, prefix, and full‑text indexes, with practical SQL examples and diagrams.

B-TreeClustered IndexDatabase Index
0 likes · 9 min read
Master MySQL Indexes: Types, Structures, and Performance Trade‑offs
Sohu Tech Products
Sohu Tech Products
Oct 9, 2025 · Databases

When to Use SELECT DISTINCT vs GROUP BY in MySQL: Performance & Use Cases

This article compares MySQL’s SELECT DISTINCT and GROUP BY clauses, explaining their syntax, functional differences, performance implications, and ideal scenarios through detailed examples, index usage analysis, and a feature comparison table, helping developers choose the right approach for deduplication or aggregation tasks.

GROUP BYSELECT DISTINCTSQL Performance
0 likes · 10 min read
When to Use SELECT DISTINCT vs GROUP BY in MySQL: Performance & Use Cases
Senior Brother's Insights
Senior Brother's Insights
Sep 18, 2025 · Databases

Why MySQL Table Lookups Slow Queries and How to Eliminate Them

This article explains the concept of table lookups in MySQL, shows how they arise from using secondary indexes, provides concrete query examples, and offers practical optimization techniques such as creating covering indexes, reducing selected columns, and analyzing execution plans to improve performance.

InnoDBSQL PerformanceTable Lookup
0 likes · 9 min read
Why MySQL Table Lookups Slow Queries and How to Eliminate Them
Architect's Guide
Architect's Guide
Sep 14, 2025 · Databases

Why Database Indexes Speed Up Queries: From Storage Basics to Binary Search

This article explains how databases store data on various storage devices, why indexes dramatically improve query performance through sorted structures and binary search, and outlines practical SQL optimization techniques while warning about the trade‑offs of excessive indexing.

Binary SearchClustered IndexDatabase Index
0 likes · 11 min read
Why Database Indexes Speed Up Queries: From Storage Basics to Binary Search
Aikesheng Open Source Community
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 ANALYZESQL Performance
0 likes · 11 min read
Visualizing MySQL Execution Plans with Flame Graphs
JD Tech Talk
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.

Database OptimizationLocal JoinReplacingMergeTree
0 likes · 10 min read
Optimizing Supply Chain Planning Systems: ClickHouse ReplacingMergeTree and Local Join Solutions
Su San Talks Tech
Su San Talks Tech
Mar 30, 2025 · Databases

How to Supercharge Large Database Tables: Proven Optimization Techniques

This article explains why massive tables become slow, identifies common bottlenecks such as disk I/O, missing indexes, deep pagination and lock contention, and provides a step‑by‑step guide covering table design, indexing, SQL tuning, sharding, caching and a real‑world case study to dramatically improve performance.

Index TuningLarge TablesSQL Performance
0 likes · 13 min read
How to Supercharge Large Database Tables: Proven Optimization Techniques
Aikesheng Open Source Community
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.

SQL Performanceexplainmysql
0 likes · 10 min read
How to Interpret the rows Value in MySQL EXPLAIN Output
DataFunSummit
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 DataSQL Performance
0 likes · 13 min read
Spark SQL Window Function Optimizations: Concepts, Techniques, and Q&A
Java Architecture Stack
Java Architecture Stack
Dec 23, 2024 · Databases

10 Real-World MySQL Subquery Optimizations to Boost Performance

This article explains why MySQL subqueries can hurt performance, outlines common pitfalls such as temporary tables, index loss, and optimizer complexity, and presents ten concrete examples that replace subqueries with IN, EXISTS, JOIN, indexes, temporary tables, window functions, and LIMIT to achieve faster, more maintainable queries.

Database TuningJOINSQL Performance
0 likes · 10 min read
10 Real-World MySQL Subquery Optimizations to Boost Performance
Ctrip Technology
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.

Data PlatformSQL Performancecaching
0 likes · 26 min read
Performance Governance and Optimization of Ctrip's Nova Data Reporting Platform
Java Backend Technology
Java Backend Technology
Sep 11, 2024 · Databases

When to Use DISTINCT vs GROUP BY in MySQL: Performance Insights

This article compares MySQL's DISTINCT and GROUP BY clauses, detailing when they deliver identical performance with indexed columns, why DISTINCT can be faster without indexes, how each handles NULLs and multi‑column deduplication, and provides practical syntax examples and optimization guidance.

DISTINCTGROUP BYIndex Optimization
0 likes · 10 min read
When to Use DISTINCT vs GROUP BY in MySQL: Performance Insights
Java Interview Crash Guide
Java Interview Crash Guide
Jul 24, 2024 · Databases

How to Supercharge MySQL Queries: 8 Proven Optimization Techniques

Learn eight practical MySQL optimization strategies—including smarter LIMIT usage, avoiding implicit type conversion, rewriting subqueries with JOINs, handling mixed sorting, eliminating costly EXISTS clauses, pushing conditions, pre‑filtering data, and leveraging intermediate result sets—to dramatically reduce query execution time from seconds to milliseconds.

LIMITSQL PerformanceWITH clause
0 likes · 15 min read
How to Supercharge MySQL Queries: 8 Proven Optimization Techniques
ITPUB
ITPUB
Jul 3, 2024 · Databases

Why Skipping Secondary Indexes Can Speed Up MySQL Queries: A Real‑World Example

A client complained about a 35‑second MySQL query that ignored indexes; by analyzing column selectivity and rewriting the condition to use the auto‑increment primary key instead of a secondary index, the execution time dropped to 2.55 seconds, illustrating why primary‑key access is often faster.

Database TuningIndex OptimizationSQL Performance
0 likes · 5 min read
Why Skipping Secondary Indexes Can Speed Up MySQL Queries: A Real‑World Example
ITPUB
ITPUB
Jan 3, 2024 · Databases

Why MySQL Queries Can Be 50,000× Faster with Proper Indexing

This article walks through a MySQL 5.6 scenario where a sub‑query that scans 700,000 rows takes over 30,000 seconds, then demonstrates how adding single‑column and composite indexes, rewriting the query as a join, and analyzing execution plans reduces the runtime to milliseconds, highlighting best‑practice indexing techniques.

Index OptimizationQuery TuningSQL Performance
0 likes · 12 min read
Why MySQL Queries Can Be 50,000× Faster with Proper Indexing
Huawei Cloud Developer Alliance
Huawei Cloud Developer Alliance
Aug 25, 2023 · Databases

Unlock GaussDB(DWS) Performance: Expert Tips for Resource Management

This announcement introduces Huawei Cloud’s DTT live session on August 29, where expert Lv Pengbo will explain GaussDB(DWS) resource control principles and demonstrate practical techniques—such as CPU usage analysis, memory tuning, and queue issue resolution—to help developers efficiently manage data‑warehouse resources and boost performance.

Database TuningGaussDBHuawei Cloud
0 likes · 3 min read
Unlock GaussDB(DWS) Performance: Expert Tips for Resource Management
Efficient Ops
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.

Database AdministrationRelational DatabasesSQL Performance
0 likes · 9 min read
Elevating DBA Skills: Practical Insights from a Bank’s Database Team
Su San Talks Tech
Su San Talks Tech
Jul 8, 2023 · Databases

Master MySQL Index Optimization: 20 Proven Rules to Boost Query Performance

This comprehensive guide explains 20 practical MySQL index optimization rules—including when NOT to use indexes, how to choose the right index type, and techniques such as prefix indexes, covering indexes, and query rewriting—to dramatically improve SQL query efficiency and reduce database load.

Database TuningIndex OptimizationSQL Performance
0 likes · 19 min read
Master MySQL Index Optimization: 20 Proven Rules to Boost Query Performance
macrozheng
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.

Batch InsertExecutorType.BATCHMyBatis
0 likes · 9 min read
Boost MyBatis Batch Insert Speed: Avoid foreach Pitfalls and Use ExecutorType.BATCH
Java High-Performance Architecture
Java High-Performance Architecture
Mar 15, 2023 · Databases

Why MySQL’s COUNT(*) May Use an Index and When It Chooses Full Table Scan

This article examines how MySQL decides whether to use an index or perform a full table scan for COUNT(*) and other queries, explains the IO and CPU cost model, demonstrates the calculations with a sample table, and shows how optimizer trace can reveal mismatches between estimated and actual performance.

Optimizer_traceSQL Performancecost estimation
0 likes · 10 min read
Why MySQL’s COUNT(*) May Use an Index and When It Chooses Full Table Scan
Laravel Tech Community
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 BYSQL Performance
0 likes · 8 min read
DISTINCT vs GROUP BY in MySQL: Performance and Usage Guide
ITPUB
ITPUB
Dec 29, 2022 · Databases

How TDSQL‑C Accelerates Parallel Queries in Cloud‑Native Databases

This transcript details the design, implementation, and performance evaluation of TDSQL‑C's parallel query feature, covering product background, workload challenges, execution plans, Amdahl's law analysis, task scheduling, data partitioning, and future enhancements for cloud‑native database systems.

Amdahl's LawData PartitioningParallel Query
0 likes · 17 min read
How TDSQL‑C Accelerates Parallel Queries in Cloud‑Native Databases
dbaplus Community
dbaplus Community
Oct 24, 2022 · Databases

Mastering MySQL Slow Query Optimization: Practical Strategies from Ctrip

Facing a surge in MySQL slow queries, Ctrip’s senior database engineers detail a comprehensive approach—including refined SQL release processes, deep execution‑plan analysis, index tuning, query rewriting, and resource management—to systematically identify, diagnose, and eliminate performance bottlenecks in large‑scale hotel services.

Database TuningIndex OptimizationSQL Performance
0 likes · 14 min read
Mastering MySQL Slow Query Optimization: Practical Strategies from Ctrip
Ctrip Technology
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 OptimizationQuery PlanningSQL Performance
0 likes · 10 min read
Practical Slow Query Optimization for MySQL at Ctrip
DataFunSummit
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
Alibaba Cloud Native
Alibaba Cloud Native
Jul 19, 2022 · Cloud Native

Mastering Database Governance in Cloud‑Native Microservices: Scenarios & Solutions

This article outlines critical database governance scenarios in cloud‑native microservice architectures, explains why database performance is a top priority, and details practical solutions such as slow‑SQL insight, flow‑control, connection‑pool management, and gray‑release techniques using MSE and OpenSergo standards.

Cloud NativeDatabase GovernanceMSE
0 likes · 12 min read
Mastering Database Governance in Cloud‑Native Microservices: Scenarios & Solutions
dbaplus Community
dbaplus Community
Jan 16, 2022 · Databases

Essential MySQL Query Optimization Tips to Avoid Full Table Scans

This guide compiles practical MySQL performance techniques—including proper indexing, avoiding costly operators, rewriting OR conditions, using EXISTS, limiting SELECT *, managing temporary tables, configuring threads, choosing storage engines, and optimizing data types—to dramatically reduce full‑table scans and improve query speed.

Database designSQL Performanceindexes
0 likes · 21 min read
Essential MySQL Query Optimization Tips to Avoid Full Table Scans
Programmer DD
Programmer DD
Sep 13, 2021 · Databases

COUNT(*), COUNT(1) or COUNT(column) in MySQL? Which Is Faster?

This article compares MySQL's COUNT(*), COUNT(1) and COUNT(column) functions, explains how each handles NULL values, analyzes performance differences on MyISAM and InnoDB engines, and provides practical recommendations for writing efficient count queries and existence checks.

COUNT(1)COUNT(column)InnoDB
0 likes · 9 min read
COUNT(*), COUNT(1) or COUNT(column) in MySQL? Which Is Faster?
Qunar Tech Salon
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 AdministrationSQL Performance
0 likes · 24 min read
Implementing Transparent Table Partitioning in PostgreSQL: Strategies, Code Samples, and Performance Gains
ITPUB
ITPUB
Jul 2, 2021 · Databases

Boost MySQL Bulk Insert and Query Performance with Proven Optimizations

This article presents practical MySQL optimization techniques—including disabling keys for MyISAM, sorting InnoDB input, adjusting UNIQUE_CHECKS and AUTOCOMMIT, using multi‑row INSERT, LOAD DATA INFILE, ORDER BY NULL for GROUP BY, index‑aware ORDER BY, OR condition handling, and SQL hints—to dramatically speed up bulk data loads and common queries.

Bulk InsertSQL Performancemysql
0 likes · 13 min read
Boost MySQL Bulk Insert and Query Performance with Proven Optimizations
Laravel Tech Community
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 OptimizationOrder BySQL Performance
0 likes · 6 min read
Understanding and Optimizing MySQL ORDER BY Execution
Liangxu Linux
Liangxu Linux
May 7, 2021 · Databases

Why a Missing Index Parameter Nearly Crashed Our Production Database

A production MySQL server suffered CPU overload and half‑hour alerts because a high‑volume query skipped a crucial composite index field, leading to full‑table scans; the post explains the root cause, the temporary index fix, the left‑most index principle, code validation oversights, and lessons learned.

Composite IndexDatabase DebuggingJava Validation
0 likes · 8 min read
Why a Missing Index Parameter Nearly Crashed Our Production Database
Java Interview Crash Guide
Java Interview Crash Guide
Mar 24, 2021 · Databases

Top 20 Proven Techniques to Optimize Your Database Performance

This article compiles essential database optimization strategies—including proper index creation, composite and short indexes, avoiding full‑table scans, efficient use of temporary tables and cursors, and best practices for query writing—to dramatically improve query speed and overall system throughput.

SQL Performancecursorsindexes
0 likes · 11 min read
Top 20 Proven Techniques to Optimize Your Database Performance
Architecture Digest
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.

Code reviewSQL Performancebug fix
0 likes · 8 min read
Debugging a High‑Load SQL Query: Index Miss and Temporary Fix
ITPUB
ITPUB
Jan 16, 2021 · Databases

Why a Missing Index Parameter Crashed Our Production DB—and How to Fix It

During a production outage, a high‑volume SQL query without a required leftmost index field caused full table scans that saturated CPU, prompting a temporary index fix and revealing a deeper bug involving missing @NotNull validation and improper parameter handling, highlighting MySQL’s left‑most index rule.

SQL Performancebug fixindexing
0 likes · 9 min read
Why a Missing Index Parameter Crashed Our Production DB—and How to Fix It
Java Interview Crash Guide
Java Interview Crash Guide
Jan 12, 2021 · Databases

Boost MySQL Query Speed: Index Tricks, Join vs Subquery, and Execution Plan Hacks

This article walks through a MySQL 5.6 case study where a nested sub‑query for finding students scoring 100 in a specific subject runs for hours, then demonstrates how adding single‑column and composite indexes, rewriting the query as a join, and analyzing execution plans can shrink the runtime from tens of thousands of seconds to under a second.

Database TuningIndex OptimizationQuery Planning
0 likes · 11 min read
Boost MySQL Query Speed: Index Tricks, Join vs Subquery, and Execution Plan Hacks
Big Data Technology & Architecture
Big Data Technology & Architecture
Sep 24, 2020 · Big Data

HiveSQL Classic Optimization Cases: Partitioning, Subset Decomposition, and Percentile Approximation Improvements

This article presents three HiveSQL optimization case studies—restructuring a large‑scale query with partitioned tables, breaking a complex window‑function query into smaller subsets with joins, and refactoring excessive PERCENTILE_APPROX calls—demonstrating how each change reduces execution time from hours to minutes and improves overall performance.

Big DataHiveSQLPartitioning
0 likes · 9 min read
HiveSQL Classic Optimization Cases: Partitioning, Subset Decomposition, and Percentile Approximation Improvements
Aikesheng Open Source Community
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 OptimizationQuery RewriteSQL Performance
0 likes · 8 min read
Using MySQL 8.0 Statement Digest and Digest Text Functions for SQL Summarization
ITPUB
ITPUB
Sep 8, 2020 · Databases

Why a Missing Index Parameter Crashed Our Production MySQL Database

A production MySQL server was overwhelmed by a high‑volume query that ignored a crucial composite index because the leftmost key was missing, leading to full‑table scans, CPU saturation, and a half‑hour outage, and the post explains how the issue was diagnosed and fixed.

Composite IndexJava ValidationLeftmost Matching
0 likes · 9 min read
Why a Missing Index Parameter Crashed Our Production MySQL Database
ITPUB
ITPUB
Sep 7, 2020 · Databases

Why a Missing Index Parameter Crashed Our Production MySQL and How We Fixed It

A production MySQL server was overwhelmed by a full‑table‑scan query that omitted the leftmost index column, causing CPU saturation and read‑only alerts; the article explains the root cause, temporary mitigation, the leftmost‑match principle, code‑level bug, and key lessons learned.

Java ValidationSQL Performancebug fix
0 likes · 9 min read
Why a Missing Index Parameter Crashed Our Production MySQL and How We Fixed It
Top Architect
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 SELECTSQL Performanceindexing
0 likes · 7 min read
Case Study: Insert‑Into‑Select Migration Failure and Resolution in MySQL
Aikesheng Open Source Community
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 ConfigurationSQL Performance
0 likes · 4 min read
Diagnosing Slow SQL Execution in DBLE Due to Incorrect Stringhash Partition Configuration
macrozheng
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.

Index OptimizationOptimizer_traceSQL Performance
0 likes · 10 min read
Why MySQL’s Index Choice Can Miss the Best Plan – A Deep Cost Analysis
Java Backend Technology
Java Backend Technology
Nov 2, 2019 · Databases

How Indexes Can Slash MySQL Query Time from Hours to Milliseconds

This article walks through a MySQL 5.6 scenario where a nested subquery to find students scoring 100 in Chinese runs for over 30,000 seconds, then demonstrates how adding single‑column and multi‑column indexes, analyzing execution plans, and applying join optimizations can reduce the runtime to milliseconds, while also covering best practices for index design and query tuning.

Query PlanSQL Performancedatabase indexing
0 likes · 9 min read
How Indexes Can Slash MySQL Query Time from Hours to Milliseconds
Qunar Tech Salon
Qunar Tech Salon
Aug 13, 2019 · Databases

Efficient Deduplication of Large MySQL Tables Using Indexes, Variables, and Window Functions

This article demonstrates how to efficiently remove duplicate rows from a million‑record MySQL table by comparing created_time and item_name, exploring various approaches such as correlated subqueries, joins, user‑defined variables, index optimization, window functions, and parallel execution with shell scripts and MySQL events to achieve significant performance gains.

Parallel ExecutionSQL Performancededuplication
0 likes · 21 min read
Efficient Deduplication of Large MySQL Tables Using Indexes, Variables, and Window Functions
ITPUB
ITPUB
Jul 8, 2019 · Databases

How to Supercharge MySQL Queries with Index Optimization: A Step‑by‑Step Guide

This article walks through a real‑world MySQL scenario, showing how a slow sub‑query can be accelerated by adding single‑column indexes, converting the query to a join, and finally creating a composite index, with performance numbers and execution‑plan analysis to illustrate each optimization step.

Database TuningIndex OptimizationSQL Performance
0 likes · 11 min read
How to Supercharge MySQL Queries with Index Optimization: A Step‑by‑Step Guide
Efficient Ops
Efficient Ops
May 7, 2019 · Databases

Master MySQL: Essential Tips for Data Types, Indexes, and Query Optimization

This guide compiles essential MySQL 5.5 techniques for teams without a DBA, covering storage engine characteristics, data type selection, index design—including composite, prefix, and covering indexes—and practical query optimizations such as reducing scanned rows, avoiding redundant indexes, and improving execution plans.

Data TypesDatabase OptimizationSQL Performance
0 likes · 22 min read
Master MySQL: Essential Tips for Data Types, Indexes, and Query Optimization
Java Captain
Java Captain
May 19, 2018 · Databases

MySQL Index Optimization and Performance Analysis

This article explains MySQL index fundamentals, demonstrates performance issues with slow queries, and walks through practical optimization cases using EXPLAIN, covering index creation, covering indexes, and composite indexes, and best practices for when to add or avoid indexes.

Index OptimizationSQL Performancedatabase
0 likes · 17 min read
MySQL Index Optimization and Performance Analysis
ITPUB
ITPUB
Jan 2, 2018 · Databases

10 Proven Techniques to Supercharge Your Database Performance

Learn ten practical strategies to accelerate database queries—including proper schema design, using EXPLAIN, caching, selective column retrieval, LIMIT clauses, avoiding loops, replacing subqueries with joins, careful wildcard use, UNION over OR, and indexing—to reduce server load and improve application responsiveness.

Query TuningSQL Performanceindexing
0 likes · 9 min read
10 Proven Techniques to Supercharge Your Database Performance
ITPUB
ITPUB
Sep 23, 2016 · Databases

Master MySQL Performance: Practical Table, Index, and Connection Pool Optimizations

This article presents practical MySQL optimization techniques covering table schema design, character set choices, primary key strategies, field definitions, index creation, SQL query tuning, pagination tricks, join handling, connection‑pool parameter tuning, and historical data archiving for large‑scale databases.

Connection PoolDatabase OptimizationSQL Performance
0 likes · 13 min read
Master MySQL Performance: Practical Table, Index, and Connection Pool Optimizations
ITPUB
ITPUB
Nov 19, 2015 · Databases

Essential MySQL & Oracle Development Guidelines for Tables, Indexes, and SQL

This guide presents practical recommendations for MySQL and Oracle development, covering table design, appropriate data types, indexing best practices, and efficient SQL patterns to improve performance, maintainability, and security.

Database designIndex OptimizationSQL Performance
0 likes · 7 min read
Essential MySQL & Oracle Development Guidelines for Tables, Indexes, and SQL