Skip to content
Tagged articles

Query Optimization

462 articles · Page 1 of 5
Mingyi World Elasticsearch
Mingyi World Elasticsearch
Nov 12, 2026 · Databases

Green Cluster ≠ Fast Cluster: A Production Troubleshooting & Tuning Framework for Easysearch

This article explains why an Easysearch cluster's green health status doesn't guarantee performance, and provides a comprehensive framework for production troubleshooting covering shard sizing, JVM tuning, thread pools, query optimization, monitoring dimensions, and a step-by-step diagnostic workflow.

EasysearchElasticsearchJVM tuning
0 likes · 17 min read
Green Cluster ≠ Fast Cluster: A Production Troubleshooting & Tuning Framework for Easysearch
IT Services Circle
IT Services Circle
Nov 3, 2026 · Databases

MySQL Top 10 from 50M Rows: SQL Pitfalls, Pre-aggregation & Space-Saving Algorithm

This article walks through a big-tech interview question about finding top 10 hot items from 50M MySQL rows, revealing why naive SQL fails, how to clarify time windows and real-time needs, and explores solutions from pre-aggregated snapshots and partitioned parallel queries to the Space-Saving algorithm for approximate top-K with bounded memory.

Approximate AlgorithmsDatabase InternalsQuery Optimization
0 likes · 14 min read
MySQL Top 10 from 50M Rows: SQL Pitfalls, Pre-aggregation & Space-Saving Algorithm
Java Tech Workshop
Java Tech Workshop
Oct 29, 2026 · Databases

MySQL Deep Pagination: Why OFFSET 99980 Takes 4.68s and How to Cut It to 6ms

This article analyzes why MySQL LIMIT with large offsets causes severe performance degradation, explaining the server-layer mechanics of OFFSET, comparing index-scan vs full-table-scan execution paths, and demonstrating two optimization techniques — delayed join (covering index subquery) and cursor-based pagination — with benchmark results showing 4.68s reduced to 0.21s and 0.006s respectively on a 1.28M-row table.

Covering IndexCursor PaginationDeep Pagination
0 likes · 17 min read
MySQL Deep Pagination: Why OFFSET 99980 Takes 4.68s and How to Cut It to 6ms
Feidie Network
Feidie Network
Oct 19, 2026 · Backend Development

EF Core Deep Dive: Change Tracking, Query Optimization, Concurrency & Migration Strategies

This comprehensive guide explores EF Core's ChangeTracker internals, performance optimization techniques including AsNoTracking and compiled queries, optimistic concurrency with Timestamp tokens, production migration strategies using SQL scripts and bundles, and previews EF Core 10 LTS features like vector search and JSON support.

ChangeTrackerDatabase MigrationEF Core 10
0 likes · 19 min read
EF Core Deep Dive: Change Tracking, Query Optimization, Concurrency & Migration Strategies
Cilantro Loves Linux
Cilantro Loves Linux
Oct 18, 2026 · Databases

Why Adding Indexes Fails at Scale: Relational Big Data Architecture Patterns

This chapter from 'Architecture Fundamentals' details relational big data architecture patterns: sharding strategies, query optimization avoiding unbounded scans, async import/export with streaming I/O, idempotency, and a bottleneck-first evolution checklist that prioritizes product-level constraints over premature scaling.

Query Optimizationasync processingbig data architecture
0 likes · 9 min read
Why Adding Indexes Fails at Scale: Relational Big Data Architecture Patterns
Lakehouse Research Base
Lakehouse Research Base
Oct 15, 2026 · Databases

StarRocks Integrates Paimon C++ for Native Lake Query Execution

StarRocks integrates Paimon C++ (paimon-cpp) to enable native C++ execution paths for querying Apache Paimon tables, eliminating JNI overhead, supporting primary key merges and delete handling, and achieving 1.4-3.4x speedups in benchmarks while reusing StarRocks' caching, memory management, and query profiling.

JNINative ExecutionPaimon
0 likes · 14 min read
StarRocks Integrates Paimon C++ for Native Lake Query Execution
StarRocks
StarRocks
Oct 14, 2026 · Databases

StarRocks Integrates Paimon C++ for 1.4–3.4x Faster Lake Queries via Native Execution

StarRocks adopts Apache Paimon's C++ implementation (paimon-cpp) to replace JNI calls for primary-key merges and delete-vector handling, cutting cross-language overhead and reusing StarRocks' cache, memory management, and Query Profile; benchmarks show 1.4–3.4× speedups on primary-key counts and wide-table aggregations.

Apache PaimonJNINative Execution
0 likes · 14 min read
StarRocks Integrates Paimon C++ for 1.4–3.4x Faster Lake Queries via Native Execution
Lakehouse Research Base
Lakehouse Research Base
Oct 1, 2026 · Databases

StarRocks Vectorized Execution & CBO Optimizer Deep Dive: Pipeline, CodeGen & Cost Model

This article provides a deep technical analysis of StarRocks' vectorized execution engine and cost-based optimizer, covering pipeline scheduling, vectorized operators, CodeGen/JIT compilation, statistics-driven cost modeling, parameter tuning strategies, and production troubleshooting methodologies for low-latency analytical queries.

CBO OptimizerCodeGenOLAP Database
0 likes · 40 min read
StarRocks Vectorized Execution & CBO Optimizer Deep Dive: Pipeline, CodeGen & Cost Model
Rain's Path to Java Mastery
Rain's Path to Java Mastery
Sep 21, 2026 · Databases

Elasticsearch Core Internals: Inverted Index, Cluster, Write/Query, IK, Deep Pagination & Optimization

A comprehensive technical guide covering Elasticsearch inverted index internals (FST, skip lists), cluster architecture with node roles and shard/replica design, write path (refresh, translog, flush), query execution (Query Then Fetch), IK Chinese analyzer with hot reload, deep pagination solutions (search_after vs scroll), and production index optimization across design, write, query, and operations dimensions.

Cluster ArchitectureDeep PaginationElasticsearch
0 likes · 36 min read
Elasticsearch Core Internals: Inverted Index, Cluster, Write/Query, IK, Deep Pagination & Optimization
Cloud Architecture
Cloud Architecture
Sep 18, 2026 · Databases

From Ad-Hoc Indexing to Verifiable Governance: MySQL Index Governance at Scale

This article details a production-grade MySQL index governance system for high-concurrency e-commerce, covering evidence collection, invisible index validation, automated change gates, regression detection, and a case study reducing order-list P99 from 2.3s to 170ms while preventing unsafe deletions.

Query Optimizatione-commercehigh concurrency
0 likes · 38 min read
From Ad-Hoc Indexing to Verifiable Governance: MySQL Index Governance at Scale
Cloud Architecture
Cloud Architecture
Sep 17, 2026 · Databases

Indexes Aren't Free: Production Index Governance for High-Write Order Systems

This article presents a comprehensive index governance methodology for high-write MySQL order systems, demonstrating through a real incident how a read-optimized index caused write latency, replica lag, and timeouts, and detailing a reusable process covering query-driven design, cost measurement, validation, change buffer limits, index convergence, safe deletion, read-write routing, sharding, transactional outbox, idempotent consumers, online DDL safeguards, gating, and long-term ownership.

Change BufferDescending IndexesHigh-Write Systems
0 likes · 29 min read
Indexes Aren't Free: Production Index Governance for High-Write Order Systems
StarRocks
StarRocks
Sep 15, 2026 · Databases

StarRocks AI Function: Embedding LLM Inference into the SQL Execution Pipeline

StarRocks introduces AI Functions that let SQL directly call LLMs for generation, classification, extraction, and embedding, integrating model inference into the query optimizer and pipeline execution engine with async dual-pipeline architecture, backpressure, and cost-based optimization to reduce unnecessary calls and manage latency.

AI FunctionLLM inferenceQuery Optimization
0 likes · 25 min read
StarRocks AI Function: Embedding LLM Inference into the SQL Execution Pipeline
Lakehouse Research Base
Lakehouse Research Base
Sep 6, 2026 · Big Data

StarRocks Query Acceleration on Paimon: Production Tuning & Best Practices

This article details production practices for accelerating StarRocks queries on Paimon external tables, covering architecture, table design (partitioning, bucketing, compaction), dirty data handling, catalog configuration, predicate pushdown verification, SQL optimization with partition/bucket pruning, query method selection (direct, async materialized views, hot/cold tiering), and BE-level tuning.

Bucket PruningMaterialized ViewPaimon
0 likes · 22 min read
StarRocks Query Acceleration on Paimon: Production Tuning & Best Practices
liandk
liandk
Aug 24, 2026 · Databases

One‑Line Queries: Using Database Views to Encapsulate Complex SQL

This article explains how database views act as virtual tables that store only query logic, allowing developers to replace repetitive, multi‑join SQL statements with a single, reusable query while improving consistency, hiding sensitive fields, and boosting development efficiency.

Data ModelingDatabase ViewsQuery Optimization
0 likes · 4 min read
One‑Line Queries: Using Database Views to Encapsulate Complex SQL
Tencent Technical Engineering
Tencent Technical Engineering
Aug 20, 2026 · Big Data

Tencent SuperSQL Sets New TPC‑DS World Record, Leads in Performance and Cost Efficiency

Tencent's SuperSQL achieved a 654 million composite score on the TPC‑DS 100 TB benchmark—nearly ten times the previous record—while cutting performance‑per‑dollar cost to 11.04 CNY (about one‑sixth of earlier results), thanks to innovations in query optimization, a vectorized execution engine, adaptive memory and shuffle mechanisms, and AI‑driven diagnostics that together deliver superior speed, scalability, and business value.

AI diagnosticsQuery OptimizationSuperSQL
0 likes · 18 min read
Tencent SuperSQL Sets New TPC‑DS World Record, Leads in Performance and Cost Efficiency
Senior Tony
Senior Tony
Aug 15, 2026 · Artificial Intelligence

How We Boosted RAG Recall by 15% with Practical Query Optimization Techniques

The article details how a 15% recall@K improvement was achieved in a RAG system by building an offline test set and applying six concrete query‑optimization methods—including rewrite, HyDE, multi‑query, query splitting, contextual completion, and keyword enhancement—while discussing their trade‑offs and implementation tips.

HyDEKeyword EnhancementMulti-Query
0 likes · 9 min read
How We Boosted RAG Recall by 15% with Practical Query Optimization Techniques

MySQL Large Table Optimization: 5 Strategies for 100M+ Row Queries

This article presents a five-step practical guide for optimizing MySQL queries on tables exceeding 100 million rows, covering index optimization, SQL rewriting, cursor-based pagination, table structure improvements including partitioning, and architectural upgrades like read-write separation and sharding, with concrete code examples and performance comparisons.

Large TablesPaginationPartitioning
0 likes · 12 min read
MySQL Large Table Optimization: 5 Strategies for 100M+ Row Queries
samdeepthink
samdeepthink
Aug 4, 2026 · Databases

Choosing the Best Composite Index for A = ?, B IN (...), ORDER BY C

The article explains why placing column C immediately after the equality column A in a composite index (A, C, B) avoids filesort and keeps performance stable regardless of how many values appear in the B IN list, outperforming other index orders such as (A, B, C).

Composite IndexFilesortIN Clause
0 likes · 10 min read
Choosing the Best Composite Index for A = ?, B IN (...), ORDER BY C
Linyb Geek Road
Linyb Geek Road
Jul 27, 2026 · Artificial Intelligence

Why RAG Misses Casual User Questions and How to Optimize Retrieval

Real users ask informal, incomplete questions that often miss the right documents, so the article classifies common failure types, explains three query‑optimization techniques—Query Rewrite, Multi‑Query, and HyDE—provides concrete prompts, code snippets, selection guidelines, evaluation metrics, and practical deployment pitfalls.

HyDELLM RetrievalMulti-Query
0 likes · 14 min read
Why RAG Misses Casual User Questions and How to Optimize Retrieval
Linyb Geek Road
Linyb Geek Road
Jul 17, 2026 · Databases

8 Common SQL Mistakes That Are Destroying Your Database

The article identifies eight easy-to-overlook SQL habits—such as using SELECT *, applying functions on indexed columns, careless LIKE patterns, improper IN/OR logic, deep offset pagination, unconditional UPDATE/DELETE, excessive indexing, and ignoring execution plans—that can cause slow queries, lock tables, index failures, and even data loss, and provides concrete examples and safer alternatives.

IndexingQuery OptimizationSQL
0 likes · 12 min read
8 Common SQL Mistakes That Are Destroying Your Database
Linyb Geek Road
Linyb Geek Road
Jul 17, 2026 · Databases

Why 90% of Slow SQL Queries Aren’t Caused by Large Data Volumes

Most slow SQL statements stem from poor query writing—such as index‑breaking functions, implicit type casts, leading wildcards, unnecessary column selection, improper joins, sorting, grouping, and pagination—rather than merely the size of the data, and the article shows how to diagnose and fix each issue.

IndexingQuery OptimizationSQL
0 likes · 11 min read
Why 90% of Slow SQL Queries Aren’t Caused by Large Data Volumes
dbaplus Community
dbaplus Community
Jul 12, 2026 · Databases

Stop Blaming Data Size: 90% of Slow SQLs Are Due to Poor Queries

This article reveals that most slow SQL queries aren't caused by large data volumes but by poor query writing, such as index‑killing functions, implicit type casts, leading wildcards, SELECT *, missing LIMIT, and inefficient JOINs, and offers a five‑step method to diagnose and fix them.

IndexingQuery OptimizationSQL
0 likes · 11 min read
Stop Blaming Data Size: 90% of Slow SQLs Are Due to Poor Queries
samdeepthink
samdeepthink
Jul 3, 2026 · Databases

MySQL Index Interview Guide: From B+ Trees to Index Design

This article explains MySQL index fundamentals—from the B+‑tree storage engine and InnoDB’s clustered and secondary indexes to query execution, common index‑failure scenarios, and practical design principles for building effective indexes in interview settings.

B+ TreeEXPLAINIndex
0 likes · 25 min read
MySQL Index Interview Guide: From B+ Trees to Index Design
IoT Full-Stack Technology
IoT Full-Stack Technology
Jun 26, 2026 · Databases

Are You Still Using These 8 Inefficient SQL Patterns?

This article examines eight common SQL pitfalls—including misuse of LIMIT offsets, implicit type conversion, sub‑query updates, mixed ordering, EXISTS clauses, condition push‑down, early limiting, and intermediate result set handling—showing how each can degrade performance and providing rewritten queries with execution‑plan evidence that dramatically improve speed.

Query OptimizationSQLexecution plan
0 likes · 12 min read
Are You Still Using These 8 Inefficient SQL Patterns?
samdeepthink
samdeepthink
Jun 25, 2026 · Databases

How Much SQL Do You Really Need to Master for Real‑World Development?

The author argues that in most companies you only need to write simple, single‑table or limited‑join queries and focus on index tuning and basic MySQL concepts such as InnoDB, locks, and execution flow, while complex SQL work often signals a poorly organized team.

Database PerformanceQuery OptimizationSQL
0 likes · 3 min read
How Much SQL Do You Really Need to Master for Real‑World Development?
Xiaolin Talks Programming
Xiaolin Talks Programming
Jun 25, 2026 · Backend Development

Building a Production-Grade Search Engine with Spring Boot & Elasticsearch

This article details how to replace MySQL complex queries with Elasticsearch in production, covering data sync via Canal/Kafka, mapping design with IK analyzer, Spring Data ES query builders, deep pagination with Search After, and consistency guarantees through reconciliation and dead-letter queues.

Consistency PatternsDeep PaginationElasticsearch
0 likes · 21 min read
Building a Production-Grade Search Engine with Spring Boot & Elasticsearch
dbaplus Community
dbaplus Community
Jun 21, 2026 · Databases

Why the 20‑Year‑Old N+1 Query Problem Doesn’t Apply to SQLite

The article explains that the classic N+1 query anti‑pattern, harmful on client‑server databases like MySQL, is irrelevant for SQLite because its embedded architecture eliminates network round‑trips, turning hundreds of queries into cheap function calls, and examines the performance data and trade‑offs behind this claim.

Database PerformanceFossilN+1 Query
0 likes · 29 min read
Why the 20‑Year‑Old N+1 Query Problem Doesn’t Apply to SQLite
StarRocks
StarRocks
Jun 4, 2026 · Databases

How StarRocks and Iceberg Enable Federated Queries: A Practical Walkthrough

This article details Fresha's real‑world integration of StarRocks with Apache Iceberg, covering metadata planning, distributed execution, adaptive metadata retrieval, hot‑cold data layering, missing statistics handling, catalog configuration, and performance optimizations that together demonstrate how federated queries can be efficiently executed over data‑lake tables.

Apache IcebergHot/Cold Data LayeringMetadata Cache
0 likes · 14 min read
How StarRocks and Iceberg Enable Federated Queries: A Practical Walkthrough
Programmer XiaoFu
Programmer XiaoFu
Jun 1, 2026 · Databases

Why Does an OR Between Two Indexed Columns Still Trigger a Full Table Scan?

Even though the phone and email columns each have a single‑column index, an OR condition forces MySQL's cost‑based optimizer to choose a full table scan because the estimated cost of index merge (random I/O and possible sort‑union) exceeds the cost of a sequential scan, and the article explains the underlying mechanics and practical workarounds.

OR queryQuery OptimizationSQL
0 likes · 10 min read
Why Does an OR Between Two Indexed Columns Still Trigger a Full Table Scan?
Lakehouse Research Base
Lakehouse Research Base
May 27, 2026 · Databases

Paimon Bloom Index Deep Dive: 25x Query Speedup from 38s to 1.5s with Production Best Practices

This article analyzes Paimon Bloom Index internals, demonstrates a real-world 25x query acceleration (38s to 1.5s) on a 400GB partitioned table, explains false positive rates, optimal use cases, and provides production-ready configuration patterns for StarRocks+Paimon lakehouse architectures.

Bloom IndexData EngineeringFalse Positive Rate
0 likes · 19 min read
Paimon Bloom Index Deep Dive: 25x Query Speedup from 38s to 1.5s with Production Best Practices
Smart Sea Tide
Smart Sea Tide
May 22, 2026 · Databases

SQL Query Optimization: Cutting a 9M‑Row Scan from 17 s to 300 ms

The article analyzes why a MySQL LIMIT OFFSET query on a 9.5 million‑row table takes 16 seconds, demonstrates how moving the filter into a sub‑query that returns only primary‑key IDs and joining back reduces execution to 0.35 seconds, and validates the theory by measuring InnoDB buffer‑pool page accesses.

Buffer PoolInnoDBLIMIT OFFSET
0 likes · 9 min read
SQL Query Optimization: Cutting a 9M‑Row Scan from 17 s to 300 ms
Architect's Guide
Architect's Guide
May 20, 2026 · Databases

30 Essential SQL Query Optimization Techniques

This article presents thirty practical SQL optimization tips, covering index usage, avoiding full‑table scans caused by operators like !=, NULL checks, OR, LIKE, IN, functions, and expressions, as well as best practices for temporary tables, cursors, and transaction size to improve database performance.

Performance TuningQuery OptimizationSQL
0 likes · 10 min read
30 Essential SQL Query Optimization Techniques
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.

EXPLAINQuery OptimizationSQL performance
0 likes · 32 min read
10 Common MySQL Index Failure Scenarios – Which Ones Affect You?
dbaplus Community
dbaplus Community
May 17, 2026 · Databases

Is Raising work_mem from 4 MB to 64 MB Really Optimizing Sorts? The 2 TB PostgreSQL OOM Time Bomb

The article explains why increasing PostgreSQL's work_mem does not guarantee per‑query memory limits, how multiple sort/hash nodes, parallel workers and long‑lived memory contexts can cause OOM even on a 2 TB server, and offers concrete diagnostics and mitigation strategies for DBAs and developers.

ConcurrencyMemory ManagementOOM
0 likes · 12 min read
Is Raising work_mem from 4 MB to 64 MB Really Optimizing Sorts? The 2 TB PostgreSQL OOM Time Bomb
Architect's Guide
Architect's Guide
May 14, 2026 · Databases

8 SQL Pitfalls That Can Slow Your Queries 100‑Fold – How to Avoid Them

The article enumerates eight common MySQL query patterns—such as large‑offset LIMIT, implicit type conversion, sub‑query updates, mixed ordering, unnecessary EXISTS, poor condition push‑down, early range reduction, and inefficient intermediate result handling—and shows rewritten SQL that reduces execution time from seconds to milliseconds.

Query OptimizationSQLindexes
0 likes · 15 min read
8 SQL Pitfalls That Can Slow Your Queries 100‑Fold – How to Avoid Them
Wukong Talks Architecture
Wukong Talks Architecture
May 14, 2026 · Databases

How to Cut a Production MySQL 8.0 LATERAL Query from 3 s to 0.8 s

The article walks through diagnosing a slow MySQL 8.0 query that scans 77,724 rows and performs a costly filesort, then rewrites it with a LATERAL join and a new index, reducing execution time to about 0.75 seconds on 30 k rows while explaining the differences between ordinary subqueries and LATERAL, and highlighting the need for deterministic ordering when timestamps duplicate.

IndexLATERALQuery Optimization
0 likes · 8 min read
How to Cut a Production MySQL 8.0 LATERAL Query from 3 s to 0.8 s
Cloud Architecture
Cloud Architecture
May 11, 2026 · Operations

3 Billion Products, 500 Million Daily Queries: How I Boosted a Failing Elasticsearch Cluster 5×

A large‑scale e‑commerce search system handling 3 billion product documents and 500 million daily queries was on the brink of collapse, but a systematic overhaul across query modeling, index design, write pipelines, cluster topology, and application‑level governance lifted throughput five‑fold while cutting P99 latency from 3.2 seconds to 180 ms.

ElasticsearchJavaQuery Optimization
0 likes · 30 min read
3 Billion Products, 500 Million Daily Queries: How I Boosted a Failing Elasticsearch Cluster 5×
Architect's Guide
Architect's Guide
Apr 23, 2026 · Databases

Is JOIN Faster Than IN in MySQL Queries? A Practical Performance Test

The article experimentally compares MySQL JOIN and IN queries across small, medium, and large data sets, showing that JOIN is generally faster for modest volumes while IN can become impractically long and error‑prone with massive lists, leaving neither approach ideal for all scenarios.

PHPQuery Optimizationin
0 likes · 8 min read
Is JOIN Faster Than IN in MySQL Queries? A Practical Performance Test
Cloud Architecture
Cloud Architecture
Apr 16, 2026 · Databases

EXPLAIN ANALYZE: The CT Scan That Triggers a Query Performance Revolution

When a core order‑query suddenly slowed from milliseconds to seconds, the team discovered that using PostgreSQL’s EXPLAIN ANALYZE revealed hidden plan regressions, outdated statistics, and index misuse, leading to a systematic, production‑grade methodology for diagnosing and fixing query performance issues.

EXPLAIN ANALYZEPerformance TroubleshootingPostgreSQL
0 likes · 32 min read
EXPLAIN ANALYZE: The CT Scan That Triggers a Query Performance Revolution
StarRocks
StarRocks
Apr 16, 2026 · Databases

Why Traditional Databases Stall AI Agents—and How StarRocks Overcomes the Bottleneck

Traditional databases were built for low‑frequency, human‑driven queries, but AI agents generate dozens of concurrent, sub‑second queries that expose architectural limits, and StarRocks addresses these challenges with self‑healing optimization, real‑time data pipelines, extreme concurrency handling, and seamless lakehouse access.

Database ConcurrencyQuery OptimizationSelf-Healing
0 likes · 13 min read
Why Traditional Databases Stall AI Agents—and How StarRocks Overcomes the Bottleneck
Data Party THU
Data Party THU
Mar 23, 2026 · Artificial Intelligence

Boosting RAG Performance: Query Translation & Decomposition Techniques

The article explains two emerging RAG query‑optimization approaches—query translation and query decomposition—detailing fan‑out retrieval, reciprocal rank fusion, HyDE, step‑back prompting, and chain‑of‑thought retrieval, and shows how combining them can improve relevance and latency in LLM‑augmented systems.

LLMQuery OptimizationRAG
0 likes · 9 min read
Boosting RAG Performance: Query Translation & Decomposition Techniques
Code Wrench
Code Wrench
Mar 23, 2026 · Databases

Why Your MySQL Queries Slow Down in Production and How to Fix Them

The article explains why locally fast MySQL queries can become painfully slow in production, identifies three common pitfalls such as ineffective indexes, oversized Go connection pools, and hidden type conversions, and provides concrete optimization techniques spanning index design, query planning, Go‑MySQL integration, and systematic self‑checks.

GoIndexingQuery Optimization
0 likes · 9 min read
Why Your MySQL Queries Slow Down in Production and How to Fix Them
Java Architect Handbook
Java Architect Handbook
Mar 18, 2026 · Information Security

How to Enable Fuzzy Search on Encrypted Data: Methods, Pros & Cons

This article analyzes three categories of techniques—naïve, conventional, and advanced—for performing fuzzy queries on encrypted fields, compares their memory and performance trade‑offs, provides concrete code examples and storage calculations, and recommends the most practical approach for production systems.

Database SecurityQuery Optimizationencrypted data
0 likes · 13 min read
How to Enable Fuzzy Search on Encrypted Data: Methods, Pros & Cons
Top Architect
Top Architect
Jan 18, 2026 · Databases

Boost Your SQL Query Speed: Proven Optimization Techniques

This guide walks through the SQL query processing workflow, explains how parsing, optimization, and execution plans affect performance, and provides concrete techniques—such as selecting specific columns, avoiding DISTINCT, using proper indexes, preferring EXISTS over COUNT, limiting result sets, and replacing HAVING with WHERE—to dramatically speed up queries while keeping statements concise.

Database PerformanceQuery OptimizationSQL
0 likes · 8 min read
Boost Your SQL Query Speed: Proven Optimization Techniques
Tech Freedom Circle
Tech Freedom Circle
Jan 3, 2026 · Databases

How Many Values Can MySQL IN Handle? 100K‑10M IDs and the Ultimate Solution

An interview scenario reveals that using a massive IN list (hundreds of thousands of IDs) in MySQL quickly exceeds packet limits, forces full‑table scans, and overloads CPU, while batch IN queries cause network storms; the article dissects these pitfalls and presents three progressive strategies—splitting IN, temporary tables with JOIN, and sharding with stream‑merge—to safely query massive ID sets.

IN ClauseQuery Optimizationmysql
0 likes · 32 min read
How Many Values Can MySQL IN Handle? 100K‑10M IDs and the Ultimate Solution
dbaplus Community
dbaplus Community
Dec 28, 2025 · Databases

How Treating Your Database as a Compute Engine Boosted Dashboard Speed 166×

The article explains why teams mistakenly use databases merely as storage, shows a real‑world Node.js dashboard that suffered severe latency, demonstrates how moving aggregation and filtering into SQL queries eliminated N+1 and filtering issues, and presents dramatic performance gains and best‑practice guidelines.

Node.jsORMQuery Optimization
0 likes · 17 min read
How Treating Your Database as a Compute Engine Boosted Dashboard Speed 166×
Su San Talks Tech
Su San Talks Tech
Dec 22, 2025 · Databases

10 Advanced SQL Techniques Every Data Professional Should Master

This article presents ten powerful SQL techniques—including CTEs, recursive CTEs, temporary functions, CASE‑WHEN pivots, EXCEPT vs NOT IN, self‑joins, ranking functions, delta calculations, cumulative sums, and datetime manipulation—complete with explanations and runnable code examples for data analysts and engineers.

CTEQuery OptimizationWindow Functions
0 likes · 13 min read
10 Advanced SQL Techniques Every Data Professional Should Master
Lakehouse Research Base
Lakehouse Research Base
Dec 21, 2025 · Databases

StarRocks Join Performance Gap: Paimon/Iceberg vs Internal Tables - Root Causes & Fixes

This article analyzes why StarRocks multi-table joins on Paimon/Iceberg tables remain significantly slower than internal tables even after cache warming, detailing six root causes across storage format, data layout, caching, execution engine, metadata, and version management, plus four optimization strategies to narrow the gap.

IcebergJoin PerformanceOLAP
0 likes · 17 min read
StarRocks Join Performance Gap: Paimon/Iceberg vs Internal Tables - Root Causes & Fixes
dbaplus Community
dbaplus Community
Dec 16, 2025 · Databases

83 Proven SQL Optimization Techniques to Supercharge Your Database

This comprehensive guide presents 83 practical SQL optimization scenarios—from avoiding SELECT * and replacing IN with EXISTS to leveraging materialized views, partitioning, and advanced indexing—each illustrated with real‑world use cases and exact code snippets to help developers dramatically improve query performance and reduce resource consumption.

Performance TuningQuery OptimizationSQL
0 likes · 55 min read
83 Proven SQL Optimization Techniques to Supercharge Your Database
dbaplus Community
dbaplus Community
Dec 7, 2025 · Databases

Postgres vs MySQL: Deep Dive into Indexes, Query Costs, and MVCC

An in‑depth comparison of PostgreSQL and MySQL examines how primary and secondary indexes are implemented, contrasts query execution costs, explores data type impacts, explains MVCC undo logs, and discusses process versus thread architectures, highlighting performance trade‑offs for each system.

MVCCPostgreSQLQuery Optimization
0 likes · 13 min read
Postgres vs MySQL: Deep Dive into Indexes, Query Costs, and MVCC
Architect's Guide
Architect's Guide
Dec 5, 2025 · Databases

When Should You Turn Off MySQL’s prefer_ordering_index? A Deep Dive

This article explains the purpose of MySQL's optimizer_switch prefer_ordering_index, shows how it affects ORDER BY and GROUP BY queries with LIMIT, provides code examples and EXPLAIN output, and argues that disabling it often yields better performance for skewed data distributions.

Index ScanQuery Optimizationmysql
0 likes · 7 min read
When Should You Turn Off MySQL’s prefer_ordering_index? A Deep Dive
Top Architect
Top Architect
Dec 2, 2025 · Databases

30 Essential SQL Query Optimization Tips from a Senior Architect

This article compiles thirty practical SQL performance tips, covering index usage, avoiding full table scans, proper handling of NULLs, OR/IN clauses, functions, temporary tables, cursors, and transaction size, each illustrated with clear example queries and recommended rewrites.

Query OptimizationSQLindexes
0 likes · 12 min read
30 Essential SQL Query Optimization Tips from a Senior Architect
Architect
Architect
Nov 25, 2025 · Databases

When Should You Disable MySQL’s prefer_ordering_index? A Practical Guide

This article explains the purpose of MySQL’s optimizer_switch ‘prefer_ordering_index’, shows how it affects ORDER BY/GROUP BY queries with LIMIT, provides sample tables and procedures, compares EXPLAIN output with the option ON and OFF, and concludes why disabling it is often the safer choice for most workloads.

EXPLAINIndex ScanQuery Optimization
0 likes · 8 min read
When Should You Disable MySQL’s prefer_ordering_index? A Practical Guide
Java Companion
Java Companion
Nov 21, 2025 · Databases

How to Query Phone Number Suffix in Milliseconds on 50 Million Records

When faced with 50 million user rows, using LIKE '%1234' triggers a full table scan, but adding a suffix column, reversing the phone number, leveraging function indexes, or integrating Elasticsearch can reduce query time from minutes to a few milliseconds, each with its own trade‑offs.

ElasticsearchIndexingQuery Optimization
0 likes · 12 min read
How to Query Phone Number Suffix in Milliseconds on 50 Million Records
Rare Earth Juejin Tech Community
Rare Earth Juejin Tech Community
Nov 19, 2025 · Backend Development

Master Elasticsearch: Index Design, Field Types, and Cluster Management Tips

An experienced engineer shares practical Elasticsearch insights covering index design with aliases and routing, field type choices, query optimization techniques, pagination strategies, real‑time refresh settings, memory limits, and cluster management, offering concrete examples and actionable recommendations for robust search implementations.

ElasticsearchField TypesQuery Optimization
0 likes · 12 min read
Master Elasticsearch: Index Design, Field Types, and Cluster Management Tips
Alibaba Cloud Native
Alibaba Cloud Native
Nov 15, 2025 · Cloud Native

How Materialized Views Supercharge Alibaba Cloud Log Service Queries

When log volumes explode from gigabytes to petabytes, Alibaba Cloud Log Service’s traditional on‑the‑fly querying becomes slow, resource‑hungry, and inaccurate, but materialized views pre‑compute and store results, delivering seconds‑level responses with far lower resource consumption.

Cloud NativeLog AnalyticsMaterialized View
0 likes · 11 min read
How Materialized Views Supercharge Alibaba Cloud Log Service Queries
Architect's Guide
Architect's Guide
Nov 5, 2025 · Databases

25 Essential SQL Query Optimization Tips to Avoid Full Table Scans

This article presents a comprehensive set of SQL performance guidelines, covering index creation, avoiding costly operators, rewriting predicates, using proper joins, limiting temporary objects, and best practices for query design to prevent full table scans and improve overall database efficiency.

Query OptimizationSQLdatabase
0 likes · 9 min read
25 Essential SQL Query Optimization Tips to Avoid Full Table Scans
Senior Brother's Insights
Senior Brother's Insights
Oct 16, 2025 · Databases

Master MySQL JOIN Optimization: Understanding Driving and Driven Tables

This article explains the concepts of driving and driven tables in MySQL JOIN queries, shows how the optimizer chooses their order based on data size, indexes and filters, provides concrete examples with EXPLAIN output, and offers practical tips to control and improve table selection for better performance.

EXPLAINIndexQuery Optimization
0 likes · 8 min read
Master MySQL JOIN Optimization: Understanding Driving and Driven Tables
Architect
Architect
Sep 28, 2025 · Information Security

How to Perform Fuzzy Searches on Encrypted Data: Practical Approaches

This article examines three categories of techniques—naïve, conventional, and advanced—for enabling fuzzy queries on encrypted data, evaluates their pros and cons, provides implementation details, performance considerations, and references to real‑world solutions, guiding developers toward secure and efficient search strategies.

Query Optimizationdatabaseencrypted data
0 likes · 12 min read
How to Perform Fuzzy Searches on Encrypted Data: Practical Approaches
Lakehouse Research Base
Lakehouse Research Base
Sep 16, 2025 · Databases

StarRocks 3.3 Indexes: When to Use Bitmap, Bloom Filter, N-Gram & Full-Text Search

This guide details the four index types in StarRocks 3.3—Bitmap, Bloom Filter, N-Gram Bloom Filter, and Full-Text Inverted Index—covering their core principles, supported data types, creation syntax, ideal use cases (low/high cardinality, fuzzy/long-text queries), anti-patterns, and real-world optimization examples with performance metrics.

Bitmap IndexBloom filterDatabase Indexing
0 likes · 27 min read
StarRocks 3.3 Indexes: When to Use Bitmap, Bloom Filter, N-Gram & Full-Text Search
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.

Query OptimizationSQL performancebinary search
0 likes · 11 min read
Why Database Indexes Speed Up Queries: From Storage Basics to Binary Search
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.

Performance TuningQuery Optimizationdatabase scaling
0 likes · 24 min read
Master MySQL Performance: From Slow Queries to Billion-Row Optimization
Big Data Technology & Architecture
Big Data Technology & Architecture
Sep 10, 2025 · Databases

When to Use Materialized Views in Production: Benefits, Types, and Pitfalls

This article explains what materialized views are, outlines their advantages such as query acceleration, lightweight ETL, and lake‑warehouse integration, classifies them by sync mode, table count, and refresh strategy, and highlights their limitations and best‑practice recommendations for production use.

Database PerformanceETLQuery Optimization
0 likes · 6 min read
When to Use Materialized Views in Production: Benefits, Types, and Pitfalls
Code Mala Tang
Code Mala Tang
Sep 1, 2025 · Databases

Boost SQL Query Performance: Practical Tips and Code Examples

This article presents a collection of practical techniques—such as using SELECT TOP, avoiding DISTINCT, preferring IN over OR, indexing key columns, and leveraging stored procedures—to dramatically improve SQL query speed and reduce database load.

Query OptimizationSQLdatabase
0 likes · 10 min read
Boost SQL Query Performance: Practical Tips and Code Examples
ITPUB
ITPUB
Aug 15, 2025 · Databases

Postgres vs MySQL: Deep Dive into Indexes, Query Costs, and MVCC Differences

This article compares PostgreSQL and MySQL by examining their index structures, query execution costs, data‑type impacts, undo‑log mechanisms, and process models, highlighting where each system excels or falls short in real‑world workloads.

MVCCPostgreSQLQuery Optimization
0 likes · 13 min read
Postgres vs MySQL: Deep Dive into Indexes, Query Costs, and MVCC Differences
dbaplus Community
dbaplus Community
Aug 14, 2025 · Databases

10 Common Reasons Your PostgreSQL Queries Are Slow—and How to Fix Them

Discover the ten most frequent causes of sluggish PostgreSQL queries—from missing or inefficient indexes and poor query design to lack of vacuuming, table bloat, wrong data types, excessive sequential scans, deadlocks, missing connection pooling, overused ORDER BY/DISTINCT, and misconfigured settings—and learn concrete steps to resolve each issue.

PostgreSQLQuery OptimizationVACUUM
0 likes · 6 min read
10 Common Reasons Your PostgreSQL Queries Are Slow—and How to Fix Them
Code Ape Tech Column
Code Ape Tech Column
Aug 13, 2025 · Databases

Master 10 Essential Advanced SQL Concepts Every Data Professional Needs

This article outlines ten crucial intermediate‑to‑advanced SQL techniques—including common table expressions, recursive CTEs, temporary functions, CASE‑WHEN pivots, EXCEPT vs NOT IN, self‑joins, ranking window functions, delta calculations, cumulative sums, and date‑time manipulation—providing explanations and practical query examples for data professionals.

CTEQuery OptimizationWindow Functions
0 likes · 11 min read
Master 10 Essential Advanced SQL Concepts Every Data Professional Needs
Big Data Technology Tribe
Big Data Technology Tribe
Aug 5, 2025 · Big Data

How Spark’s Catalyst Optimizer Transforms SQL Queries: Trees, Rules, and Code Generation

This article explains Spark SQL’s Catalyst optimizer, describing its extensible design, tree‑based representation, rule‑driven transformations, batch execution to a fixed point, and how Scala’s pattern matching and quasiquotes enable efficient analysis, logical optimization, physical planning, and code generation.

Catalyst OptimizerQuery OptimizationScala
0 likes · 18 min read
How Spark’s Catalyst Optimizer Transforms SQL Queries: Trees, Rules, and Code Generation
Mingyi World Elasticsearch
Mingyi World Elasticsearch
Jul 25, 2025 · Backend Development

Pitfall Diary: Practical Lessons on Using Elasticsearch Nested Types

After a failed flatten‑field migration from MySQL to Elasticsearch caused incorrect product matches, the team introduced nested types, redesigned mappings, rewrote queries with nested and inner_hits, optimized performance, documented pitfalls, and concluded that nested types solve one‑to‑many relations but require careful evaluation.

Data ModelingElasticsearchNested Type
0 likes · 15 min read
Pitfall Diary: Practical Lessons on Using Elasticsearch Nested Types
Java Tech Enthusiast
Java Tech Enthusiast
Jul 14, 2025 · Databases

13 Proven SQL Optimization Tricks to Supercharge Your Queries

This article presents a comprehensive set of practical SQL optimization techniques—including proper indexing, selective column retrieval, efficient joins, query planning, and database‑specific features—to dramatically improve query speed, reduce resource consumption, and enhance overall application performance.

JoinsQuery OptimizationSQL
0 likes · 12 min read
13 Proven SQL Optimization Tricks to Supercharge Your Queries
Java Tech Enthusiast
Java Tech Enthusiast
Jul 2, 2025 · Databases

Why MySQL GROUP BY Can Be Slow and How to Optimize It

This article explains why MySQL GROUP BY queries may run slowly, analyzes the execution plan showing temporary tables and filesort, and provides practical optimization techniques such as removing unnecessary sorting, adding indexes, adjusting buffer settings, and considering application‑level or materialized view solutions.

IndexingQuery Optimizationdatabases
0 likes · 4 min read
Why MySQL GROUP BY Can Be Slow and How to Optimize It
IT Services Circle
IT Services Circle
Jun 20, 2025 · Databases

Why MySQL GROUP BY Can Be Slow and How to Optimize It

This article explains why MySQL GROUP BY queries may run slowly due to missing indexes, temporary tables, and filesort, and provides practical optimization techniques such as removing unnecessary sorting, adding indexes, tuning buffers, and using application‑level or materialized view solutions.

IndexingPerformance TuningQuery Optimization
0 likes · 6 min read
Why MySQL GROUP BY Can Be Slow and How to Optimize It
Tech Freedom Circle
Tech Freedom Circle
Jun 6, 2025 · Databases

How to Design the Optimal Index for a 27‑Column Table Query (Alibaba Interview)

This article analyzes a typical Alibaba interview question that asks for the best index on a table with 27 columns for the query SELECT a, b, c FROM table WHERE d = X ORDER BY e, covering index design, field ordering, comparisons with alternative indexes, index condition pushdown, and the detailed MySQL execution process.

Covering IndexQuery Optimizationexecution plan
0 likes · 28 min read
How to Design the Optimal Index for a 27‑Column Table Query (Alibaba Interview)
Java Tech Enthusiast
Java Tech Enthusiast
Jun 3, 2025 · Backend Development

Common MySQL Index Failure Scenarios and How to Diagnose Them

This article presents a series of MySQL interview questions and answers that illustrate typical situations where indexes become ineffective—such as leading wildcards in LIKE, NOT IN, NOT EXISTS, functions, inequality operators, OR conditions, mismatched ORDER BY, large result sets, and explains how to analyze execution plans and use FORCE INDEX to improve performance.

IndexQuery OptimizationSQL
0 likes · 8 min read
Common MySQL Index Failure Scenarios and How to Diagnose Them