BestHub
Sign in
Tagged articles

Query Optimization

433 articles · Page 1 of 5
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 DiagnosticsBig DataDistributed Computing
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.

Artificial IntelligenceHyDEKeyword Enhancement
0 likes · 9 min read
How We Boosted RAG Recall by 15% with Practical Query Optimization Techniques
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.

DatabaseIndexingMySQL
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.

DatabaseIndexingQuery Optimization
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.

MySQLQuery OptimizationSQL
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 PerformanceIndex TuningMySQL
0 likes · 3 min read
How Much SQL Do You Really Need to Master for Real‑World Development?
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 IcebergData LakeFederated Query
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.

MySQLOR queryQuery Optimization
0 likes · 10 min read
Why Does an OR Between Two Indexed Columns Still Trigger a Full Table Scan?
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.

InnoDBLIMIT OffsetMySQL
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.

DatabasePerformance TuningQuery Optimization
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.

EXPLAINMySQLQuery Optimization
0 likes · 32 min read
10 Common MySQL Index Failure Scenarios – Which Ones Affect You?
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.

JOINMySQLQuery Optimization
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.

IndexLATERALMySQL
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.

JOINMySQLPHP
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 ANALYZEPostgreSQLQuery Optimization
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 ConcurrencyLakehouseQuery Optimization
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.

GoIndexingMySQL
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 SecurityFuzzy SearchQuery Optimization
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 ClauseMySQLQuery Optimization
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
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.

MVCCMySQLPostgreSQL
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 ScanMySQLQuery Optimization
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 ScanMySQL
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.

ElasticsearchIndexingMySQL
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.

Cluster ManagementElasticsearchField Types
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.

Log AnalyticsQuery Optimizationcloud-native
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.

DatabaseQuery OptimizationSQL
0 likes · 9 min read
25 Essential SQL Query Optimization Tips to Avoid Full Table Scans
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.

DatabaseFuzzy SearchQuery Optimization
0 likes · 12 min read
How to Perform Fuzzy Searches on Encrypted Data: Practical Approaches
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 PerformanceStorage Architecture
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.

Database ScalingMySQLPerformance Tuning
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.

Data WarehousingDatabase PerformanceETL
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.

DatabaseQuery OptimizationSQL
0 likes · 10 min read
Boost SQL Query Performance: Practical Tips and Code Examples
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 Optimizationindexes
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.

CTEDatabaseQuery Optimization
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.

Big DataCatalyst OptimizerQuery Optimization
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.

ElasticsearchNested TypePerformance Tuning
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.

GROUP BYIndexingMySQL
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.

GROUP BYIndexingMySQL
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 IndexMySQLQuery Optimization
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.

DatabaseIndexMySQL
0 likes · 8 min read
Common MySQL Index Failure Scenarios and How to Diagnose Them
Java Backend Full-Stack
Java Backend Full-Stack
May 1, 2025 · Databases

Common Interview Question: When Does a MySQL Index Fail? Hands‑On Demo

This article walks through a mini‑project that creates a student table, adds indexes, populates it with 100 k rows, and then demonstrates six concrete scenarios—such as leading wildcards, arithmetic on indexed columns, functions, type mismatches, composite‑index misuse, and encoding differences—that cause MySQL indexes to become ineffective, showing the EXPLAIN output for each case.

Database PerformanceEXPLAINIndex
0 likes · 8 min read
Common Interview Question: When Does a MySQL Index Fail? Hands‑On Demo
ByteDance Data Platform
ByteDance Data Platform
Apr 25, 2025 · Databases

How ByteDance’s AQETuner Cuts Query Latency by 23% and Boosts Reliability

ByteDance Data Platform’s recent breakthroughs in database research—spanning query‑level Bayesian tuning, adaptive stream‑processing parallelism, and learned cardinality estimation—were highlighted by two papers accepted at VLDB 2025 and ICDE 2025, showcasing significant performance gains and real‑world deployments.

AIQuery OptimizationStream Processing
0 likes · 5 min read
How ByteDance’s AQETuner Cuts Query Latency by 23% and Boosts Reliability
21CTO
21CTO
Apr 24, 2025 · Databases

What’s New in MySQL 9.3? Backup, JavaScript Support, and Group Replication Explained

MySQL 9.3 introduces powerful backup enhancements, JavaScript stored‑procedure support, advanced query optimization, and a new Group Replication primary‑election feature, all aimed at improving reliability, performance, and developer flexibility, making it a compelling upgrade for enterprises and developers alike.

Group ReplicationJavaScript Stored ProceduresMySQL
0 likes · 3 min read
What’s New in MySQL 9.3? Backup, JavaScript Support, and Group Replication Explained
IT Xianyu
IT Xianyu
Apr 18, 2025 · Databases

Comprehensive Guide to SQL Basics, Advanced Queries, and Performance Optimization

This article provides a thorough tutorial on SQL fundamentals, including statement order, core clause rules, templates for SELECT/INSERT/UPDATE/DELETE, practical multi‑table query cases, join and subquery techniques, common pitfalls, index optimization strategies, experimental performance data, and a structured learning path for mastering database development.

IndexingJoinsPerformance Tuning
0 likes · 7 min read
Comprehensive Guide to SQL Basics, Advanced Queries, and Performance Optimization
dbaplus Community
dbaplus Community
Apr 15, 2025 · Big Data

How Xiaohongshu Boosted Data Warehouse Performance with Logical Datasets and Materialized Views

Xiaohongshu introduced logical datasets and materialized views to overcome low reuse of APP tables, limited scalability of single‑table BI datasets, and poor dashboard query performance, achieving higher data processing efficiency and faster query responses through optimized data flow, query pruning, and accelerated ETL scheduling.

Big DataQuery Optimizationlogical dataset
0 likes · 24 min read
How Xiaohongshu Boosted Data Warehouse Performance with Logical Datasets and Materialized Views
Mingyi World Elasticsearch
Mingyi World Elasticsearch
Apr 15, 2025 · Operations

Why You Shouldn’t Hand Out Kibana Permissions Lightly

A careless Kibana query—such as selecting the past 30 days without filters—can overload an Elasticsearch node, causing CPU spikes, GC storms, and service outages; the article explains why unrestricted access is risky and outlines a three‑layer safety strategy with concrete configuration examples.

Cluster SafetyElasticsearchKibana
0 likes · 8 min read
Why You Shouldn’t Hand Out Kibana Permissions Lightly
Java Tech Enthusiast
Java Tech Enthusiast
Apr 14, 2025 · Databases

SQL Optimization Steps and Common Scenarios

To improve MySQL performance, first identify slow queries via logs, then examine their EXPLAIN plans and profiling data, understand optimizer traces, and apply fixes such as proper left‑most indexing, avoiding implicit conversions, large offsets, unsuitable IN/ORDER BY patterns, range‑blocking, NOT‑IN predicates, and consider alternative storage or pagination strategies for complex or massive datasets.

IndexingMySQLQuery Optimization
0 likes · 10 min read
SQL Optimization Steps and Common Scenarios
MaGe Linux Operations
MaGe Linux Operations
Apr 12, 2025 · Databases

Master MySQL Remote Connections and Query Optimization: Tips & Tricks

This article explains how to create and grant MySQL users for remote access, describes client‑server communication methods such as TCP/IP, named pipes, shared memory, and Unix sockets, and provides detailed guidance on index usage, common pitfalls, and performance‑friendly techniques for query optimization and large OFFSET LIMIT handling.

IndexingMySQLQuery Optimization
0 likes · 17 min read
Master MySQL Remote Connections and Query Optimization: Tips & Tricks
Sohu Tech Products
Sohu Tech Products
Apr 9, 2025 · Databases

Six Critical MySQL Index Pitfalls and How to Fix Them

This article analyzes six common MySQL query performance traps—type conversion, function usage, left‑most prefix, implicit charset conversion, left‑most match, and optimizer mis‑selection—illustrates each with real‑world SQL examples, explains why they degrade performance, and provides concrete remediation steps and verification tools.

DatabaseIndexingMySQL
0 likes · 5 min read
Six Critical MySQL Index Pitfalls and How to Fix Them
360 Zhihui Cloud Developer
360 Zhihui Cloud Developer
Apr 2, 2025 · Databases

How VictoriaMetrics' Distributed Architecture Scales Massive Time‑Series Data

VictoriaMetrics employs a modular, horizontally scalable architecture composed of vmagent, vminsert, vmstorage, vmselect, and vmalert, each handling data collection, ingestion, storage, querying, and alerting, while leveraging consistent hashing, LSM‑tree storage, TSID indexing, and multi‑tenant isolation to efficiently manage large‑scale time‑series workloads.

Query OptimizationTime Series DatabaseVictoriaMetrics
0 likes · 11 min read
How VictoriaMetrics' Distributed Architecture Scales Massive Time‑Series Data
DataFunSummit
DataFunSummit
Mar 12, 2025 · Big Data

Principles and Common Optimization Techniques of the Spark SQL Optimizer

This article explains the underlying principles of the Spark SQL optimizer and presents three classic optimization paradigms—push‑down optimization, operator elimination/merging, and expression elimination/replacement—illustrating each with concrete rule implementations and code examples.

Big DataOptimizerQuery Optimization
0 likes · 12 min read
Principles and Common Optimization Techniques of the Spark SQL Optimizer
Alibaba Cloud Big Data AI Platform
Alibaba Cloud Big Data AI Platform
Feb 28, 2025 · Databases

How MaxCompute’s Intelligent Data Warehouse Optimizes Queries with AutoMV

This article explains MaxCompute’s intelligent data warehouse architecture, its self‑learning optimization pipeline, the role of intelligent materialized views, the automated recommendation system for materialized views, and the AutoMV feature that automatically creates, updates, and cleans up materialized views to reduce compute costs and improve query performance.

AutoMVBig DataMaxCompute
0 likes · 17 min read
How MaxCompute’s Intelligent Data Warehouse Optimizes Queries with AutoMV
Programmer1970
Programmer1970
Feb 24, 2025 · Databases

Deep Dive into MySQL Join Algorithms: Principles, Implementation, and Optimization

This article thoroughly examines MySQL join operations, detailing the fundamentals, inner workings, and performance characteristics of Nested‑Loop Join, Block Nested‑Loop Join, and Hash Join, while providing practical optimization techniques, parameter tuning, and decision‑making guidance for different data sizes and query patterns.

Block Nested Loop JoinDatabase PerformanceHash Join
0 likes · 11 min read
Deep Dive into MySQL Join Algorithms: Principles, Implementation, and Optimization
Xiaohongshu Tech REDtech
Xiaohongshu Tech REDtech
Feb 20, 2025 · Big Data

How Xiaohongshu Accelerated Data Warehouse Queries with Logical Datasets & Materialized Views

Xiaohongshu tackled low reuse of APP tables, limited scalability of single-table BI datasets, and poor dashboard query performance by introducing logical datasets and materialized views, which enable query pruning, reduce data redundancy, and accelerate BI queries, achieving up to 80% latency reduction and higher hit rates.

BIBig DataQuery Optimization
0 likes · 25 min read
How Xiaohongshu Accelerated Data Warehouse Queries with Logical Datasets & Materialized Views
JD Tech Talk
JD Tech Talk
Feb 5, 2025 · Databases

Optimizing Query Performance and Data Architecture for JD BIP Procurement System

This article details how JD’s BIP procurement system tackled massive data volume and complex query performance issues by reducing slow SQL, partitioning “in‑stock” orders, migrating large datasets to Elasticsearch, implementing dynamic query routing, and establishing robust monitoring, resulting in a 92% data reduction and smoother operations.

ElasticsearchQuery Optimization
0 likes · 11 min read
Optimizing Query Performance and Data Architecture for JD BIP Procurement System
JD Cloud Developers
JD Cloud Developers
Feb 5, 2025 · Databases

Cutting Procurement Query Times by 92%: Data Heterogeneity & ES Strategies

This case study details how the BIP procurement system tackled massive data volume, complex queries, and slow SQL by segmenting inbound orders, leveraging Elasticsearch, introducing a dynamic routing layer, and implementing robust ES high‑availability and monitoring, ultimately reducing query load by over 90%.

Big DataQuery Optimizationdata modeling
0 likes · 14 min read
Cutting Procurement Query Times by 92%: Data Heterogeneity & ES Strategies
Raymond Ops
Raymond Ops
Jan 23, 2025 · Databases

Mastering SQL Execution Order & Multi‑Table Join Optimization

This guide explains the exact sequence in which SQL statements are processed, offers practical tips for optimizing multi‑table joins, and provides comprehensive index recommendations and mnemonic rules to improve query performance and reduce resource consumption.

IndexJOINQuery Optimization
0 likes · 8 min read
Mastering SQL Execution Order & Multi‑Table Join Optimization
Architect's Guide
Architect's Guide
Jan 17, 2025 · Databases

Understanding MySQL Multi‑Table Join Performance Compared to PostgreSQL and Query Decomposition Strategies

The article compares MySQL and PostgreSQL multi‑table join capabilities, explains why MySQL’s nested‑loop joins can be less efficient for complex queries, and discusses how decomposing joins into separate single‑table queries or moving logic to the service layer can improve performance, scalability, and caching.

Database PerformanceJOINMySQL
0 likes · 7 min read
Understanding MySQL Multi‑Table Join Performance Compared to PostgreSQL and Query Decomposition Strategies
Mike Chen's Internet Architecture
Mike Chen's Internet Architecture
Dec 30, 2024 · Backend Development

How to Define and Tackle High Concurrency: Strategies and Code Samples

This article explains what constitutes high concurrency, categorizes load levels, and presents practical solutions such as load balancing, database sharding, query optimization, caching, message queues, and rate‑limiting, complete with code examples for implementing these techniques in backend systems.

Query Optimizationdatabase shardinghigh-concurrency
0 likes · 7 min read
How to Define and Tackle High Concurrency: Strategies and Code Samples
ITPUB
ITPUB
Dec 26, 2024 · Databases

Why SELECT * Can Kill Your API Performance: Lessons from a 2012 Database Mishap

This article recounts a 2012 incident where a backend API slowed dramatically after hidden BLOB columns were added, and explains why using SELECT *—which blocks index‑only scans, forces extra I/O, increases deserialization, network, and client processing costs, and hampers schema maintenance—should be avoided in favor of explicit column lists.

Database PerformancePostgreSQLQuery Optimization
0 likes · 7 min read
Why SELECT * Can Kill Your API Performance: Lessons from a 2012 Database Mishap
21CTO
21CTO
Dec 16, 2024 · Databases

Why SELECT * Slows Down Your Database and How to Avoid It

The article recounts a 2012 incident where a seemingly fast backend API became sluggish after hidden blob columns were added, explains how SELECT * forces full table scans, extra deserialization, network overhead, and unpredictable performance, and advises selecting only needed columns for optimal efficiency.

Database PerformancePostgreSQLQuery Optimization
0 likes · 7 min read
Why SELECT * Slows Down Your Database and How to Avoid It