BestHub
Sign in
Tagged articles

Query Optimization

416 articles · Page 1 of 5
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.

Execution PlanQuery OptimizationSQL
0 likes · 12 min read
Are You Still Using These 8 Inefficient SQL Patterns?
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.

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

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

Database TuningEXPLAINIndex 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.

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

Execution PlanLATERALQuery Optimization
0 likes · 8 min read
How to Cut a Production MySQL 8.0 LATERAL Query from 3 s to 0.8 s
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.

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 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 PerformanceIndexesQuery Optimization
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 OptimizationSharding
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.

DatabasesNode.jsORM
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.

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

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

IndexesQuery OptimizationSQL
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.

Database DesignElasticsearchIndexing
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.

ElasticsearchQuery Optimizationcluster management
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 AnalyticsQuery Optimization
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.

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

Fuzzy SearchQuery Optimizationdatabase
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.

Binary SearchClustered IndexQuery Optimization
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.

Index OptimizationPerformance TuningQuery Optimization
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.

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

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

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.

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

DatabasesGROUP BYIndexing
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 BYIndexingPerformance Tuning
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 IndexExecution PlanQuery 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.

Query OptimizationSQLdatabase
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 PerformanceEXPLAINQuery Optimization
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.

AIDatabasesQuery Optimization
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 ProceduresQuery Optimization
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.

IndexingQuery OptimizationSQL
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.

IndexingQuery OptimizationRemote Connection
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.

IndexingQuery OptimizationSQL
0 likes · 5 min read
Six Critical MySQL Index Pitfalls and How to Fix Them
Aikesheng Open Source Community
Aikesheng Open Source Community
Apr 9, 2025 · Databases

Understanding MySQL EXPLAIN Output Formats and Using JSON for Detailed Query Cost Analysis

This article explains the three MySQL EXPLAIN output formats—TRADITIONAL, JSON, and TREE—demonstrates how the JSON format provides richer cost and data‑read information, compares query costs of sample SQL statements, and offers practical optimization suggestions for poorly performing joins.

Database PerformanceEXPLAINQuery Optimization
0 likes · 9 min read
Understanding MySQL EXPLAIN Output Formats and Using JSON for Detailed Query Cost Analysis
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 OptimizationVictoriaMetricsscalable storage
0 likes · 11 min read
How VictoriaMetrics' Distributed Architecture Scales Massive Time‑Series Data
Cognitive Technology Team
Cognitive Technology Team
Mar 28, 2025 · Databases

Index Condition Pushdown (ICP) Optimization in MySQL

Index Condition Pushdown (ICP) is a MySQL query‑optimization technique that pushes eligible WHERE predicates to the storage engine so that index entries can filter rows early, reducing row fetches and server‑engine communication.

DatabasesQuery OptimizationSQL
0 likes · 5 min read
Index Condition Pushdown (ICP) Optimization in MySQL
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 DataQuery OptimizationRule Engine
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 DataData Warehouse
0 likes · 17 min read
How MaxCompute’s Intelligent Data Warehouse Optimizes Queries with AutoMV
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 DataData Warehouse
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 DataDatabasesPerformance Optimization
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.

JOINQuery OptimizationSQL
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 PerformanceJOINPostgreSQL
0 likes · 7 min read
Understanding MySQL Multi‑Table Join Performance Compared to PostgreSQL and Query Decomposition Strategies
Aikesheng Open Source Community
Aikesheng Open Source Community
Jan 6, 2025 · Databases

Understanding MySQL EXPLAIN Type Column and JOIN Types: ALL, index_merge, and Optimization Techniques

This article explains MySQL EXPLAIN type column, detailing the inefficiencies of type=ALL, how adding ORDER BY can change it to index, and demonstrates the use of INDEX_MERGE for UNION and INTERSECT queries, including practical examples and cost comparisons to improve query performance.

Database PerformanceEXPLAINQuery Optimization
0 likes · 8 min read
Understanding MySQL EXPLAIN Type Column and JOIN Types: ALL, index_merge, and Optimization Techniques
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.

High concurrencyQuery Optimizationdatabase sharding
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
Ctrip Technology
Ctrip Technology
Nov 21, 2024 · Big Data

Performance Governance and Optimization of Ctrip's Nova Data Reporting Platform

This article details the performance challenges of Ctrip's Nova data reporting platform and describes a series of governance measures—including multi‑dimensional data caching, materialized view acceleration, query strategy optimization, and SQL quality improvements—that collectively reduced average query latency by over 50% and stabilized the system.

CachingData PlatformQuery Optimization
0 likes · 26 min read
Performance Governance and Optimization of Ctrip's Nova Data Reporting Platform
Aikesheng Open Source Community
Aikesheng Open Source Community
Nov 11, 2024 · Databases

New JSON Format for EXPLAIN and EXPLAIN ANALYZE in MySQL 8.3+

MySQL 8.3 introduces a new iterator‑aware JSON output for EXPLAIN and EXPLAIN ANALYZE, selectable via the explain_json_format_version variable, which aligns JSON with the tree format, provides richer execution statistics, and can be accessed programmatically through EXPLAIN INTO across all recent MySQL releases.

DatabasesEXPLAINQuery Optimization
0 likes · 11 min read
New JSON Format for EXPLAIN and EXPLAIN ANALYZE in MySQL 8.3+
Shopee Tech Team
Shopee Tech Team
Oct 25, 2024 · Big Data

StarRocks at Shopee: Practical Use Cases and Performance Analysis

Shopee’s deployment of StarRocks across DataService, DataGo, and DataStudio demonstrates that its vectorized engine, cost‑based optimizer, and materialized‑view caching can query Hive, Iceberg, Delta Lake and Hudi up to 20,000× faster than Presto, cutting CPU usage and delivering consistently lower latency for complex analytics.

Data LakeHiveMPP
0 likes · 11 min read
StarRocks at Shopee: Practical Use Cases and Performance Analysis
Java Architect Essentials
Java Architect Essentials
Oct 23, 2024 · Databases

Does Varchar Length Really Affect MySQL Storage and Performance?

This article investigates whether MySQL varchar column length influences storage size and query performance by creating two tables with varchar(50) and varchar(500), inserting one million rows, measuring disk usage, and benchmarking various queries, ultimately revealing that storage is identical while longer columns can degrade performance during sorted scans due to increased temporary file usage.

Database DesignQuery Optimizationindex
0 likes · 10 min read
Does Varchar Length Really Affect MySQL Storage and Performance?
Baidu Tech Salon
Baidu Tech Salon
Oct 22, 2024 · Big Data

TDE-ClickHouse: Baidu MEG's High-Performance Big Data Analytics Engine

TDE‑ClickHouse, the core engine of Baidu MEG’s Turing 3.0 ecosystem, delivers sub‑second, self‑service analytics on petabyte‑scale data by decoupling compute, adding multi‑level aggregation, high‑cardinality and rule‑based optimizations, a two‑phase bulk‑load pipeline, cloud‑native deployment, and a lightweight meta service, now powering over 350 000 cores, 10 PB storage and more than 150 000 daily BI queries with average response times under three seconds.

ClickHouseDatabase ArchitectureQuery Optimization
0 likes · 19 min read
TDE-ClickHouse: Baidu MEG's High-Performance Big Data Analytics Engine
Code Ape Tech Column
Code Ape Tech Column
Oct 21, 2024 · Big Data

Design and Optimization of Querying 100k Records from Tens of Millions Using ClickHouse, Elasticsearch, HBase, and RediSearch

This article presents a business-driven requirement to extract no more than 100,000 records from a pool of tens of millions, evaluates four technical solutions—including multithreaded ClickHouse pagination, Elasticsearch scroll‑scan, an ES‑HBase hybrid, and RediSearch + RedisJSON—provides implementation details, performance measurements, and practical recommendations for large‑scale data querying.

Big DataHBaseQuery Optimization
0 likes · 11 min read
Design and Optimization of Querying 100k Records from Tens of Millions Using ClickHouse, Elasticsearch, HBase, and RediSearch
Rare Earth Juejin Tech Community
Rare Earth Juejin Tech Community
Oct 19, 2024 · Databases

Understanding the HAVING Clause in SQL: Concepts, Examples, and Best Practices

This article explains the purpose and proper use of the SQL HAVING clause, contrasts it with WHERE, and provides multiple practical examples—including counting groups, detecting missing IDs, calculating mode and median, and filtering fully‑submitted records—while highlighting common pitfalls and performance considerations.

AggregationGROUP BYHAVING
0 likes · 13 min read
Understanding the HAVING Clause in SQL: Concepts, Examples, and Best Practices
Big Data Technology & Architecture
Big Data Technology & Architecture
Oct 16, 2024 · Databases

Kuaishou's Lakehouse‑Integrated OLAP Architecture with Apache Doris: Design, Migration, and Optimization

The article describes how Kuaishou transformed its high‑traffic OLAP system from a separated lake‑and‑warehouse architecture using Hive/Hudi and ClickHouse into a unified lakehouse solution powered by Apache Doris, detailing the challenges, design choices, caching and automatic materialization mechanisms, and the resulting performance and governance improvements.

Apache DorisBig DataData Caching
0 likes · 18 min read
Kuaishou's Lakehouse‑Integrated OLAP Architecture with Apache Doris: Design, Migration, and Optimization
IT Services Circle
IT Services Circle
Oct 1, 2024 · Databases

Effectiveness of Adding an Index on a Status Column in a Tens‑of‑Millions Row MySQL Table

This article explains how adding an index to a status column in a tens‑of‑millions‑row MySQL table affects query performance, covering the basic index lookup process, extreme cases where full scans are chosen, selectivity, covering indexes, composite indexes, partitioning, and using EXPLAIN to verify execution plans.

EXPLAINQuery OptimizationSQL
0 likes · 9 min read
Effectiveness of Adding an Index on a Status Column in a Tens‑of‑Millions Row MySQL Table
dbaplus Community
dbaplus Community
Sep 25, 2024 · Databases

Segment‑wise Train Seat Allocation with SQL: A Practical Guide

The article recounts a personal experience of a failed train ticket change, analyzes segment‑wise seat availability, proposes a database model, and demonstrates a SQL query that allocates seats per segment, showing how such logic could enable full‑journey booking and even inspired a new feature on the 12306 platform.

OracleQuery OptimizationRailway
0 likes · 10 min read
Segment‑wise Train Seat Allocation with SQL: A Practical Guide
Big Data Technology & Architecture
Big Data Technology & Architecture
Sep 18, 2024 · Databases

Doris Performance Optimization: OLAP Query, Indexes, Vectorized Execution, and High‑Concurrency Point Queries

This article explains how Apache Doris achieves high‑concurrency OLAP and point‑query performance through MPP architecture, columnar storage, partition‑bucket pruning, various indexes, materialized views, vectorized execution, runtime filters, short‑circuit planning, and prepared‑statement caching.

DorisHigh concurrencyIndexes
0 likes · 12 min read
Doris Performance Optimization: OLAP Query, Indexes, Vectorized Execution, and High‑Concurrency Point Queries