Skip to content
Tagged articles

Query Optimization

462 articles · Page 2 of 5
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 Optimizationcardinality estimation
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.

Query Optimizationbig datalogical 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 OptimizationUser Privileges
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
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.

Query OptimizationSQLdatabases
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.

OptimizerQuery OptimizationSpark SQL
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.

AutoMVMaxComputeQuery Optimization
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
Top Architecture Tech Stack
Top Architecture Tech Stack
Feb 21, 2025 · Databases

SQL Query Optimization: Sorting, GROUP BY, Pagination, and Index Strategies

This article explores advanced SQL query optimization techniques for large datasets, covering sorting methods, GROUP BY improvements, pagination strategies, covering indexes, and prefix index considerations, with practical examples, EXPLAIN analyses, and recommendations to reduce filesort usage and enhance performance.

IndexingPaginationQuery Optimization
0 likes · 17 min read
SQL Query Optimization: Sorting, GROUP BY, Pagination, and Index Strategies
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.

BIMaterialized ViewQuery 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%.

Data ModelingPerformance OptimizationQuery 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.

IndexQuery 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 PerformancePostgreSQLQuery Optimization
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.

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
Programmer1970
Programmer1970
Nov 29, 2024 · Databases

40 Essential MySQL Interview Questions and Answers

This article compiles 40 core MySQL interview questions covering ACID properties, lock types, views, stored procedures, JOINs, storage engines, query optimization, buffer pools, write buffers, transaction mechanisms, MVCC, isolation levels, gap locks, metadata locks, thread model, row formats, and more, providing concise answers for each.

MVCCQuery OptimizationTransactions
0 likes · 57 min read
40 Essential MySQL Interview Questions and Answers
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.

Materialized ViewQuery OptimizationSQL performance
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.

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

HiveMPPMaterialized View
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.

IndexQuery OptimizationStorage
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.

ClickHouseQuery Optimizationbig data analytics
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.

HBaseQuery OptimizationRediSearch
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.

HAVINGQuery OptimizationSQL
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 DorisData CachingMaterialized View
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.

EXPLAINIndexPartitioning
0 likes · 9 min read
Effectiveness of Adding an Index on a Status Column in a Tens‑of‑Millions Row MySQL Table
Programmer1970
Programmer1970
Sep 26, 2024 · Databases

How MySQL’s Multi-Range Read (MRR) Turns Random I/O into Sequential I/O

The article explains MySQL’s Multi-Range Read (MRR) optimization, detailing how it scans secondary indexes, buffers and sorts primary keys, leverages disk pre‑read and cache locality to convert random I/O into sequential I/O, and shows configuration, monitoring methods, and a concrete orders‑table example.

InnoDBMRRMulti-Range Read
0 likes · 11 min read
How MySQL’s Multi-Range Read (MRR) Turns Random I/O into Sequential I/O
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.

DorisOLAPQuery Optimization
0 likes · 12 min read
Doris Performance Optimization: OLAP Query, Indexes, Vectorized Execution, and High‑Concurrency Point Queries
dbaplus Community
dbaplus Community
Sep 10, 2024 · Databases

Boost MySQL Performance: Essential Tools, Installation & Report Analysis

This guide introduces four popular MySQL performance‑tuning utilities—mysqltuner.pl, tuning‑primer.sh, pt‑variable‑advisor, and pt‑query‑digest—provides download commands, usage examples, and detailed tips for interpreting their diagnostic reports to improve database configuration and query efficiency.

Database ToolsPerformance TuningQuery Optimization
0 likes · 8 min read
Boost MySQL Performance: Essential Tools, Installation & Report Analysis
ITPUB
ITPUB
Sep 3, 2024 · Backend Development

Boost Query Speed from 20 s to 0.5 s with MQ‑Driven Elasticsearch Separation (Java)

A large table with tens of millions of rows caused 20‑second query times, but by decoupling reads through RabbitMQ and writing data asynchronously to Elasticsearch, query latency dropped to 500 ms, with Java code examples for both producer and consumer, sync handling, and migration tips.

ElasticsearchJavaQuery Optimization
0 likes · 6 min read
Boost Query Speed from 20 s to 0.5 s with MQ‑Driven Elasticsearch Separation (Java)
Java Architect Essentials
Java Architect Essentials
Aug 9, 2024 · Databases

Comprehensive MySQL Query and Configuration Optimization Guide

This article provides a thorough guide to MySQL performance optimization, covering general query improvements, proper data types, index usage, configuration parameter tuning, avoiding common pitfalls, and detailed EXPLAIN analysis, with concrete SQL examples and practical recommendations for developers and DBAs.

Database ConfigurationIndexingPerformance Tuning
0 likes · 16 min read
Comprehensive MySQL Query and Configuration Optimization Guide
Bilibili Tech
Bilibili Tech
Aug 9, 2024 · Operations

Design and Optimization of Monitoring 2.0 Architecture with VictoriaMetrics and Flink

The new Monitoring 2.0 architecture separates collection, compute and storage, adopts VictoriaMetrics for compact time‑series storage and a zone‑based scheduler, introduces push‑based ingestion, uses Flink for real‑time pre‑aggregation and automatic PromQL rewrite, delivering ten‑fold query speedups, sub‑300 ms p90 latency, and dramatically higher write and query throughput.

FlinkMetricsPrometheus
0 likes · 29 min read
Design and Optimization of Monitoring 2.0 Architecture with VictoriaMetrics and Flink
Java Captain
Java Captain
Jul 29, 2024 · Information Security

How to Perform Fuzzy Queries on Encrypted Data

This article examines the challenges of fuzzy searching encrypted data and compares three categories of solutions—naïve, conventional, and advanced—detailing their implementation ideas, performance trade‑offs, storage costs, and security implications for real‑world applications.

AlgorithmQuery Optimizationencryption
0 likes · 10 min read
How to Perform Fuzzy Queries on Encrypted Data
Java Interview Crash Guide
Java Interview Crash Guide
Jul 24, 2024 · Databases

How to Supercharge MySQL Queries: 8 Proven Optimization Techniques

Learn eight practical MySQL optimization strategies—including smarter LIMIT usage, avoiding implicit type conversion, rewriting subqueries with JOINs, handling mixed sorting, eliminating costly EXISTS clauses, pushing conditions, pre‑filtering data, and leveraging intermediate result sets—to dramatically reduce query execution time from seconds to milliseconds.

IndexingLIMITQuery Optimization
0 likes · 15 min read
How to Supercharge MySQL Queries: 8 Proven Optimization Techniques
Selected Java Interview Questions
Selected Java Interview Questions
Jul 16, 2024 · Databases

SQL Query Optimization Techniques

This article explains how to speed up SQL queries while keeping statements concise, covering the query processing workflow, optimization goals, and practical tips such as selecting specific columns, avoiding DISTINCT, using proper indexes, preferring EXISTS over COUNT, limiting result sets, favoring WHERE over HAVING, and replacing correlated subqueries with joins.

Query OptimizationSQLdatabase
0 likes · 9 min read
SQL Query Optimization Techniques
Sohu Tech Products
Sohu Tech Products
Jul 10, 2024 · Industry Insights

How StarRocks and Apache Paimon Transform Data Lake Analytics and Migration

This article provides a practical deep‑dive into StarRocks and Apache Paimon, covering data‑lake fundamentals, the technical advantages of both platforms, performance gains over traditional engines, step‑by‑step migration strategies, deployment options on Alibaba Cloud EMR, and future roadmap plans.

Apache PaimonQuery OptimizationStarRocks
0 likes · 15 min read
How StarRocks and Apache Paimon Transform Data Lake Analytics and Migration
Java Tech Enthusiast
Java Tech Enthusiast
Jul 7, 2024 · Databases

MySQL Query Optimization and Performance Tuning Guide

This guide presents a thorough MySQL performance checklist, covering efficient SQL writing, data‑type choices, selective column retrieval, smart joins, batch inserts, and query‑cache settings; it details key InnoDB and server variable tuning, proper index creation and usage, common pitfalls, and how to analyze and improve queries with EXPLAIN.

Query Optimizationdatabasemysql
0 likes · 17 min read
MySQL Query Optimization and Performance Tuning Guide
Tencent Cloud Developer
Tencent Cloud Developer
Jul 5, 2024 · Databases

Database Systems Reading List: Fundamentals, Design, Engines, and Optimizations

This curated bibliography compiles seminal database‑system papers—spanning foundational relational theory, system design for RDBMS and NoSQL, SQL engine optimization, storage structures, transaction and scheduling mechanisms, plus workload, networking, quality, and tuning research—each entry listing title, authors, year and a PDF link for scholars and practitioners.

Query Optimizationdatabase systemsreading list
0 likes · 25 min read
Database Systems Reading List: Fundamentals, Design, Engines, and Optimizations
Python Programming Learning Circle
Python Programming Learning Circle
Jun 11, 2024 · Databases

SQL Best Practices: Avoid Common Mistakes and Write Efficient Queries

This article presents a reverse‑analysis of common SQL pitfalls—such as ambiguous column names, selecting all columns, unnecessary joins, missing WHERE clauses, lack of ORDER BY, unclear aliases, absent error handling, redundant subqueries, and missing comments—and provides clear "Don’t" and "Do" examples with code to help you write clean, optimized, and reliable SQL statements.

Best PracticesQuery Optimizationerror handling
0 likes · 7 min read
SQL Best Practices: Avoid Common Mistakes and Write Efficient Queries
Huawei Cloud Developer Alliance
Huawei Cloud Developer Alliance
Jun 3, 2024 · Databases

Master OpenGemini: From Schema Design to Performance Tuning Best Practices

This article summarizes a live session where Shawn explains how understanding business scenarios drives effective OpenGemini database design, and provides comprehensive best‑practice guidance on library and table design, data ingestion, query optimization, and performance tuning for time‑series workloads.

Data IngestionPerformance TuningQuery Optimization
0 likes · 7 min read
Master OpenGemini: From Schema Design to Performance Tuning Best Practices
Top Architect
Top Architect
May 24, 2024 · Databases

How to Perform Fuzzy Search on Encrypted Data

This article examines various techniques for enabling fuzzy queries on encrypted fields, comparing naive memory‑based methods, conventional token‑based approaches that leverage database indexes, and advanced cryptographic schemes, and recommends practical solutions for real‑world applications.

Database EncryptionQuery Optimizationencrypted data
0 likes · 12 min read
How to Perform Fuzzy Search on Encrypted Data
Lobster Programming
Lobster Programming
Apr 28, 2024 · Databases

How to Solve MySQL Deep Pagination: Causes and 6 Effective Optimization Strategies

Deep pagination in MySQL becomes a performance bottleneck when tables reach millions of rows, causing excessive row scans and costly index lookups; this article explains its root causes and presents six practical optimization techniques—including keyset pagination, delayed joins, covering indexes, data partitioning, and external search engines—to dramatically improve query speed.

Covering IndexDeep PaginationIndexing
0 likes · 7 min read
How to Solve MySQL Deep Pagination: Causes and 6 Effective Optimization Strategies
StarRocks
StarRocks
Apr 18, 2024 · Databases

Master StarRocks Deployment and Optimization: From Capacity Planning to Query Tuning

This comprehensive guide walks you through StarRocks production deployment, covering capacity planning, hardware specs, environment setup, table modeling, partitioning, bucketing, index selection, data import best practices, query optimization, monitoring, and resource‑group configuration, all backed by concrete SQL examples and configuration commands.

Query OptimizationStarRocksdeployment
0 likes · 20 min read
Master StarRocks Deployment and Optimization: From Capacity Planning to Query Tuning
ITPUB
ITPUB
Apr 11, 2024 · Big Data

Query 100K Items from 10M+ Records: CK, ES Scroll, HBase, RediSearch

When faced with a business requirement to filter up to 100 000 records from a pool of tens of millions and then sort and de‑duplicate them, this article explores four technical solutions—multithreaded ClickHouse pagination, Elasticsearch scroll‑scan, a combined Elasticsearch‑HBase approach, and RediSearch with RedisJSON—detailing their design, implementation, performance testing, and trade‑offs.

ClickHouseElasticsearchHBase
0 likes · 12 min read
Query 100K Items from 10M+ Records: CK, ES Scroll, HBase, RediSearch
dbaplus Community
dbaplus Community
Apr 10, 2024 · Databases

How MySQL Chooses the Cheapest Index for COUNT(*) and When It Gets It Wrong

This article examines whether SELECT COUNT(*) causes full‑table scans, explains MySQL’s optimizer cost‑based index selection (including IO and CPU costs), demonstrates with a 100k‑row table how auxiliary indexes are chosen, and shows cases where the optimizer’s estimates mislead performance.

COUNTIndex CostQuery Optimization
0 likes · 9 min read
How MySQL Chooses the Cheapest Index for COUNT(*) and When It Gets It Wrong
Baidu Geek Talk
Baidu Geek Talk
Apr 10, 2024 · Big Data

TDA: A One‑Stop Self‑Service BI Platform – Architecture, Challenges, and Solutions

The article presents Turing Data Analysis (TDA), a self‑service BI platform that replaces fragile traditional pipelines with a unified DWD‑based data model, drag‑and‑drop analytics, multi‑engine query optimization and caching, delivering sub‑10‑second queries on billions of rows, fine‑grained permissions, and rapid dashboard creation, while reporting significant usage growth and outlining AI‑driven future enhancements.

BIMPPQuery Optimization
0 likes · 15 min read
TDA: A One‑Stop Self‑Service BI Platform – Architecture, Challenges, and Solutions
DataFunTalk
DataFunTalk
Mar 16, 2024 · Big Data

Performance Optimization Practices for KwaiBI Big Data Analysis Platform

This article introduces KwaiBI, the internal data analysis product of Kuaishou, outlines its five major functional areas, details the performance challenges of large‑scale analytics, and presents a comprehensive set of optimization techniques—including cache warming, query rewriting, materialized acceleration, and the Bleem lake‑house engine—along with future directions and a brief Q&A.

KwaiBIPerformance OptimizationQuery Optimization
0 likes · 15 min read
Performance Optimization Practices for KwaiBI Big Data Analysis Platform
Java Tech Enthusiast
Java Tech Enthusiast
Mar 10, 2024 · Databases

Why IN and NOT IN Should Be Avoided in SQL Queries

Because IN and NOT IN often bypass indexes on large tables, can yield incorrect results when column names differ or NULL values are present, and may cause severe performance penalties, they should be replaced with EXISTS/NOT EXISTS subqueries or appropriate JOINs, reserving IN only for small literal lists.

IN ClauseNOT INQuery Optimization
0 likes · 4 min read
Why IN and NOT IN Should Be Avoided in SQL Queries
MaGe Linux Operations
MaGe Linux Operations
Feb 12, 2024 · Databases

Why Hash Join Beats Nested Loop Join and When It Fails

This article explains why hash joins usually outperform nested‑loop joins, how to force hash joins in SQL, the data‑type restrictions that prevent hash joins, and practical tips for optimizing join performance in TD and Oracle compatibility modes.

Hash JoinNested LoopQuery Optimization
0 likes · 6 min read
Why Hash Join Beats Nested Loop Join and When It Fails
Sohu Tech Products
Sohu Tech Products
Jan 24, 2024 · Databases

Optimizing Database Expression Evaluation with JIT Technology Using Gandiva

The article explains how database expression evaluation—especially in WHERE and SELECT clauses—can be dramatically accelerated by replacing interpreted AST traversal with Just‑In‑Time compilation using Apache Gandiva, which leverages LLVM to generate SIMD‑optimized machine code for Arrow columnar data, and discusses extensions such as timestamp, array, higher‑order functions, and UDF support.

Apache ArrowApache GandivaExpression Evaluation
0 likes · 17 min read
Optimizing Database Expression Evaluation with JIT Technology Using Gandiva
ITPUB
ITPUB
Jan 8, 2024 · Databases

How I Cut an 8‑Second SQL Query to 0.7 Seconds: Step‑by‑Step Optimization

This article walks through a real‑world case of a slow MySQL query that took 8 seconds, explains how the original use of EXISTS and NOT EXISTS caused performance bottlenecks, and details two successive rewrites—first using JOIN + GROUP BY, then replacing NOT EXISTS with NOT IN—bringing execution time down to 0.7 seconds while highlighting key indexing and testing lessons.

Performance TuningQuery OptimizationSQL
0 likes · 7 min read
How I Cut an 8‑Second SQL Query to 0.7 Seconds: Step‑by‑Step Optimization
DeWu Technology
DeWu Technology
Dec 18, 2023 · Databases

Elasticsearch Best Practices: Query, Index, and Performance Optimizations

The guide outlines production‑ready Elasticsearch best practices, covering query tuning such as using shard request cache, filter context, size‑0 aggregations and composite aggregations; write strategies like auto‑generated IDs, bulk API sizing and refresh handling; optimal shard counts, explicit mappings with disabled unnecessary features, and general advice to use explicit index names and stored scripts.

ElasticsearchIndexingQuery Optimization
0 likes · 22 min read
Elasticsearch Best Practices: Query, Index, and Performance Optimizations
Architecture Digest
Architecture Digest
Dec 6, 2023 · Databases

MySQL Query Optimization and Index Tuning: From Subqueries to Joins

This article analyzes a slow MySQL query that scans millions of rows to find students scoring 100 in Chinese, then demonstrates how adding appropriate single‑column and composite indexes, rewriting the query as a join, and using covering indexes can reduce execution time from hours to milliseconds.

Performance TuningQuery OptimizationSQL
0 likes · 10 min read
MySQL Query Optimization and Index Tuning: From Subqueries to Joins
dbaplus Community
dbaplus Community
Dec 5, 2023 · Databases

How I Cut an 8‑Second Slow Query to 0.7 Seconds with Two Optimizations

The article walks through a real‑world case of a slow MySQL query that took 8 seconds, explains why simple indexing wasn't enough, and demonstrates two successive rewrites using JOIN‑GROUP BY and NOT IN that reduced execution time to 0.7 seconds while detailing the EXPLAIN analysis.

EXPLAINPerformance TuningQuery Optimization
0 likes · 6 min read
How I Cut an 8‑Second Slow Query to 0.7 Seconds with Two Optimizations
JD Cloud Developers
JD Cloud Developers
Nov 14, 2023 · Databases

How MySQL Executes a Simple SQL Query: Step‑by‑Step Deep Dive

This article walks through the complete lifecycle of a MySQL query—from the client request, through connection handling, parsing, optimization, execution, and storage engine processing—illustrating each component with diagrams, code snippets, and a detailed analysis of execution order for both simple and join queries.

Database InternalsQuery OptimizationSQL execution
0 likes · 12 min read
How MySQL Executes a Simple SQL Query: Step‑by‑Step Deep Dive
Java Architect Essentials
Java Architect Essentials
Nov 6, 2023 · Databases

Understanding MySQL InnoDB Full-Text Search and Inverted Indexes

This article explains why full‑text search is needed, how InnoDB implements it with inverted indexes, the syntax for creating and using full‑text indexes, the different query modes (natural language, boolean, query expansion), and how to delete full‑text indexes in MySQL.

Inverted IndexQuery OptimizationSQL
0 likes · 10 min read
Understanding MySQL InnoDB Full-Text Search and Inverted Indexes
ITPUB
ITPUB
Nov 1, 2023 · Databases

Doris 2.0.2 vs 1.2.3: Real‑World Query Performance Comparison

After upgrading a Doris cluster from version 1.2.3 to 2.0.2, the author runs a series of SQL benchmarks—including PK lookups, top‑client queries, distinct counts on low‑ and high‑cardinality columns, minute‑level session analysis, and full‑table deduplication—to measure execution times, revealing mixed performance gains and regressions across the seven test scenarios.

Database UpgradeDorisQuery Optimization
0 likes · 9 min read
Doris 2.0.2 vs 1.2.3: Real‑World Query Performance Comparison
Java Architect Essentials
Java Architect Essentials
Oct 17, 2023 · Databases

Understanding LEFT JOIN ON vs WHERE Conditions in SQL

This article explains how LEFT JOIN works in SQL, clarifying the difference between ON‑clause conditions that affect the join process and WHERE‑clause filters that operate on the resulting temporary table, using multiple example queries and visual illustrations.

LEFT JOINON clauseQuery Optimization
0 likes · 5 min read
Understanding LEFT JOIN ON vs WHERE Conditions in SQL
Su San Talks Tech
Su San Talks Tech
Oct 17, 2023 · Databases

Why MySQL LIMIT + ORDER BY Returns Unexpected Rows – Priority Queue Threshold

This article investigates the nondeterministic behavior of MySQL when combining ORDER BY with LIMIT, reproduces the official example, identifies a critical row count threshold where the optimizer switches to a priority‑queue (heap) sort, and explains the underlying source‑code logic and its impact on query results.

ORDER BYQuery Optimizationheap sort
0 likes · 19 min read
Why MySQL LIMIT + ORDER BY Returns Unexpected Rows – Priority Queue Threshold
ITPUB
ITPUB
Sep 28, 2023 · Databases

Mastering MySQL Index Merge: How Three Algorithms Boost Query Performance

Learn how MySQL's index merge optimization works, explore its three algorithms—intersect, union, and sort_union—understand when each applies, see practical setup and sample queries, and discover configuration flags and best‑practice tips to improve query efficiency.

Query Optimizationdatabaseindex_merge
0 likes · 9 min read
Mastering MySQL Index Merge: How Three Algorithms Boost Query Performance
DataFunSummit
DataFunSummit
Sep 25, 2023 · Big Data

Trino in Bilibili Lakehouse: Compute Engine, Stability, and Containerization Practices

This article presents Bilibili's practical implementation of Trino within a lakehouse architecture, focusing on the compute engine placement, stability enhancements, and containerized deployment, while detailing indexing strategies, pre‑computation techniques, Iceberg metadata optimizations, and performance gains for large‑scale analytical queries.

ContainerizationIcebergIndexing
0 likes · 14 min read
Trino in Bilibili Lakehouse: Compute Engine, Stability, and Containerization Practices
JD Retail Technology
JD Retail Technology
Sep 13, 2023 · Databases

Optimizing Pagination Queries for Billion‑Row MySQL Tables

The article analyzes the performance problems of deep pagination on massive MySQL tables storing billions of fan records and presents three progressive solutions—simple LIMIT, tag‑record (maxId) pagination, and range‑limited pagination with async and offline minId caching—along with general indexing best‑practices for high‑throughput queries.

IndexingLarge DataQuery Optimization
0 likes · 10 min read
Optimizing Pagination Queries for Billion‑Row MySQL Tables
Liangxu Linux
Liangxu Linux
Sep 10, 2023 · Databases

Master MySQL Query Optimization: Architecture, Caching, and Index Strategies

This article explains MySQL's logical architecture, query execution flow, caching mechanisms, and detailed index design techniques, providing practical tips and examples so developers can understand the underlying principles and apply effective performance optimizations in real‑world applications.

Database PerformanceQuery OptimizationSQL
0 likes · 38 min read
Master MySQL Query Optimization: Architecture, Caching, and Index Strategies
Ximalaya Technology Team
Ximalaya Technology Team
Sep 6, 2023 · Backend Development

Design Analysis of Lucene and In-Memory Inverted Index Service for Advertising Retrieval

The team analyzed Lucene’s disk‑based inverted index and built a custom in‑memory inverted‑index service for Himalaya’s ad engine, encoding terms as 64‑bit keys, supporting real‑time updates and BooleanQuery‑style and custom expression filtering, which cut query latency from ~50 ms to under 5 ms and enabled massive scaling.

Data StructuresInverted IndexJava
0 likes · 27 min read
Design Analysis of Lucene and In-Memory Inverted Index Service for Advertising Retrieval
MaGe Linux Operations
MaGe Linux Operations
Aug 30, 2023 · Databases

Understanding SQL Execution Order: From FROM to LIMIT Explained

This article walks through the exact sequence a database follows when executing a SQL query—starting with FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and finally LIMIT—highlighting key differences from the textual order of the statement.

Query OptimizationSQLSQL Tutorial
0 likes · 5 min read
Understanding SQL Execution Order: From FROM to LIMIT Explained
Full-Stack Internet Architecture
Full-Stack Internet Architecture
Aug 16, 2023 · Databases

Common Cases When MySQL Indexes Are Ignored

This article explains how to set up a MySQL test table and indexes, then enumerates nine typical scenarios—such as leading‑wildcard LIKE, arithmetic or functions on indexed columns, type mismatches, composite‑index misuse, character‑set differences, and OR conditions with non‑indexed fields—that cause queries to bypass indexes and perform full table scans.

IndexQuery OptimizationSQL
0 likes · 8 min read
Common Cases When MySQL Indexes Are Ignored
Java Architect Essentials
Java Architect Essentials
Aug 13, 2023 · Information Security

How to Perform Fuzzy Searches on Encrypted Data Without Breaking Security

This article examines three categories of approaches—naïve, conventional, and advanced—for enabling fuzzy queries on encrypted fields, comparing their implementation steps, performance trade‑offs, storage costs, and security implications, and provides practical examples such as in‑memory decryption, tag mapping, database functions, tokenization, and algorithm‑level designs.

Database SecurityQuery Optimizationencrypted data
0 likes · 11 min read
How to Perform Fuzzy Searches on Encrypted Data Without Breaking Security
dbaplus Community
dbaplus Community
Aug 13, 2023 · Databases

Why Does This PostgreSQL Self‑Join Behave Unexpectedly? InitPlan & One‑Time Filter

The article examines a puzzling PostgreSQL self‑join query that yields surprising true/false results, walks through step‑by‑step rewrites, analyzes the resulting execution plans—including the appearance of InitPlan and One‑Time Filter nodes—and explains how correlated versus uncorrelated subqueries affect query evaluation.

InitPlanPostgreSQLQuery Optimization
0 likes · 11 min read
Why Does This PostgreSQL Self‑Join Behave Unexpectedly? InitPlan & One‑Time Filter
Top Architect
Top Architect
Aug 8, 2023 · Databases

MySQL Pagination Query Optimization Techniques and Performance Testing

The article examines various MySQL pagination strategies—including simple LIMIT clauses, sub‑query approaches, ID‑range filtering, and temporary‑table methods—provides performance measurements on a table with millions of rows, and offers practical recommendations for reducing query latency in large datasets.

PaginationQuery Optimizationdatabase
0 likes · 9 min read
MySQL Pagination Query Optimization Techniques and Performance Testing
DataFunTalk
DataFunTalk
Jul 30, 2023 · Databases

MatrixOne Architecture and OLAP Engine Design Overview

This article presents an in‑depth overview of MatrixOne, an open‑source hyper‑converged cloud‑native database, detailing its three‑tier architecture of compute, data and file services, and explains the design and implementation of its OLAP engine, including parser, planner, optimizer, and push‑based execution pipeline.

Execution EngineMatrixOneOLAP
0 likes · 18 min read
MatrixOne Architecture and OLAP Engine Design Overview
21CTO
21CTO
Jul 17, 2023 · Big Data

How WeChat Cut Query Latency from Seconds to 100 ms with Druid Optimizations

This case study explains how the WeChat multi‑dimensional monitoring platform identified performance bottlenecks in its Druid‑based data layer, analyzed user query patterns, and applied sub‑query splitting, Redis caching, and segment size reductions to achieve over 85% cache‑hit rates and bring average query latency down to around 100 ms.

DruidQuery Optimizationbig data
0 likes · 13 min read
How WeChat Cut Query Latency from Seconds to 100 ms with Druid Optimizations
Su San Talks Tech
Su San Talks Tech
Jul 8, 2023 · Databases

Master MySQL Index Optimization: 20 Proven Rules to Boost Query Performance

This comprehensive guide explains 20 practical MySQL index optimization rules—including when NOT to use indexes, how to choose the right index type, and techniques such as prefix indexes, covering indexes, and query rewriting—to dramatically improve SQL query efficiency and reduce database load.

Query OptimizationSQL performancedatabase tuning
0 likes · 19 min read
Master MySQL Index Optimization: 20 Proven Rules to Boost Query Performance