Tagged articles

28 articles

Page 1 of 1

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

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

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 Backend Technology

Sep 10, 2025 · Databases

8 Proven MySQL Tricks to Supercharge Query Performance

This article walks through eight practical MySQL optimization techniques—including smarter LIMIT usage, avoiding implicit type conversion, rewriting UPDATE/DELETE with JOINs, handling mixed sorting, replacing EXISTS with joins, pushing predicates, narrowing result sets early, and leveraging WITH clauses—to dramatically reduce query execution time from seconds to milliseconds.

Query Planningdatabase indexingmysql

0 likes · 13 min read

8 Proven MySQL Tricks to Supercharge Query Performance

Baidu Geek Talk

Dec 16, 2024 · Artificial Intelligence

AIAPI: Baidu's AI-Native Retrieval System for Large Language Model Applications

AIAPI, Baidu’s AI‑native retrieval platform for large language models, tackles hallucination, slow domain updates, and output opacity by delivering authoritative, timely, full‑content data through a dual‑channel architecture that combines traditional search and RAG, employs reusable ranking, graph‑enhanced data layers, dynamic caching that cuts storage by 70 %, and QueryPlan‑based QoS, achieving markedly higher retrieval quality and a 34 % speed gain with Wenxin 4.0.

AI-Native SystemsAIAPIQuery Planning

0 likes · 12 min read

AIAPI: Baidu's AI-Native Retrieval System for Large Language Model Applications

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

ITPUB

Sep 26, 2023 · Databases

Why PostgreSQL Shows InitPlan and One‑Time Filter in Self‑Join Queries

This article walks through a puzzling PostgreSQL self‑join query, shows how different alias placements change the execution plan, explains the meaning of InitPlan and One‑Time Filter nodes, and demonstrates their impact with concrete examples and EXPLAIN output.

InitPlanOne-Time FilterPostgreSQL

0 likes · 11 min read

Why PostgreSQL Shows InitPlan and One‑Time Filter in Self‑Join Queries

StarRocks

Nov 2, 2022 · Databases

Mastering Join Optimization in StarRocks: Techniques, Algorithms, and Distributed Planning

This article provides a comprehensive, step‑by‑step guide to StarRocks join optimization, covering join types, logical rewrite rules, predicate push‑down, join reorder algorithms, cost modeling, distributed join strategies, and runtime filters, while offering practical tips for achieving high‑performance query execution.

Cost ModelDistributed SQLJOIN optimization

0 likes · 26 min read

Mastering Join Optimization in StarRocks: Techniques, Algorithms, and Distributed Planning

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

dbaplus Community

Aug 7, 2022 · Databases

Mastering MySQL Performance: Practical Strategies for Slow‑SQL Optimization

This comprehensive guide examines why slow SQL queries degrade system performance, outlines key factors such as data volume, access patterns, and processing methods, and provides concrete optimization techniques, case studies, index design principles, and advanced features like MRR and index push‑down for MySQL‑InnoDB.

InnoDBQuery PlanningSQL Optimization

0 likes · 14 min read

Mastering MySQL Performance: Practical Strategies for Slow‑SQL Optimization

DataFunSummit

Oct 17, 2021 · Databases

Databend: Cloud‑Native Modern Data Warehouse Architecture and Features

This article explains how Databend, a cloud‑native data warehouse, addresses modern OLAP requirements through storage‑compute separation, elastic scaling, multi‑cloud support, advanced query planning, and serverless‑ready design, contrasting it with traditional data warehouse limitations.

DatabendQuery Planningcloud-native

0 likes · 11 min read

Databend: Cloud‑Native Modern Data Warehouse Architecture and Features

Liangxu Linux

Aug 25, 2021 · Databases

Why MySQL ORDER BY + LIMIT Returns Unexpected Order and How to Ensure Consistency

Using MySQL's ORDER BY with LIMIT can yield an unexpected row order because duplicate values in the sorting column cause nondeterministic results; adding a unique secondary column like id stabilizes the order, and the official documentation explains how LIMIT influences query planning and optimization.

Database OptimizationLIMITOrder By

0 likes · 6 min read

Why MySQL ORDER BY + LIMIT Returns Unexpected Order and How to Ensure Consistency

Alibaba Cloud Developer

Jul 26, 2021 · Databases

Inside MySQL 8.0: Deep Dive into Parser, Optimizer, and Execution Engine

This article analyzes MySQL 8.0.25 source code, detailing the server‑layer architecture, parser reconstruction, query‑rewrite phases, transformation rules, and both the legacy and hypergraph optimizers, while also comparing key stages with PostgreSQL.

Database ArchitectureParserQuery Planning

0 likes · 13 min read

Inside MySQL 8.0: Deep Dive into Parser, Optimizer, and Execution Engine

Code Ape Tech Column

May 8, 2021 · Databases

Understanding MySQL Pagination Performance and Index Optimization

The article explores why MySQL pagination with large offsets is slow, explains index structures such as clustered and secondary indexes, describes logical query operators, and presents two practical optimization strategies to improve query performance.

Database PerformanceIndex OptimizationQuery Planning

0 likes · 6 min read

Understanding MySQL Pagination Performance and Index Optimization

Xianyu Technology

Mar 18, 2021 · Backend Development

Multi-Engine Concurrent Search Architecture for Idlefish

Idlefish’s new multi‑engine concurrent search architecture replaces the tightly‑coupled single‑engine pipeline with deep engine isolation, asynchronous multi‑engine recall, and unified result merging, cutting dump build time from 14 h to 5 h, shrinking memory use dramatically, improving latency by only ~15 ms, and boosting exposure by 50 % and orders by 33 %.

Big DataLuaQuery Planning

0 likes · 10 min read

Multi-Engine Concurrent Search Architecture for Idlefish

Aikesheng Open Source Community

Feb 24, 2021 · Databases

Understanding Composite (Multi‑Column) Indexes in MySQL

This article explains how MySQL composite indexes work, the syntax for creating them, the conditions required for their use, and how different query patterns affect index utilization, illustrated with multiple SQL examples and execution‑plan analyses.

Composite IndexQuery Planningdatabase indexing

0 likes · 14 min read

Understanding Composite (Multi‑Column) Indexes in MySQL

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

ITPUB

Nov 30, 2020 · Databases

How to Fix Common MySQL Performance Pitfalls: Real-World SQL Optimizations

This article examines frequent MySQL performance problems such as inefficient LIMIT usage, implicit type conversion, subquery updates, mixed ordering, EXISTS clauses, predicate push‑down, early row reduction, and intermediate result set handling, providing concrete rewrites and execution‑plan comparisons that dramatically improve query speed.

Query PlanningSQL OptimizationSubqueries

0 likes · 14 min read

How to Fix Common MySQL Performance Pitfalls: Real-World SQL Optimizations

ITPUB

Sep 30, 2020 · Databases

Mastering MySQL Index Usage: When and How Queries Leverage Indexes

This guide explains the conditions under which MySQL can use indexes for range scans, LIKE patterns, BETWEEN clauses, and sorting, detailing key_len calculation, index key vs. filter vs. table filter, and practical examples to demystify index utilization.

Database PerformanceIndex OptimizationQuery Planning

0 likes · 10 min read

Mastering MySQL Index Usage: When and How Queries Leverage Indexes

ITPUB

Feb 22, 2020 · Databases

12 Practical SQL Optimization Tips to Speed Up Your Queries

This article presents a concise collection of twelve actionable SQL tuning techniques—including using proper comparison operators, LIMIT 1, appropriate data types, breaking large statements, UNION ALL, consistent query formatting, avoiding SELECT *, indexing WHERE/JOIN/ORDER BY columns, pagination with LIMIT, and leveraging EXPLAIN—to help developers improve database performance.

Database OptimizationQuery PlanningSQL

0 likes · 5 min read

12 Practical SQL Optimization Tips to Speed Up Your Queries

Aikesheng Open Source Community

Dec 9, 2019 · Databases

Cross‑Database JOIN and Distributed Query Planning in DBLE Middleware

This tutorial explains how DBLE splits tables using different rules, demonstrates cross‑database INNER, LEFT, and RIGHT JOINs with jump‑hash sharding, and details the distributed query plan and sorting optimization that reduces join complexity in a middleware environment.

DBLEQuery PlanningSQL

0 likes · 7 min read

Cross‑Database JOIN and Distributed Query Planning in DBLE Middleware

Efficient Ops

Aug 4, 2019 · Databases

Master MySQL Indexes: When and How They’re Used for Queries, LIKE, and Sorting

This article explains how MySQL decides whether an index can be used for range queries, LIKE patterns, and ORDER BY operations, detailing key length calculation, index key vs. filter vs. table filter, and practical examples for BETWEEN and sorting scenarios.

Query PlanningSQLmysql

0 likes · 11 min read

Master MySQL Indexes: When and How They’re Used for Queries, LIKE, and Sorting

21CTO

Jun 5, 2019 · Databases

12 Proven SQL Tricks to Supercharge Your Database Performance

This article presents twelve practical SQL optimization techniques—from using proper comparison operators and LIMIT clauses to indexing strategies and query consistency—to dramatically improve database query speed and overall application performance.

Database OptimizationQuery PlanningSQL

0 likes · 5 min read

12 Proven SQL Tricks to Supercharge Your Database Performance

DataFunTalk

Apr 22, 2019 · Databases

Deep Dive into TiDB SQL Optimizer and Execution Engine

This article provides a comprehensive technical overview of TiDB's architecture, its SQL optimizer—including logical and physical optimization phases, rule‑based transformations, join ordering, and statistics collection—and outlines future work to improve query planning stability and performance.

Database InternalsQuery PlanningSQL Optimizer

0 likes · 16 min read

Deep Dive into TiDB SQL Optimizer and Execution Engine

Efficient Ops

Nov 28, 2018 · Databases

Master MySQL Indexes: When and How They’re Used for Queries

This article explains how MySQL decides whether an index can be used for range queries, LIKE patterns, and sorting, detailing key_len calculation, index key vs. filter vs. table filter concepts, and practical examples with BETWEEN and ORDER BY.

Database PerformanceIndex OptimizationQuery Planning

0 likes · 11 min read

Master MySQL Indexes: When and How They’re Used for Queries

ITPUB

Jan 9, 2018 · Databases

Top 17 Oracle SQL Tuning Techniques to Boost Query Performance

This guide presents seventeen practical Oracle SQL optimization tips—including table order selection, WHERE clause ordering, avoiding SELECT *, reducing round‑trips, using DECODE, consolidating queries, eliminating duplicates, preferring TRUNCATE, leveraging COMMIT, replacing HAVING with WHERE, minimizing sub‑queries, employing internal functions, using aliases, favoring EXISTS over IN, detecting inefficient SQL, and proper index usage—to help developers write faster, more efficient database queries.

Database IndexesOraclePerformance Optimization

0 likes · 11 min read

Top 17 Oracle SQL Tuning Techniques to Boost Query Performance

dbaplus Community

Sep 19, 2017 · Databases

Why PostgreSQL Misestimates Rows When Types Are Cast – and How to Fix It

A recent production issue showed that PostgreSQL dramatically over‑estimated row counts because filter predicates performed implicit type casts, leading to sequential scans and 18‑second queries, but correcting column types or rewriting the predicates restored accurate estimates and reduced execution time to under a second.

Index ScanPostgreSQLQuery Planning

0 likes · 8 min read

Why PostgreSQL Misestimates Rows When Types Are Cast – and How to Fix It

360 Quality & Efficiency

May 17, 2016 · Databases

Understanding Database Indexes: Benefits, Usage Principles, and Analysis with EXPLAIN

This article explains the advantages and disadvantages of database indexes, outlines practical principles for their use, demonstrates how to analyze index effectiveness with MySQL's EXPLAIN command, and lists common situations that cause index loss, providing clear guidance for performance optimization.

Database PerformanceQuery PlanningSQL Optimization

0 likes · 9 min read

Understanding Database Indexes: Benefits, Usage Principles, and Analysis with EXPLAIN