BestHub
Sign in
Tagged articles
205 articles
Page 1 of 3
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?
Su San Talks Tech
Su San Talks Tech
May 17, 2026 · Databases

Why Leading Companies Avoid NULL Values in MySQL

The article explains why major tech companies discourage using NULL in MySQL, covering its meaning as an unknown state, three-valued logic pitfalls such as NOT IN subqueries, index inefficiencies, aggregate function quirks, storage overhead, Java handling issues, and offers practical alternatives like NOT NULL constraints with sensible defaults.

Database designIndex OptimizationNULL
0 likes · 11 min read
Why Leading Companies Avoid NULL Values in MySQL
Ops Community
Ops Community
May 2, 2026 · Databases

How to Completely Resolve MySQL CPU Spikes: Real‑World Fault Replay and Optimization Guide

This article walks you through a systematic, step‑by‑step process for diagnosing and fixing MySQL CPU usage spikes—from identifying the symptoms and gathering system metrics, to pinpointing problematic queries, analyzing locks and buffers, applying index and configuration tweaks, and validating the performance gains with real‑world examples and command‑line tools.

CPUIndex Optimizationdatabase
0 likes · 44 min read
How to Completely Resolve MySQL CPU Spikes: Real‑World Fault Replay and Optimization Guide
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
MaGe Linux Operations
MaGe Linux Operations
Apr 23, 2026 · Databases

How to Diagnose and Optimize MySQL Slow Queries Beyond Adding Indexes

This guide walks through a systematic approach to identify, analyze, and fix MySQL slow queries by enabling the slow‑query log, interpreting its format, using tools like mysqldumpslow and pt‑query‑digest, examining execution plans with EXPLAIN, designing proper indexes, rewriting SQL, tuning server parameters, and establishing continuous monitoring to prevent regressions.

Index Optimizationexplainmysql
0 likes · 34 min read
How to Diagnose and Optimize MySQL Slow Queries Beyond Adding Indexes
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
Spring Full-Stack Practical Cases
Spring Full-Stack Practical Cases
Apr 3, 2026 · Databases

10 Must‑Know MySQL Advanced Techniques to Supercharge Performance

This article presents ten practical MySQL advanced techniques—including EXPLAIN analysis, composite and covering indexes, index condition push‑down, prefix indexes, window functions, CTEs, JSON handling, partition tables, join optimizations, variables, online DDL, and generated columns—each illustrated with real‑world SQL examples and execution‑plan screenshots to boost efficiency in high‑volume, high‑concurrency environments.

Advanced SQLCTEIndex Optimization
0 likes · 16 min read
10 Must‑Know MySQL Advanced Techniques to Supercharge Performance
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
JavaGuide
JavaGuide
Mar 9, 2026 · Databases

Does SELECT * Always Invalidate Indexes? Why Some Teams Ban It

While many believe that using SELECT * inevitably disables indexes, MySQL’s optimizer only skips indexes when a full table scan is cheaper, and patterns such as violating the left‑most prefix, applying functions or implicit casts, leading‑wildcard LIKE, OR/IN misuse, and mismatched ORDER BY can cause index invalidation, prompting some companies to forbid SELECT * in production.

Index OptimizationSQLdatabase
0 likes · 14 min read
Does SELECT * Always Invalidate Indexes? Why Some Teams Ban It
Ops Community
Ops Community
Mar 6, 2026 · Databases

Master MySQL Index Optimization: Deep Dive into EXPLAIN Execution Plans

This article provides a hands‑on guide to MySQL index optimization, deeply exploring EXPLAIN execution plans, new features in MySQL 8/8.4, practical index design principles, step‑by‑step SQL examples, common pitfalls, advanced techniques like functional and descending indexes, and comprehensive monitoring and troubleshooting strategies.

Database PerformanceIndex OptimizationSQL Tuning
0 likes · 28 min read
Master MySQL Index Optimization: Deep Dive into EXPLAIN Execution Plans
MaGe Linux Operations
MaGe Linux Operations
Mar 4, 2026 · Databases

Master MySQL Performance: From Slow Query Analysis to Index Tuning

This guide walks through the full MySQL performance optimization workflow, covering slow‑query logging, pt‑query‑digest analysis, EXPLAIN interpretation, index design (including covering indexes and index‑condition pushdown), InnoDB buffer‑pool tuning, connection‑pool settings, real‑time diagnostics, monitoring metrics, and best‑practice recommendations for large‑scale production databases.

Database TuningIndex Optimizationmysql
0 likes · 22 min read
Master MySQL Performance: From Slow Query Analysis to Index Tuning
Architect Chen
Architect Chen
Feb 13, 2026 · Databases

Boost MySQL Performance: Proven Tuning, Indexing, and Scaling Strategies

This guide presents practical MySQL optimization techniques—including SQL and index refinement, InnoDB and connection parameter tuning, cache layer integration, and architectural scaling with read‑write splitting and sharding—to dramatically increase query throughput and reduce latency.

Index OptimizationInnoDBmysql
0 likes · 6 min read
Boost MySQL Performance: Proven Tuning, Indexing, and Scaling Strategies
Ray's Galactic Tech
Ray's Galactic Tech
Jan 17, 2026 · Databases

Master MySQL Index Execution: From Index Key to Table Filter

This guide explains how MySQL processes queries in three index‑related stages—Index Key, Index Filter, and Table Filter—showing why keeping operations in the index layer dramatically improves performance and how to design and diagnose indexes effectively.

Database PerformanceIndex OptimizationSQL
0 likes · 9 min read
Master MySQL Index Execution: From Index Key to Table Filter
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
Java Tech Enthusiast
Java Tech Enthusiast
Dec 6, 2025 · Databases

6 Critical MySQL Pitfalls and How to Avoid Them

Learn how to prevent six frequent MySQL problems—including index misuse, transaction isolation anomalies, inefficient pagination, charset and collation errors, risky foreign-key cascades, and misconfigured connection pools—through detailed explanations, code examples, and practical mitigation strategies.

CharsetConnection PoolDatabase Performance
0 likes · 22 min read
6 Critical MySQL Pitfalls and How to Avoid Them
Ray's Galactic Tech
Ray's Galactic Tech
Nov 27, 2025 · Databases

Why Implicit Type Conversion Breaks Indexes and How to Fix It

This article explains how implicit type conversion can silently invalidate database indexes, outlines the underlying rules and type‑precedence logic, provides real‑world MySQL examples, shows how to detect the issue with EXPLAIN, and offers concrete best‑practice solutions.

Implicit ConversionIndex OptimizationSQL
0 likes · 10 min read
Why Implicit Type Conversion Breaks Indexes and How to Fix It
Java Architect Handbook
Java Architect Handbook
Nov 26, 2025 · Databases

Why UUID Primary Keys Slow Down MySQL and How to Optimize Them

This article explains the performance drawbacks of using UUIDs as primary keys in MySQL—including larger indexes, random inserts, and slower comparisons—details why data updates trigger index refreshes, and offers practical optimization techniques such as ordered UUIDs, binary storage, hybrid auto‑increment keys, and table partitioning.

Binary StorageDatabase PerformanceIndex Optimization
0 likes · 8 min read
Why UUID Primary Keys Slow Down MySQL and How to Optimize Them
MaGe Linux Operations
MaGe Linux Operations
Nov 25, 2025 · Databases

10 Common MySQL Index Failure Scenarios and How to Fix Them

This comprehensive guide explains why MySQL indexes often become ineffective, outlines ten typical failure scenarios with concrete EXPLAIN examples, and provides practical solutions, scripts, best‑practice recommendations, and monitoring techniques to dramatically improve slow‑query performance.

Database PerformanceIndex OptimizationSQL Tuning
0 likes · 35 min read
10 Common MySQL Index Failure Scenarios and How to Fix Them
Ops Community
Ops Community
Nov 22, 2025 · Databases

Why Is Your SQL Slow? 7 Common MySQL Index Failure Scenarios Explained

This article examines why MySQL queries can become sluggish by analyzing seven typical situations where indexes become ineffective, provides step‑by‑step testing procedures, performance comparisons, troubleshooting tips, and best‑practice recommendations to restore optimal query performance.

Index OptimizationSQLmysql
0 likes · 37 min read
Why Is Your SQL Slow? 7 Common MySQL Index Failure Scenarios Explained
DaTaobao Tech
DaTaobao Tech
Nov 19, 2025 · Databases

Mastering MySQL Slow Query Diagnosis and Index Optimization

This article walks through a real‑world slow‑SQL case on a massive e‑commerce order table, explains B+Tree index structures, index height estimation, index push‑down, sorting strategies, EXPLAIN and Query Profiler usage, and provides a step‑by‑step SOP for safe index changes and validation.

B+TreeIndex OptimizationQuery Profiling
0 likes · 40 min read
Mastering MySQL Slow Query Diagnosis and Index Optimization
MaGe Linux Operations
MaGe Linux Operations
Oct 29, 2025 · Databases

Master MySQL Slow Query Analysis & Optimization: A Practical Guide

This guide walks through enabling MySQL slow query logging, analyzing logs with pt‑query‑digest, interpreting EXPLAIN output, applying index and SQL optimizations, tuning database parameters, and setting up Prometheus monitoring, culminating in a real‑world order‑query case that reduces execution time from seconds to milliseconds.

Index Optimizationexplainmysql
0 likes · 17 min read
Master MySQL Slow Query Analysis & Optimization: A Practical Guide
Java Tech Enthusiast
Java Tech Enthusiast
Oct 13, 2025 · Databases

Why OR Queries Break MySQL Indexes and How to Fix Them

This article explains how MySQL OR conditions can cause index loss, demonstrates typical scenarios with multi‑field, range, and dynamic filters, and provides practical solutions such as using UNION/UNION ALL or adding appropriate indexes to restore efficient query performance.

Index OptimizationOR querySQL
0 likes · 7 min read
Why OR Queries Break MySQL Indexes and How to Fix Them
Senior Brother's Insights
Senior Brother's Insights
Oct 7, 2025 · Databases

Why MySQL OR Queries Often Skip Indexes—and How to Fix Them

This article explains how MySQL's OR conditions can cause index loss, provides concrete query examples, analyzes why the optimizer falls back to full table scans, and offers practical solutions such as rewriting with UNION or adding missing indexes to restore efficient index usage.

Index OptimizationOR querySQL
0 likes · 7 min read
Why MySQL OR Queries Often Skip Indexes—and How to Fix Them
Ops Community
Ops Community
Oct 5, 2025 · Databases

Master MySQL Slow Query Optimization: Proven Methods & Pitfall Guide

This comprehensive guide walks you through a systematic methodology for diagnosing and fixing MySQL slow queries, covering proper log configuration, EXPLAIN analysis, index design, query rewriting, table restructuring, and practical caching techniques, while highlighting common misconceptions and real‑world case studies.

Index Optimizationdatabasemysql
0 likes · 35 min read
Master MySQL Slow Query Optimization: Proven Methods & Pitfall Guide
NiuNiu MaTe
NiuNiu MaTe
Sep 17, 2025 · Databases

Why MySQL Indexes Fail and How to Fix Them: 12 Common Scenarios

This article explains the core purpose of MySQL indexes, enumerates twelve typical situations that cause index failure, provides concrete SQL examples and corrections, and shows how to use EXPLAIN to diagnose and verify index usage for better query performance.

Database PerformanceIndex OptimizationSQL
0 likes · 16 min read
Why MySQL Indexes Fail and How to Fix Them: 12 Common Scenarios
MaGe Linux Operations
MaGe Linux Operations
Sep 13, 2025 · Databases

Master MySQL Performance: From Slow Queries to Billion-Row Optimization

This comprehensive guide walks you through real-world MySQL performance tuning, covering slow query analysis, index design, query optimization, parameter tuning, hardware considerations, sharding, and fault handling, with practical scripts and case studies to help you transform sluggish databases into high‑throughput, scalable systems.

Index Optimizationdatabase scalingmysql
0 likes · 24 min read
Master MySQL Performance: From Slow Queries to Billion-Row Optimization
Tech Freedom Circle
Tech Freedom Circle
Aug 26, 2025 · Artificial Intelligence

How to Optimize RAG for Alibaba Interviews? 7 Golden Rules Explained

This article provides a step‑by‑step technical guide to optimizing Retrieval‑Augmented Generation (RAG) for interview scenarios, covering query rewriting, HyDE, fallback strategies, routing and prompt routing, multi‑representation indexing, hybrid retrieval, re‑ranking, self‑RAG, generation control, performance benchmarking, and a practical checklist with concrete code examples and metrics.

AI InterviewHybrid RetrievalIndex Optimization
0 likes · 30 min read
How to Optimize RAG for Alibaba Interviews? 7 Golden Rules Explained
MaGe Linux Operations
MaGe Linux Operations
Aug 1, 2025 · Databases

Master MySQL Slow Query Fixes: From Index Tuning to Distributed Sharding

This comprehensive guide walks MySQL administrators through diagnosing slow queries, configuring the slow‑query log, leveraging EXPLAIN, optimizing indexes, rewriting joins, implementing pagination tricks, and scaling with read‑write splitting, master‑slave replication, and horizontal sharding to dramatically improve performance in high‑traffic production environments.

Index Optimizationmysqlsharding
0 likes · 21 min read
Master MySQL Slow Query Fixes: From Index Tuning to Distributed Sharding
Senior Tony
Senior Tony
Jul 19, 2025 · Databases

Why Your MySQL Index Fails: 8 Common Pitfalls and How to Fix Them

This article examines eight typical situations that cause MySQL indexes to become ineffective—such as violating the leftmost‑prefix rule, leading‑wildcard LIKE patterns, OR conditions, functions, calculations, implicit type conversions, large result sets, and mismatched ORDER BY directions—providing SQL examples, execution‑plan screenshots, and practical explanations.

Index OptimizationSQLdatabase
0 likes · 11 min read
Why Your MySQL Index Fails: 8 Common Pitfalls and How to Fix Them
Tech Freedom Circle
Tech Freedom Circle
Jul 15, 2025 · Databases

One‑Order Four‑Split: Solving MySQL Deadlocks in Million‑Row Imports

During massive daily imports of up to one million reconciliation records, MySQL deadlocks can cripple performance; this article dissects the four classic deadlock conditions, then presents a systematic “One‑Order Four‑Split” strategy—ordered writes, transaction splitting, index management, partitioning, and gap‑lock removal—backed by benchmarks, code samples, and configuration tweaks that cut lock rates by up to 90% and reduce batch times to under ten minutes.

Batch InsertIndex OptimizationJava
0 likes · 27 min read
One‑Order Four‑Split: Solving MySQL Deadlocks in Million‑Row Imports
Java Captain
Java Captain
Jun 10, 2025 · Databases

Master MySQL Performance: Slow Queries, Indexes, Transactions & Replication

This article explains how to locate and analyze MySQL slow queries, understand index structures and types, apply best practices for index creation, handle large pagination, manage transaction isolation and MVCC, and implement master‑slave replication and sharding strategies for high‑performance databases.

Index Optimizationmysqlsharding
0 likes · 13 min read
Master MySQL Performance: Slow Queries, Indexes, Transactions & Replication
Su San Talks Tech
Su San Talks Tech
May 27, 2025 · Databases

Why MySQL Picks the Wrong Index and How to Fix It

Learn how MySQL’s optimizer decides between indexes, why the same query can suddenly use a different index with dramatically slower performance, and practical steps—including analyzing execution plans, updating statistics, checking index coverage, and rebuilding fragmented indexes—to diagnose and resolve index selection issues.

Database TuningIndex Optimizationmysql
0 likes · 9 min read
Why MySQL Picks the Wrong Index and How to Fix It
Java Tech Enthusiast
Java Tech Enthusiast
Apr 4, 2025 · Databases

MySQL Large Table Index Optimization and Batch Deletion Strategies

By dropping a low‑selectivity composite index, adding targeted indexes on product_id‑sequence‑station_no and receive_time, and using online DDL with pt‑osc, the team reduced row scans and replication lag, then implemented batch deletions by primary‑key ranges, cutting delete time from minutes to seconds on a 100‑million‑row table.

Batch DeletionIndex OptimizationLarge Tables
0 likes · 14 min read
MySQL Large Table Index Optimization and Batch Deletion Strategies
IT Services Circle
IT Services Circle
Apr 3, 2025 · Databases

Six Common MySQL Index Pitfalls and Their Remedies

This article examines six common MySQL indexing pitfalls—type mismatches, function usage, left‑prefix violations, implicit charset conversions, range query issues, and optimizer mis‑choices—and provides concrete SQL fixes and verification tools to improve query performance.

Index OptimizationSQLdatabase
0 likes · 5 min read
Six Common MySQL Index Pitfalls and Their Remedies
Cognitive Technology Team
Cognitive Technology Team
Mar 29, 2025 · Databases

MySQL Multi-Range Read (MRR) Optimization: Advantages, Limitations, Use Cases, and Configuration

MySQL's Multi-Range Read (MRR) optimization reduces random disk I/O by scanning indexes, sorting keys, and retrieving rows sequentially, offering advantages like sequential data access and batch key processing, while outlining its limitations, applicable scenarios, usage conditions, and configuration via optimizer_switch variables and buffer settings.

Disk I/OIndex OptimizationMRR
0 likes · 5 min read
MySQL Multi-Range Read (MRR) Optimization: Advantages, Limitations, Use Cases, and Configuration
Sanyou's Java Diary
Sanyou's Java Diary
Mar 27, 2025 · Databases

Why Your MySQL Indexes Fail: Cost Estimation, Fragmentation, and Slow Query Secrets

This article examines MySQL slow‑query troubleshooting, explaining how the optimizer estimates costs, why indexes may be ineffective even when present, the impact of memory fragmentation, pitfalls of prefix indexes, index merging techniques, and additional resource‑related factors that can cause seemingly healthy SQL statements to become slow.

Index OptimizationMemory Fragmentationcost estimation
0 likes · 11 min read
Why Your MySQL Indexes Fail: Cost Estimation, Fragmentation, and Slow Query Secrets
dbaplus Community
dbaplus Community
Mar 12, 2025 · Databases

Why Does LIMIT Pagination Slow Down? Deep Dive and Optimizations

This article reproduces a slow‑query scenario caused by deep pagination with LIMIT, explains the underlying index mechanics, and presents several practical SQL techniques—subquery, INNER JOIN, and anchor‑record methods—to dramatically reduce execution time.

Back-tableINNER JOINIndex Optimization
0 likes · 11 min read
Why Does LIMIT Pagination Slow Down? Deep Dive and Optimizations
Tencent Technical Engineering
Tencent Technical Engineering
Feb 21, 2025 · Databases

Understanding Vector Storage and Optimization in Elasticsearch 8.16.1

The article explains how Elasticsearch 8.16.1 stores dense and sparse vectors using various file extensions, compares flat and HNSW index formats, shows how disabling doc‑values removes redundant column‑store copies, and demonstrates scalar and binary quantization—including a quantization‑only mode—that can cut storage to roughly 9 percent while preserving search accuracy.

ElasticsearchHNSWIndex Optimization
0 likes · 32 min read
Understanding Vector Storage and Optimization in Elasticsearch 8.16.1
Alibaba Cloud Developer
Alibaba Cloud Developer
Jan 23, 2025 · Databases

Master MySQL Slow Query Analysis: Indexes, EXPLAIN, and Optimization Tips

This article explains how to analyze MySQL slow queries by examining response time, scanned rows, and returned rows, demonstrates EXPLAIN output with and without indexes, and covers index types, column ordering, left‑most prefix rules, and sorting strategies to improve database performance.

Database PerformanceIndex Optimizationexplain
0 likes · 12 min read
Master MySQL Slow Query Analysis: Indexes, EXPLAIN, and Optimization Tips
Aikesheng Open Source Community
Aikesheng Open Source Community
Dec 12, 2024 · Databases

Using EXPLAIN to Analyze and Optimize a Simple MySQL Query

This article demonstrates how to use MySQL's EXPLAIN statement to examine execution plans, interpret key fields such as possible_keys, key, rows, and Extra, and apply index adjustments—including adding and forcing indexes—to improve query performance while weighing sorting costs.

Database PerformanceIndex OptimizationSQL
0 likes · 11 min read
Using EXPLAIN to Analyze and Optimize a Simple MySQL Query
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
dbaplus Community
dbaplus Community
Aug 14, 2024 · Databases

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

Both DISTINCT and GROUP BY can use indexes when available, giving similar performance, but without indexes DISTINCT is usually faster because GROUP BY may invoke implicit sorting; MySQL 8.0 removed this sorting, making their performance nearly equal, and GROUP BY offers clearer semantics and more flexibility for complex queries.

DISTINCTGROUP BYIndex Optimization
0 likes · 9 min read
When to Use DISTINCT vs GROUP BY in MySQL: Performance Insights
Su San Talks Tech
Su San Talks Tech
Jul 29, 2024 · Databases

Why Is MySQL Query Slow? Hidden Factors and Proven Speed‑Up Tricks

This article explores why MySQL queries can become sluggish beyond just missing indexes, covering the full query execution flow, profiling tools, index pitfalls, connection limits, buffer pool sizing, and practical configuration tips to dramatically improve performance.

Connection PoolIndex OptimizationProfiling
0 likes · 15 min read
Why Is MySQL Query Slow? Hidden Factors and Proven Speed‑Up Tricks
Mike Chen's Internet Architecture
Mike Chen's Internet Architecture
Jul 5, 2024 · Backend Development

Nine High-Performance Optimization Techniques for Large-Scale Backend Architecture

This article presents nine comprehensive performance‑optimization strategies—including load balancing, sharding, read/write separation, caching, indexing, CDN, asynchronous processing, code refinement, and algorithm improvement—aimed at boosting the efficiency and scalability of large‑scale backend systems.

Index OptimizationPerformance Optimizationcaching
0 likes · 7 min read
Nine High-Performance Optimization Techniques for Large-Scale Backend Architecture
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
Jun 30, 2024 · Databases

How Implicit Type Conversion Can Kill PostgreSQL Query Performance by 10,000×

This article explains how implicit type conversions in PostgreSQL can cause index loss, drastically misestimate row counts, and force inefficient join strategies, illustrating the issue with concrete examples, detailed execution plans, and step‑by‑step optimizations that restore index usage and boost performance thousands of times.

Implicit ConversionIndex OptimizationPostgreSQL
0 likes · 13 min read
How Implicit Type Conversion Can Kill PostgreSQL Query Performance by 10,000×
JD Retail Technology
JD Retail Technology
May 8, 2024 · Databases

Understanding MySQL Cost Model for Index Optimization and Conflict Resolution

This article explains MySQL's cost‑based optimizer, demonstrates how to calculate query costs for full‑table, covering, ref and range scans using actual source‑code constants, and applies the model to resolve index‑conflict cases in a store‑goods table, offering practical optimization guidelines and future tool ideas.

Cost ModelDatabase PerformanceIndex Optimization
0 likes · 22 min read
Understanding MySQL Cost Model for Index Optimization and Conflict Resolution
Architect
Architect
Apr 29, 2024 · Databases

How to Slash MySQL Slow Queries on a 100M‑Row Table: Index Tuning and Batch Deletion

The article walks through a real‑world MySQL performance case where a 100‑million‑row table caused SLA alerts, analyzes slow‑query logs, demonstrates index redesign, compares online DDL with pt‑osc, and shows how batch deletions by primary key dramatically reduce delete time and replication lag.

Batch DeleteIndex OptimizationLarge Table
0 likes · 15 min read
How to Slash MySQL Slow Queries on a 100M‑Row Table: Index Tuning and Batch Deletion
Top Architect
Top Architect
Apr 25, 2024 · Databases

Comprehensive Guide to MySQL Index Optimization and EXPLAIN Plan Analysis

This article explains MySQL's architecture, how to view storage engines and variables, demonstrates creating various indexes, describes why SQL optimization is needed, details the EXPLAIN plan keywords and index types, and provides practical best‑practice tips for single‑ and multi‑table query performance improvements.

Database PerformanceIndex OptimizationQuery Tuning
0 likes · 35 min read
Comprehensive Guide to MySQL Index Optimization and EXPLAIN Plan Analysis
Alibaba Cloud Developer
Alibaba Cloud Developer
Apr 17, 2024 · Databases

Why MySQL’s TDDL Optimizer Picks the Wrong Index and How to Fix It

This article investigates a slow‑SQL incident caused by the TDDL optimizer reordering a MySQL query, analyzes why the optimizer chose an inefficient index, demonstrates how to verify and force the correct index, and proposes better index and partition‑key designs for optimal performance.

Index OptimizationPartitioningTDDL
0 likes · 11 min read
Why MySQL’s TDDL Optimizer Picks the Wrong Index and How to Fix It
Su San Talks Tech
Su San Talks Tech
Apr 15, 2024 · Databases

Mastering MySQL ORDER BY: Full-Field vs RowID Sorting and Optimizations

This article explains how MySQL executes ORDER BY queries, the role of sort_buffer, the difference between full-field and rowid sorting, how parameters like sort_buffer_size and max_length_for_sort_data affect performance, and how proper indexing can eliminate the need for sorting altogether.

Index OptimizationOrder ByRowid
0 likes · 12 min read
Mastering MySQL ORDER BY: Full-Field vs RowID Sorting and Optimizations
Top Architect
Top Architect
Apr 11, 2024 · Databases

MySQL Slow Query Analysis and Index Optimization for Large Tables

This article presents a detailed investigation of a MySQL master‑slave instance suffering from nightly SLA alerts due to slow‑query latency, analyzes the root causes with pt‑query‑digest and execution plans, proposes index redesign, demonstrates backup and restore with mydumper, compares online DDL and pt‑osc, and finally shows batch‑delete techniques to reduce load on large tables.

Database PerformanceIndex OptimizationLarge Tables
0 likes · 16 min read
MySQL Slow Query Analysis and Index Optimization for Large Tables
Mike Chen's Internet Architecture
Mike Chen's Internet Architecture
Apr 8, 2024 · Databases

9 Essential MySQL Index Optimization Techniques You Must Know

This article presents nine practical MySQL index optimization techniques—from selecting high‑selectivity columns and avoiding functions on indexed fields to applying the leftmost‑prefix rule, handling NULLs, using proper LIKE patterns, and leveraging EXPLAIN and LIMIT 1 for faster queries.

Database PerformanceIndex OptimizationQuery Tuning
0 likes · 6 min read
9 Essential MySQL Index Optimization Techniques You Must Know
JD Tech
JD Tech
Mar 29, 2024 · Databases

Root Cause Analysis and Optimization of a Slow MySQL Query Using Index Selection and Force Index

This article examines a MySQL slow‑query incident caused by the optimizer using the primary clustered index instead of an appropriate secondary index, explains the underlying index structures, and presents solutions such as FORCE INDEX and migrating complex queries to Elasticsearch for long‑term performance improvement.

ElasticsearchFORCE INDEXIndex Optimization
0 likes · 9 min read
Root Cause Analysis and Optimization of a Slow MySQL Query Using Index Selection and Force Index
Efficient Ops
Efficient Ops
Mar 10, 2024 · Databases

How Machine Learning Can Automate MySQL Index Optimization

This article explains how applying machine learning to database operations—specifically AIOps for MySQL—can automate index recommendation by parsing SQL, extracting semantic and statistical features, generating candidate index combinations, and training an XGBoost model to predict optimal indexes, reducing reliance on manual DBA work.

Index OptimizationSQLaiops
0 likes · 10 min read
How Machine Learning Can Automate MySQL Index Optimization
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
ITPUB
ITPUB
Dec 31, 2023 · Databases

Master MySQL Index Optimization with EXPLAIN: A Step‑by‑Step Guide

This article explains why slow queries occur in high‑traffic MySQL databases, introduces the EXPLAIN statement, walks through its syntax and output columns, demonstrates how to interpret each field with concrete examples, and provides a practical workflow for optimizing indexes to eliminate performance bottlenecks.

Database PerformanceIndex Optimizationexplain
0 likes · 14 min read
Master MySQL Index Optimization with EXPLAIN: A Step‑by‑Step Guide
Sohu Tech Products
Sohu Tech Products
Dec 27, 2023 · Operations

Why Does Elasticsearch Refresh Take 1‑5 Seconds? A Deep Dive into Index Settings and Soft Delete

This article records a systematic test of Elasticsearch refresh latency, revealing that update operations, a high proportion of deleted documents, and the soft‑delete setting significantly increase refresh time, while the large‑segment strategy and disabling soft delete can reduce latency without harming overall performance.

ElasticsearchIndex OptimizationPerformance Testing
0 likes · 7 min read
Why Does Elasticsearch Refresh Take 1‑5 Seconds? A Deep Dive into Index Settings and Soft Delete
php Courses
php Courses
Dec 2, 2023 · Databases

Index Optimization Techniques for PHP and MySQL Multi‑Table Queries

Effective index design, including single‑table, foreign‑key, composite, and covering indexes, along with optimizer hints, can dramatically improve the performance of multi‑table queries in PHP‑MySQL applications, and the article provides practical examples and SQL statements for each technique.

Database PerformanceIndex OptimizationPHP
0 likes · 4 min read
Index Optimization Techniques for PHP and MySQL Multi‑Table Queries
JD Tech
JD Tech
Nov 9, 2023 · Databases

Optimizing Pagination Queries for Billion‑Row MySQL Tables

This article analyzes the performance problems of LIMIT‑based pagination on massive MySQL tables and presents three progressively more efficient solutions—including a simple LIMIT approach, a tag‑record method using the last primary key, and a range‑limit method with cached min‑ID—along with best‑practice indexing recommendations to keep query latency in the tens of milliseconds even for tables containing billions of rows.

Index OptimizationLarge DataSQL
0 likes · 12 min read
Optimizing Pagination Queries for Billion‑Row MySQL Tables
Aikesheng Open Source Community
Aikesheng Open Source Community
Sep 19, 2023 · Databases

MySQL Deadlock Analysis and Index Optimization Using ICP

This article examines a MySQL deadlock case involving DELETE and UPDATE statements, explains why the primary key index is locked despite non‑key predicates, analyzes execution plans and optimizer trace, and proposes index restructuring and query hints to resolve the issue.

ICPIndex OptimizationQuery Planner
0 likes · 23 min read
MySQL Deadlock Analysis and Index Optimization Using ICP
dbaplus Community
dbaplus Community
Aug 10, 2023 · Databases

How sql_helper Automates Index Recommendations for MySQL & MariaDB

The article introduces sql_helper, an open-source command-line and web tool for MySQL 5.7/8.0 and MariaDB that automatically analyzes SQL statements to suggest index creation, explains its six-step workflow, provides usage examples, command-line parameters, and notes its assumptions and limitations.

Index OptimizationMariaDBSQL
0 likes · 5 min read
How sql_helper Automates Index Recommendations for MySQL & MariaDB
IT Services Circle
IT Services Circle
Jul 9, 2023 · Databases

20 Practical Rules for MySQL Index Optimization

This article presents twenty essential principles for optimizing MySQL indexes, covering topics such as the limitations of leading wildcard LIKE queries, the advantages of IN over UNION, handling negative conditions, composite index ordering, range queries, covering indexes, prefix indexes, and common pitfalls to avoid for better query performance.

Database PerformanceIndex OptimizationQuery Tuning
0 likes · 15 min read
20 Practical Rules for MySQL Index Optimization
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
Sanyou's Java Diary
Sanyou's Java Diary
Apr 27, 2023 · Databases

12 Common MySQL Slow‑Query Causes and How to Fix Them

This article examines the most frequent reasons MySQL queries become slow—including missing or ineffective indexes, deep pagination, massive tables, excessive joins, IN‑list overload, dirty pages, ORDER BY file‑sort, lock contention, and hardware limits—while offering concrete optimization techniques and best‑practice recommendations.

Index OptimizationSQLmysql
0 likes · 29 min read
12 Common MySQL Slow‑Query Causes and How to Fix Them
Java Architect Essentials
Java Architect Essentials
Apr 18, 2023 · Databases

How MySQL Calculates Execution Cost for Index Selection and Why It May Choose Suboptimal Plans

This article explains how MySQL estimates the I/O and CPU costs of different indexes when executing COUNT(*) and other queries, demonstrates the calculations with a concrete example on a 100k‑row table, and shows why the optimizer sometimes picks a full‑table scan over a seemingly better index.

Database PerformanceIndex OptimizationQuery Planner
0 likes · 10 min read
How MySQL Calculates Execution Cost for Index Selection and Why It May Choose Suboptimal Plans
Top Architect
Top Architect
Apr 2, 2023 · Databases

Optimizing Large-Scale Pagination Queries in MySQL: Data Generation and Index Strategies

This article demonstrates how to generate millions of test rows in MySQL, analyzes the performance impact of deep pagination using LIMIT, explains why non‑clustered index lookups cause costly table scans, and presents two optimization approaches—sub‑query ID filtering and key‑set pagination—to dramatically reduce query latency.

Data GenerationIndex OptimizationSQL
0 likes · 8 min read
Optimizing Large-Scale Pagination Queries in MySQL: Data Generation and Index Strategies
Selected Java Interview Questions
Selected Java Interview Questions
Mar 27, 2023 · Databases

Optimizing Deep Pagination in MySQL: Data Generation, Index Behavior, and Fast Query Strategies

This article demonstrates how to generate two million MySQL rows, examines the performance impact of large OFFSET values in LIMIT clauses, explains why non‑clustered indexes cause back‑table lookups, and presents two optimization strategies—using sub‑queries to fetch IDs and employing key‑based pagination—to dramatically speed up deep page queries.

Index OptimizationSQLdatabase
0 likes · 10 min read
Optimizing Deep Pagination in MySQL: Data Generation, Index Behavior, and Fast Query Strategies
ITPUB
ITPUB
Mar 12, 2023 · Databases

Master Real-World SQL Optimization: From Identifying Slow Queries to 20× Speed Boost

This article walks through a practical SQL optimization workflow, showing how to detect problematic queries via system metrics and execution plans, analyze table structures and indexes, apply concrete ALTER statements and index changes, and iteratively verify performance improvements, illustrated with a detailed MySQL case study that achieved a twenty‑fold speed increase.

Index OptimizationSQLexecution plan
0 likes · 10 min read
Master Real-World SQL Optimization: From Identifying Slow Queries to 20× Speed Boost
MaGe Linux Operations
MaGe Linux Operations
Mar 7, 2023 · Databases

How Indexes Can Slash MySQL Query Time by 30,000× – Real‑World Optimization

This article walks through a MySQL 5.6 scenario where a nested sub‑query to find students scoring 100 in Chinese took over 30,000 seconds, then demonstrates how adding single‑column and composite indexes, rewriting the query with EXISTS or JOIN, and analyzing execution plans dramatically reduces execution time to milliseconds while explaining index merge, covering indexes, left‑most prefix, and sorting optimizations.

Index OptimizationSQLquery-performance
0 likes · 17 min read
How Indexes Can Slash MySQL Query Time by 30,000× – Real‑World Optimization
Tencent Cloud Developer
Tencent Cloud Developer
Feb 20, 2023 · Mobile Development

iOS WeChat Full-Text Search Technology Upgrade: Selection and Optimization

iOS WeChat’s full‑text search was upgraded by selecting SQLite FTS5, creating a VerbatimTokenizer with multi‑level delimiter support, optimizing table formats to cut index size by 30 %, improving batch index updates and parallel search logic, resulting in 40‑60 % faster query latency.

Database OptimizationFull‑Text SearchIndex Optimization
0 likes · 26 min read
iOS WeChat Full-Text Search Technology Upgrade: Selection and Optimization
JD Cloud Developers
JD Cloud Developers
Feb 6, 2023 · Databases

Master MySQL Performance: Indexing, Execution Plans, and Real-World Cases

This article explains why database performance monitoring becomes critical as data grows, outlines common optimization techniques, dives into MySQL's storage architecture and index structures, and shares three real‑world cases that illustrate how proper indexing and execution‑plan analysis can prevent severe latency and CPU spikes.

Database PerformanceIndex OptimizationSQL Tuning
0 likes · 15 min read
Master MySQL Performance: Indexing, Execution Plans, and Real-World Cases
NiuNiu MaTe
NiuNiu MaTe
Dec 21, 2022 · Databases

Why Is MySQL OFFSET Slow? B+ Tree Index Insights & K‑th Largest Query Optimization

This article examines how MySQL uses secondary indexes to find the K‑th largest value, analyzes the O(N) time complexity caused by offset pagination, explains the underlying B+‑tree double‑linked list behavior, and presents three optimization strategies—including index covering, pagination redesign, and boundary prediction—to dramatically improve performance.

B+TreeIndex Optimizationmysql
0 likes · 10 min read
Why Is MySQL OFFSET Slow? B+ Tree Index Insights & K‑th Largest Query Optimization
Selected Java Interview Questions
Selected Java Interview Questions
Nov 12, 2022 · Databases

MySQL Large‑Table Index Optimization and Maintenance Strategies

This article analyzes performance problems of a massive MySQL table, demonstrates how to use pt‑query‑digest and EXPLAIN to identify inefficient composite indexes, proposes index redesign, compares online DDL with pt‑osc for index rebuilding, and presents batch‑delete techniques to reduce SLA‑impacting latency.

Batch DeleteDDLIndex Optimization
0 likes · 14 min read
MySQL Large‑Table Index Optimization and Maintenance Strategies
dbaplus Community
dbaplus Community
Oct 31, 2022 · Databases

Why MySQL Picks the Wrong Index for ORDER BY LIMIT Queries and How to Fix It

This article investigates a recurring MySQL CPU‑100% alarm caused by a slow SELECT with ORDER BY id ASC LIMIT, explains why the optimizer mistakenly chooses the primary‑key index over a suitable composite index, and presents six practical experiments—including force‑index, ORDER BY tweaks, and LIMIT adjustments—that reliably restore optimal index usage.

Cost ModelIndex OptimizationQuery Optimizer
0 likes · 14 min read
Why MySQL Picks the Wrong Index for ORDER BY LIMIT Queries and How to Fix It
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
Java High-Performance Architecture
Java High-Performance Architecture
Oct 5, 2022 · Databases

How to Optimize Massive MySQL Tables: Index Tuning, Online DDL, and Batch Deletion Strategies

This article analyzes a large MySQL table with frequent SLA alerts, demonstrates how to identify slow queries using pt‑query‑digest, proposes index redesign and online DDL versus pt‑osc methods, and shows testing, implementation, and batch‑delete techniques to dramatically reduce query time and maintenance overhead.

Batch DeletionDatabase MaintenanceIndex Optimization
0 likes · 17 min read
How to Optimize Massive MySQL Tables: Index Tuning, Online DDL, and Batch Deletion Strategies
21CTO
21CTO
Sep 28, 2022 · Databases

How to Optimize Large MySQL Tables: Index Tuning, Online DDL, and Batch Deletion

This article walks through diagnosing slow‑query problems on a massive MySQL table, reveals index misuse, proposes dropping the old composite index, adding targeted indexes, using online DDL or pt‑osc for schema changes, and applying batch‑delete techniques to reduce latency and storage fragmentation.

Batch DeletionIndex OptimizationLarge Tables
0 likes · 14 min read
How to Optimize Large MySQL Tables: Index Tuning, Online DDL, and Batch Deletion
Top Architect
Top Architect
Sep 28, 2022 · Databases

Optimizing Large MySQL Tables: Slow‑Query Analysis, Index Tuning, and Efficient Deletion

This article details a comprehensive performance investigation of a massive MySQL table, using pt‑query‑digest to analyze slow queries, exposing index inefficiencies, proposing index redesign, demonstrating backup/restore with mydumper, comparing online DDL and pt‑osc for index rebuilding, and recommending small‑batch deletions to reduce latency and maintenance overhead.

BackupDDLIndex Optimization
0 likes · 15 min read
Optimizing Large MySQL Tables: Slow‑Query Analysis, Index Tuning, and Efficient Deletion
Wukong Talks Architecture
Wukong Talks Architecture
Sep 27, 2022 · Databases

MySQL Large Table Index Optimization and Delete Performance Tuning

This article analyzes a MySQL large‑table performance issue caused by slow queries and full‑table scans, demonstrates using pt‑query‑digest, explains execution plans, proposes index redesign, shows backup and restore with mydumper, compares online DDL and pt‑osc, and presents batch delete strategies to reduce latency.

Batch DeleteDDLDatabase Performance
0 likes · 13 min read
MySQL Large Table Index Optimization and Delete Performance Tuning
Top Architect
Top Architect
Sep 17, 2022 · Databases

Optimizing Large MySQL Tables: Index Tuning, Slow Query Analysis, and Efficient Deletion Strategies

This article details a comprehensive analysis of a large MySQL table's performance issues, including slow query diagnostics with pt‑query‑digest, index evaluation, backup and restore procedures, online DDL versus pt‑osc, and practical strategies for index redesign and batch deletions to reduce latency and maintenance overhead.

DDLDatabase MaintenanceIndex Optimization
0 likes · 14 min read
Optimizing Large MySQL Tables: Index Tuning, Slow Query Analysis, and Efficient Deletion Strategies
Architecture Digest
Architecture Digest
Sep 15, 2022 · Databases

Case Study: Resolving Deep Pagination Slow Queries in MySQL

This article walks through a real‑world incident caused by deep pagination in a MySQL‑based system, detailing the timeline of symptoms, root‑cause analysis, and a series of optimizations—including ID‑based queries, range scans, and rolling cursor techniques—that ultimately restored stable performance.

Database PerformanceIndex OptimizationSQL
0 likes · 10 min read
Case Study: Resolving Deep Pagination Slow Queries in MySQL
ITFLY8 Architecture Home
ITFLY8 Architecture Home
Sep 12, 2022 · Databases

Why Auto‑Increment Fails in Distributed Databases and How to Design Scalable Indexes

This article explains why using auto‑increment primary keys in a distributed database leads to collisions and performance issues, and presents practical strategies such as ordered UUIDs, embedding shard keys, index tables, and global table replication to achieve efficient, scalable index design.

Index Optimizationdistributed databasesglobal UUID
0 likes · 10 min read
Why Auto‑Increment Fails in Distributed Databases and How to Design Scalable Indexes