BestHub
Sign in
Tagged articles

query performance

85 articles · Page 1 of 1
Linux Tech Enthusiast
Linux Tech Enthusiast
Sep 28, 2026 · Databases

8 SQL Anti-Patterns That Kill Performance (and How to Fix Them)

This article details eight common SQL anti-patterns — including OFFSET pagination, implicit type conversion, correlated subqueries in updates, mixed sorting, EXISTS clauses, blocked condition pushdown, late filtering, and unoptimized intermediate results — with execution plans and rewrites that reduce query times from seconds to milliseconds.

CTEMySQLSQL optimization
0 likes · 16 min read
8 SQL Anti-Patterns That Kill Performance (and How to Fix Them)
dbaplus Community
dbaplus Community
Sep 26, 2026 · Databases

PostgreSQL JSON vs JSONB: 1M Row Benchmark Shows 7x Query Speed Gap

This article benchmarks PostgreSQL JSON and JSONB types on 1 million rows, revealing JSONB delivers 6-9x faster queries and 26% less storage despite 31% slower writes, with practical guidance on when to choose each type and how to leverage GIN indexes for optimal performance.

Data TypesDatabase BenchmarkGIN Index
0 likes · 20 min read
PostgreSQL JSON vs JSONB: 1M Row Benchmark Shows 7x Query Speed Gap
Java Captain
Java Captain
Sep 24, 2026 · Databases

7 Common MySQL Index Failure Scenarios and How to Fix Them

This article details seven common MySQL index failure scenarios—including leftmost prefix violations, function usage on indexed columns, implicit type conversion, leading wildcards in LIKE, OR with non-indexed columns, IS NOT NULL, and NOT IN/EXISTS—with concrete SQL examples showing both failing and optimized queries.

Composite IndexIS NULLImplicit Type Conversion
0 likes · 6 min read
7 Common MySQL Index Failure Scenarios and How to Fix Them
Lobster Programming
Lobster Programming
Sep 14, 2026 · Databases

Three-Field Schema for Encrypted Phone Number Queries by Last Four Digits

The article analyzes flawed approaches for querying encrypted phone numbers by last four digits — memory decryption, ciphertext fuzzy matching, partial encryption, and mapping tables — and presents a three-field design (full ciphertext, masked display, indexed last-four plaintext) that ensures compliance, security, and millisecond query performance at million-user scale.

AES-256SM4compliance
0 likes · 6 min read
Three-Field Schema for Encrypted Phone Number Queries by Last Four Digits
Lakehouse Research Base
Lakehouse Research Base
Aug 25, 2026 · Big Data

StarRocks on Paimon: Morning Fast, Daytime Slow - Root Cause & Layered Optimization

This article analyzes why identical StarRocks queries on Paimon external tables run fast in early morning but slow dramatically during daytime peaks, identifying five layered root causes from file fragmentation to storage pressure, and provides a systematic troubleshooting methodology plus a four-layer optimization framework covering source governance, compute caching, storage scaling, and business scheduling.

Delete VectorLakehousePaimon
0 likes · 21 min read
StarRocks on Paimon: Morning Fast, Daytime Slow - Root Cause & Layered Optimization
Architect's Guide
Architect's Guide
May 23, 2026 · Databases

SQL Optimization: Cutting Query Time from 30,248 s to 0.001 s

This article walks through a MySQL 5.6 case study where a sub‑query that originally took over eight hours is accelerated to a few milliseconds by analyzing the execution plan, adding single‑column and composite indexes, rewriting the query as EXISTS or JOIN, and applying best‑practice indexing techniques.

IndexingJOINMySQL
0 likes · 13 min read
SQL Optimization: Cutting Query Time from 30,248 s to 0.001 s
Architect's Guide
Architect's Guide
May 22, 2026 · Databases

How Indexes Speed Up Database Queries: A Clear Explanation

The article explains how database indexes work by comparing them to a book’s table of contents, describes underlying storage mechanisms, demonstrates binary search on sorted records, and discusses the benefits, trade‑offs, and common pitfalls of using clustered and non‑clustered indexes.

Database IndexesSQL optimizationbinary search
0 likes · 12 min read
How Indexes Speed Up Database Queries: A Clear Explanation
ITPUB
ITPUB
Nov 22, 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 why the default ON setting can hurt performance with skewed data, and demonstrates with code and EXPLAIN output how turning it OFF can lead to faster queries for ORDER BY and GROUP BY scenarios.

MySQLindex usageoptimizer_switch
0 likes · 7 min read
When Should You Turn Off MySQL’s prefer_ordering_index? A Deep Dive
Big Data Technology & Architecture
Big Data Technology & Architecture
Oct 13, 2025 · Databases

Apache Doris 3.1 Unveiled: Variant, Index, and Lakehouse Boosts

The Apache Doris 3.1 release strengthens lake‑house capabilities with major upgrades to the VARIANT data type, vertical compaction, inverted index storage, new tokenizers, enhanced materialized view support for Iceberg/Paimon/Hudi, and numerous query‑performance optimizations such as faster partition pruning and dynamic partition clipping, offering smoother handling of thousands of columns and large‑scale semi‑structured data.

Apache DorisLakehouseVariant
0 likes · 8 min read
Apache Doris 3.1 Unveiled: Variant, Index, and Lakehouse Boosts
Big Data Tech Team
Big Data Tech Team
Oct 10, 2025 · Big Data

12 Essential Hive SQL Optimization Tricks to Boost Query Performance

This article presents twelve practical Hive SQL tuning techniques—ranging from avoiding COUNT(DISTINCT) to configuring parallel execution, reducer settings, and strict mode—to help data engineers reduce data skew, eliminate small files, improve resource utilization, and significantly accelerate query execution in large‑scale data warehouse environments.

HiveSQL optimizationdata warehouse
0 likes · 11 min read
12 Essential Hive SQL Optimization Tricks to Boost Query Performance
Raymond Ops
Raymond Ops
Oct 6, 2025 · Databases

Beyond CRUD: Unlock Hidden MySQL Features and Optimization Tricks

This article dives deep into MySQL beyond basic CRUD, covering remote user creation, permission grants, communication protocols like TCP/IP, named pipes, shared memory, Unix sockets, and advanced query optimization techniques including index usage, ORDER BY, and LIMIT offset strategies.

Database OptimizationMySQLRemote Connection
0 likes · 16 min read
Beyond CRUD: Unlock Hidden MySQL Features and Optimization Tricks
Architect
Architect
Sep 24, 2025 · Databases

How Indexes Can Speed Up MySQL Queries by 30,000×: A Practical Guide

This article walks through a MySQL 5.6 scenario with three tables, demonstrates why a sub‑query that scans millions of rows runs for hours, and shows step‑by‑step how adding single‑column, composite, and covering indexes, as well as rewriting the query as a join, reduces execution time from over eight hours to a few milliseconds.

IndexingMySQLSQL optimization
0 likes · 12 min read
How Indexes Can Speed Up MySQL Queries by 30,000×: A Practical Guide
Su San Talks Tech
Su San Talks Tech
May 27, 2025 · Databases

Why MySQL Picks the Wrong Index and How to Fix It

Learn how MySQL’s optimizer decides between indexes, why the same query can suddenly use a different index with dramatically slower performance, and practical steps—including analyzing execution plans, updating statistics, checking index coverage, and rebuilding fragmented indexes—to diagnose and resolve index selection issues.

Index OptimizationMySQLdatabase tuning
0 likes · 9 min read
Why MySQL Picks the Wrong Index and How to Fix It
Top Architecture Tech Stack
Top Architecture Tech Stack
May 10, 2025 · Databases

MySQL Query Optimization Guidelines and Best Practices

This article presents a comprehensive set of MySQL optimization techniques, covering the avoidance of SELECT *, proper use of specific fields, index-friendly query patterns, join strategies, handling of NULL and default values, efficient use of UNION, batch inserts, and numerous other performance‑enhancing tips for database developers.

MySQLSQL optimizationdatabase tuning
0 likes · 17 min read
MySQL Query Optimization Guidelines and Best Practices
Java Architect Essentials
Java Architect Essentials
Apr 7, 2025 · Databases

Practical MySQL Query Optimizations: LIMIT, Implicit Conversion, Join Rewrite, Mixed Sorting, EXISTS, Predicate Push‑down, Early Row Limiting, and Intermediate Result Push‑down

This article presents a series of MySQL performance‑tuning techniques—including smarter LIMIT usage, avoiding implicit type conversion, rewriting joins, handling mixed ordering, replacing EXISTS with joins, pushing predicates into subqueries, early row limiting, and using WITH clauses—to dramatically reduce query execution time across common scenarios.

JOINMySQLPredicate Pushdown
0 likes · 13 min read
Practical MySQL Query Optimizations: LIMIT, Implicit Conversion, Join Rewrite, Mixed Sorting, EXISTS, Predicate Push‑down, Early Row Limiting, and Intermediate Result Push‑down
Cognitive Technology Team
Cognitive Technology Team
Mar 29, 2025 · Databases

MySQL Multi-Range Read (MRR) Optimization: Advantages, Limitations, Use Cases, and Configuration

MySQL's Multi-Range Read (MRR) optimization reduces random disk I/O by scanning indexes, sorting keys, and retrieving rows sequentially, offering advantages like sequential data access and batch key processing, while outlining its limitations, applicable scenarios, usage conditions, and configuration via optimizer_switch variables and buffer settings.

Index OptimizationMRRMySQL
0 likes · 5 min read
MySQL Multi-Range Read (MRR) Optimization: Advantages, Limitations, Use Cases, and Configuration
Java Tech Enthusiast
Java Tech Enthusiast
Mar 23, 2025 · Databases

MySQL Query Optimization Techniques: LIMIT, Implicit Conversion, Joins and More

The article shows how common MySQL performance pitfalls—such as large LIMIT offsets, implicit type conversions, sub‑query‑based updates or deletes, mixed ORDER BY, EXISTS clauses, and misplaced predicates—can be rewritten into index‑friendly forms using filters, joins, early limits, UNION ALL, and CTEs to achieve execution times that drop from seconds to milliseconds.

JOINLIMITMySQL
0 likes · 14 min read
MySQL Query Optimization Techniques: LIMIT, Implicit Conversion, Joins and More
JD Tech
JD Tech
Dec 26, 2024 · Databases

Optimizing Query Performance for JD's BIP Procurement System with JED, JimKV, and Elasticsearch

This article details how JD's BIP procurement system tackled massive query‑performance challenges by segmenting order data, leveraging the JED distributed MySQL solution, introducing JimKV for hot‑data caching, and offloading complex searches to Elasticsearch, resulting in dramatically reduced load and faster user experiences.

Database OptimizationElasticsearchJD.com
0 likes · 11 min read
Optimizing Query Performance for JD's BIP Procurement System with JED, JimKV, and Elasticsearch
JD Retail Technology
JD Retail Technology
Dec 12, 2024 · Databases

Optimizing Query Performance for JD's BIP Procurement System with JED, JimKV, and Elasticsearch

This article presents a comprehensive case study of how JD's procurement system (BIP) tackled massive data volume and complex query challenges by redesigning data models, introducing heterogeneous storage for inbound orders, leveraging JED and JimKV, and offloading complex searches to Elasticsearch, resulting in dramatically reduced database load and improved user experience.

Database OptimizationElasticsearchJD supply chain
0 likes · 11 min read
Optimizing Query Performance for JD's BIP Procurement System with JED, JimKV, and Elasticsearch
Baidu Geek Talk
Baidu Geek Talk
Oct 21, 2024 · Databases

TDE-ClickHouse Optimization Practice at Baidu MEG: Query Performance, Data Import, and Distributed Architecture

Baidu MEG’s TDE‑ClickHouse optimization in the Turing 3.0 ecosystem boosts query speed up to 10×, halves latency, enables billion‑row bulk imports in under two hours, and migrates to a cloud‑native, ZooKeeper‑free architecture supporting 350 k CPU cores, 10 PB storage, and sub‑3‑second responses for 150 k daily BI queries.

Baidu MEGClickHouseDatabase Optimization
0 likes · 19 min read
TDE-ClickHouse Optimization Practice at Baidu MEG: Query Performance, Data Import, and Distributed Architecture
Architect's Tech Stack
Architect's Tech Stack
Oct 15, 2024 · Databases

MySQL Query Optimization: LIMIT, Implicit Conversion, Join Rewrite, Mixed Sorting, EXISTS, Predicate Pushdown, Early Limiting, and Intermediate Result Pushdown

This article presents practical MySQL performance tuning methods, covering LIMIT pagination, implicit type conversion pitfalls, rewriting joins for updates/deletes, mixed-order sorting, replacing EXISTS with joins, predicate pushdown, early result limiting, and intermediate result pushdown, each illustrated with SQL examples and execution plan analyses.

MySQLPredicate PushdownSQL optimization
0 likes · 13 min read
MySQL Query Optimization: LIMIT, Implicit Conversion, Join Rewrite, Mixed Sorting, EXISTS, Predicate Pushdown, Early Limiting, and Intermediate Result Pushdown
dbaplus Community
dbaplus Community
Aug 14, 2024 · Databases

When to Use DISTINCT vs GROUP BY in MySQL: Performance Insights

Both DISTINCT and GROUP BY can use indexes when available, giving similar performance, but without indexes DISTINCT is usually faster because GROUP BY may invoke implicit sorting; MySQL 8.0 removed this sorting, making their performance nearly equal, and GROUP BY offers clearer semantics and more flexibility for complex queries.

DISTINCTGROUP BYIndex Optimization
0 likes · 9 min read
When to Use DISTINCT vs GROUP BY in MySQL: Performance Insights
The Dominant Programmer
The Dominant Programmer
Jul 19, 2024 · Databases

Writing High‑Quality SQL: Practical MySQL Optimization Tips

To avoid common MySQL pitfalls, the article compiles practical SQL writing guidelines—selecting specific columns instead of *, using LIMIT 1, avoiding OR in WHERE, optimizing pagination, handling LIKE patterns, minimizing redundant indexes, preferring INNER JOIN, and leveraging covering indexes and EXPLAIN for efficient query execution.

Best PracticesIndexingMySQL
0 likes · 18 min read
Writing High‑Quality SQL: Practical MySQL Optimization Tips
StarRocks
StarRocks
Jul 2, 2024 · Big Data

What’s New in StarRocks 3.3? Deep Dive into Lakehouse‑Optimized Performance and Features

StarRocks 3.3 introduces a comprehensive set of enhancements—including maturity levels, ARM‑optimized performance, advanced caching, materialized‑view rewrites, storage optimizations, and expanded lakehouse ecosystem support—that together boost stability, query speed, and usability for large‑scale analytics workloads.

LakehouseMaterialized ViewStarRocks
0 likes · 15 min read
What’s New in StarRocks 3.3? Deep Dive into Lakehouse‑Optimized Performance and Features
Alibaba Cloud Developer
Alibaba Cloud Developer
Apr 17, 2024 · Databases

Why MySQL’s TDDL Optimizer Picks the Wrong Index and How to Fix It

This article investigates a slow‑SQL incident caused by the TDDL optimizer reordering a MySQL query, analyzes why the optimizer chose an inefficient index, demonstrates how to verify and force the correct index, and proposes better index and partition‑key designs for optimal performance.

Index OptimizationMySQLPartitioning
0 likes · 11 min read
Why MySQL’s TDDL Optimizer Picks the Wrong Index and How to Fix It
IT Niuke
IT Niuke
Apr 13, 2024 · Databases

JOIN vs IN in MySQL: Performance Comparison and Best Practices

This article compares MySQL's JOIN and IN constructs, showing that IN can be slightly faster for simple filters while JOIN offers greater flexibility for complex, multi‑table queries, and it outlines when to use each approach along with additional optimization tips such as indexing and engine selection.

Database OptimizationJOINMySQL
0 likes · 7 min read
JOIN vs IN in MySQL: Performance Comparison and Best Practices
Liangxu Linux
Liangxu Linux
Apr 7, 2024 · Databases

8 Proven MySQL Query Optimizations to Slash Execution Time

This article explains common MySQL performance pitfalls such as large‑offset pagination, implicit type conversion, inefficient UPDATE/DELETE joins, mixed ordering, EXISTS subqueries, predicate push‑down, early data reduction, and intermediate result set push‑down, and provides concrete rewrites that turn multi‑second queries into millisecond‑level executions.

CTEIndexingJoins
0 likes · 13 min read
8 Proven MySQL Query Optimizations to Slash Execution Time
Java Architect Essentials
Java Architect Essentials
Apr 3, 2024 · Databases

MySQL Query Optimization Techniques: LIMIT, Implicit Conversion, Join Updates, Mixed Sorting, EXISTS, Condition Pushdown, Early Limiting, and Intermediate Result Pushdown

This article presents a series of MySQL performance‑enhancing techniques—including smarter LIMIT usage, avoiding implicit type conversion, rewriting UPDATE/DELETE with JOINs, mixed‑order sorting tricks, replacing EXISTS with JOINs, condition push‑down, early result limiting, and intermediate result push‑down—to dramatically reduce query execution time.

MySQLdatabaseindexes
0 likes · 13 min read
MySQL Query Optimization Techniques: LIMIT, Implicit Conversion, Join Updates, Mixed Sorting, EXISTS, Condition Pushdown, Early Limiting, and Intermediate Result Pushdown
Architecture Digest
Architecture Digest
Mar 1, 2024 · Databases

SQL Performance Optimization and Index Tuning in MySQL

This article walks through a MySQL 5.6 scenario with course, student, and score tables, demonstrates why a naïve sub‑query is extremely slow, and shows step‑by‑step how adding single‑column, composite, and covering indexes, as well as rewriting the query to joins, can reduce execution time from hours to milliseconds.

MySQLSQL optimizationdatabase
0 likes · 10 min read
SQL Performance Optimization and Index Tuning in MySQL
StarRocks
StarRocks
Nov 3, 2023 · Databases

How StarRocks’ Spill to Disk Boosts Query Stability and Performance

StarRocks introduces a spill-to-disk mechanism that writes intermediate results of heavy operators to disk, freeing memory and enabling stable execution of ETL and ad‑hoc queries, while combined with materialized views it dramatically improves query success rates and delivers up to 4.35× faster performance than Spark.

Database OptimizationMaterialized ViewsSpill
0 likes · 10 min read
How StarRocks’ Spill to Disk Boosts Query Stability and Performance
Architect's Guide
Architect's Guide
Oct 10, 2023 · Databases

Advantages and Disadvantages of Application‑Layer Association vs JOIN in MySQL

The article examines why using application‑layer association instead of MySQL JOIN can improve cache efficiency, reduce lock contention, simplify scaling, and enhance query performance, while also outlining scenarios where JOIN is still beneficial and offering practical alternatives for large‑scale data retrieval.

Application LayerCachingDatabase Optimization
0 likes · 6 min read
Advantages and Disadvantages of Application‑Layer Association vs JOIN in MySQL
Java Interview Crash Guide
Java Interview Crash Guide
Sep 8, 2023 · Databases

Boost MySQL Performance: 9 Proven SQL Optimization Techniques

This article explains nine practical MySQL optimization methods—including smarter LIMIT usage, implicit conversion pitfalls, JOIN‑based updates, mixed ordering tricks, EXISTS rewrites, predicate pushdown, early range reduction, intermediate result pushdown, and a concise summary—showing how to transform slow queries into sub‑millisecond operations.

LIMIT clauseMySQLPredicate Pushdown
0 likes · 12 min read
Boost MySQL Performance: 9 Proven SQL Optimization Techniques
php Courses
php Courses
Jul 1, 2023 · Databases

MySQL Queries That Cannot Use Indexes

This article explains five common MySQL query patterns—using functions, leading wildcards with LIKE, OR operators, inequality comparisons, and NULL checks—that prevent the database engine from utilizing indexes, and provides example SQL statements illustrating each case.

Database OptimizationMySQLSQL
0 likes · 3 min read
MySQL Queries That Cannot Use Indexes
Java Backend Technology
Java Backend Technology
Jun 21, 2023 · Databases

How to Supercharge MySQL Queries: 9 Proven Optimization Techniques

This article walks through nine common MySQL performance pitfalls—such as inefficient LIMIT usage, implicit type conversion, sub‑query updates, mixed sorting, EXISTS clauses, condition push‑down, early range reduction, intermediate result push‑down, and more—showing how to rewrite each query and its execution plan to achieve dramatic speed improvements.

MySQLSQL optimizationdatabase tuning
0 likes · 14 min read
How to Supercharge MySQL Queries: 9 Proven Optimization Techniques
Tencent Database Technology
Tencent Database Technology
Apr 27, 2023 · Databases

Understanding MySQL Partition Pruning: Which Partition Should Be Queried?

This article explains how MySQL implements partition pruning, describing the conditions under which the optimizer can skip irrelevant partitions, the internal data structures such as read_partitions and lock_partitions, the role of SEL_TREE and SEL_ARG, and provides a detailed example with code to illustrate the pruning process.

Database OptimizationMySQLPartition Pruning
0 likes · 15 min read
Understanding MySQL Partition Pruning: Which Partition Should Be Queried?
Sohu Tech Products
Sohu Tech Products
Apr 12, 2023 · Databases

MySQL Query Optimization Strategies: Pagination, Joins, Subqueries, Sorting, Group By, and Count

This article presents practical MySQL query optimization techniques—including pagination, index‑based join improvements, subquery replacement with joins, order‑by and group‑by indexing, and count() efficiency—illustrated with real‑world examples, execution‑plan analysis, and ready‑to‑run SQL code.

IndexingMySQLSQL optimization
0 likes · 11 min read
MySQL Query Optimization Strategies: Pagination, Joins, Subqueries, Sorting, Group By, and Count
Su San Talks Tech
Su San Talks Tech
Apr 4, 2023 · Databases

9 Proven MySQL Query Optimizations to Slash Execution Time

This article walks through nine common MySQL performance pitfalls—from inefficient LIMIT clauses and implicit type conversions to sub‑query updates, mixed sorting, EXISTS usage, condition pushdown, early range reduction, and intermediate result pushdown—showing how to rewrite each pattern for dramatically faster execution.

LIMIT clauseMySQLdatabase indexing
0 likes · 15 min read
9 Proven MySQL Query Optimizations to Slash Execution Time
MaGe Linux Operations
MaGe Linux Operations
Mar 7, 2023 · Databases

How Indexes Can Slash MySQL Query Time by 30,000× – Real‑World Optimization

This article walks through a MySQL 5.6 scenario where a nested sub‑query to find students scoring 100 in Chinese took over 30,000 seconds, then demonstrates how adding single‑column and composite indexes, rewriting the query with EXISTS or JOIN, and analyzing execution plans dramatically reduces execution time to milliseconds while explaining index merge, covering indexes, left‑most prefix, and sorting optimizations.

Index OptimizationSQLquery performance
0 likes · 17 min read
How Indexes Can Slash MySQL Query Time by 30,000× – Real‑World Optimization
Java Architect Essentials
Java Architect Essentials
Jan 30, 2023 · Databases

Boost MySQL Performance: 9 Proven Query Optimization Techniques

This article presents nine practical MySQL optimization methods—including smarter LIMIT usage, avoiding implicit type conversion, rewriting subqueries as joins, handling mixed sorting, replacing EXISTS with joins, pushing conditions into subqueries, early range reduction, and using CTEs—to dramatically improve query execution time across common scenarios.

CTEDatabase IndexesJOIN
0 likes · 14 min read
Boost MySQL Performance: 9 Proven Query Optimization Techniques
Laravel Tech Community
Laravel Tech Community
Jan 2, 2023 · Databases

Advanced MySQL Query Optimization Techniques: LIMIT, Implicit Conversion, Join Rewrite, and More

This article presents a series of practical MySQL performance‑tuning methods—including smarter LIMIT usage, handling implicit type conversion, rewriting UPDATE/DELETE with JOIN, mixed‑order optimization, EXISTS replacement, condition push‑down, early result filtering, and CTE usage—to dramatically reduce query execution time across common scenarios.

CTEJOINLIMIT
0 likes · 13 min read
Advanced MySQL Query Optimization Techniques: LIMIT, Implicit Conversion, Join Rewrite, and More
NiuNiu MaTe
NiuNiu MaTe
Dec 21, 2022 · Databases

Why Is MySQL OFFSET Slow? B+ Tree Index Insights & K‑th Largest Query Optimization

This article examines how MySQL uses secondary indexes to find the K‑th largest value, analyzes the O(N) time complexity caused by offset pagination, explains the underlying B+‑tree double‑linked list behavior, and presents three optimization strategies—including index covering, pagination redesign, and boundary prediction—to dramatically improve performance.

B+ TreeIndex OptimizationMySQL
0 likes · 10 min read
Why Is MySQL OFFSET Slow? B+ Tree Index Insights & K‑th Largest Query Optimization
Java Architect Essentials
Java Architect Essentials
Nov 21, 2022 · Databases

MySQL Query Optimization Best Practices: Avoid SELECT *, Use Specific Fields, Index Strategies, Joins, and Other Performance Tips

This article presents a comprehensive set of MySQL optimization guidelines, covering the avoidance of SELECT *, proper use of specific columns, eliminating OR and != in WHERE clauses, preferring numeric types, choosing VARCHAR over CHAR, using TRUNCATE, batch operations, index design, join selection, GROUP BY efficiency, and other practical tips to improve query performance.

MySQLSQL optimizationdatabase indexing
0 likes · 18 min read
MySQL Query Optimization Best Practices: Avoid SELECT *, Use Specific Fields, Index Strategies, Joins, and Other Performance Tips
MaGe Linux Operations
MaGe Linux Operations
Oct 15, 2022 · Databases

20 Essential MySQL Optimization Tips to Boost Query Performance

This article presents a comprehensive set of MySQL best‑practice guidelines—including avoiding SELECT *, steering clear of OR in WHERE clauses, preferring numeric over string types, using VARCHAR instead of CHAR, limiting DELETE/UPDATE, leveraging proper JOIN types, and many other indexing and query‑execution tricks—to dramatically improve query speed, reduce resource consumption, and maintain healthy database design.

IndexingMySQLSQL optimization
0 likes · 18 min read
20 Essential MySQL Optimization Tips to Boost Query Performance
IT Architects Alliance
IT Architects Alliance
Oct 12, 2022 · Databases

Master MySQL Execution Order: 8 Proven Techniques to Supercharge Query Performance

This article explains MySQL's execution order and presents eight practical optimizations—including smarter LIMIT usage, implicit conversion handling, join‑based updates, mixed sorting, EXISTS rewriting, condition push‑down, early range reduction, and intermediate result push‑down—each illustrated with SQL examples and performance measurements.

MySQLSQL optimizationdatabase
0 likes · 14 min read
Master MySQL Execution Order: 8 Proven Techniques to Supercharge Query Performance
Su San Talks Tech
Su San Talks Tech
Sep 13, 2022 · Databases

Top 20 MySQL Query Optimization Tips to Boost Performance

This article presents a comprehensive list of MySQL optimization best practices, covering SELECT field selection, WHERE clause design, data type choices, index usage, join strategies, batch operations, UNION handling, and many other techniques to reduce resource consumption, improve query speed, and ensure efficient database management.

MySQLindexesquery performance
0 likes · 17 min read
Top 20 MySQL Query Optimization Tips to Boost Performance
Architecture Digest
Architecture Digest
Aug 14, 2022 · Big Data

Replacing Classic Data Warehouse Dimensional Model with a Single Wide Table: Architecture, Benefits, and Challenges

This article analyzes the shift from traditional multi‑layer data warehouse dimensional modeling to a single-layer wide‑table approach, detailing business drivers, technical architecture, storage and query performance gains, as well as the development, maintenance, and operational challenges involved.

query performancestorage optimizationwide table
0 likes · 10 min read
Replacing Classic Data Warehouse Dimensional Model with a Single Wide Table: Architecture, Benefits, and Challenges
Efficient Ops
Efficient Ops
Jul 20, 2022 · Databases

How Indexes Can Speed Up MySQL Queries by Tens of Thousands of Times

This article walks through a MySQL scenario where a nested sub‑query runs for hours, demonstrates how adding single‑column and multi‑column indexes, rewriting the query as a join, and analyzing execution plans can reduce execution time from thousands of seconds to a few milliseconds.

Index OptimizationMySQLSQL
0 likes · 12 min read
How Indexes Can Speed Up MySQL Queries by Tens of Thousands of Times
MaGe Linux Operations
MaGe Linux Operations
Jun 28, 2022 · Databases

How Indexes Can Speed Up MySQL Queries by 50,000×: A Deep Dive

This article walks through a MySQL scenario with large tables, demonstrates why a sub‑query that took over eight hours runs in just a second after adding appropriate single‑column and composite indexes, and explains how to read and interpret execution plans for further tuning.

Index OptimizationMySQLSQL
0 likes · 11 min read
How Indexes Can Speed Up MySQL Queries by 50,000×: A Deep Dive
Baidu Geek Talk
Baidu Geek Talk
Jun 15, 2022 · Big Data

Replacing Classic Data Warehouse with a One‑Layer Wide Table Model: Architecture, Benefits, and Challenges

The article proposes replacing the traditional multi‑layered data‑warehouse architecture (ODS‑DWD‑DWS‑ADS) with a single, column‑store wide‑table per business theme, achieving roughly 30 % storage savings and faster queries, while acknowledging higher ETL complexity, back‑tracking costs, and production timing challenges.

ETLParquetbig data
0 likes · 11 min read
Replacing Classic Data Warehouse with a One‑Layer Wide Table Model: Architecture, Benefits, and Challenges
dbaplus Community
dbaplus Community
Aug 11, 2021 · Databases

Turn Slow MySQL Queries into Millisecond Performance with Smart Rewrites

This article explains common MySQL performance pitfalls such as large‑offset LIMIT, implicit type conversion, sub‑query updates, mixed ordering, EXISTS clauses, and condition pushdown, and shows how to rewrite each pattern using indexes, JOINs, UNION ALL, CTEs and early filtering to achieve execution times measured in milliseconds instead of seconds.

CTEJOINMySQL
0 likes · 13 min read
Turn Slow MySQL Queries into Millisecond Performance with Smart Rewrites
Java Architect Essentials
Java Architect Essentials
Jun 18, 2021 · Databases

How to Supercharge MySQL Queries: 8 Proven Optimization Techniques

This article walks through eight common MySQL performance pitfalls—including inefficient LIMIT usage, implicit type conversion, sub‑query updates, mixed sorting, EXISTS clauses, condition push‑down, early range reduction, and intermediate result push‑down—and shows how to rewrite each query to achieve dramatic speed improvements, often reducing execution time from seconds to milliseconds.

Condition PushdownJOINMySQL
0 likes · 14 min read
How to Supercharge MySQL Queries: 8 Proven Optimization Techniques
Code Ape Tech Column
Code Ape Tech Column
Jan 20, 2021 · Databases

Why Does MySQL Pick the Wrong Index? A Deep Dive into Query Optimization

This article examines a slow MySQL query that scans millions of rows, explains why the optimizer chose a low‑selectivity index, demonstrates how forcing the correct index dramatically improves performance, and shows how to create a covering composite index to resolve the issue permanently.

Covering IndexExplain PlanIndex Optimization
0 likes · 8 min read
Why Does MySQL Pick the Wrong Index? A Deep Dive into Query Optimization
ITPUB
ITPUB
Jan 19, 2021 · Databases

Why Indexes Speed Up Database Queries: From Binary Trees to B+ Trees

This article explains how database indexes improve query performance by exploring binary trees, binary search, balanced trees, B‑trees, and B+‑trees, illustrating their structures, advantages, disadvantages, and the impact of disk I/O on overall efficiency.

B+ TreeB-treebalanced tree
0 likes · 17 min read
Why Indexes Speed Up Database Queries: From Binary Trees to B+ Trees
Laravel Tech Community
Laravel Tech Community
Oct 28, 2020 · Databases

Common MySQL Query Performance Pitfalls and Their Optimizations

This article examines frequent MySQL performance problems such as inefficient LIMIT usage, implicit type conversion, sub‑query updates, mixed ordering, EXISTS clauses, condition push‑down, early limiting, intermediate result push‑down, and demonstrates how rewriting queries with proper indexes, JOINs, UNION, and WITH statements can dramatically reduce execution time.

MySQLSQL optimizationdatabase
0 likes · 12 min read
Common MySQL Query Performance Pitfalls and Their Optimizations
Java Captain
Java Captain
Sep 26, 2020 · Databases

MySQL Performance Optimization Techniques: LIMIT, Implicit Conversion, Join Rewrite, Mixed Sorting, EXISTS, Condition Pushdown, and More

This article explains common MySQL performance pitfalls such as large OFFSET in LIMIT clauses, implicit type conversion, sub‑query updates, mixed sorting, EXISTS usage, condition push‑down limitations, and demonstrates how rewriting queries with indexes, JOINs, UNION ALL, and WITH clauses can dramatically reduce execution time.

JOINMySQLSQL optimization
0 likes · 14 min read
MySQL Performance Optimization Techniques: LIMIT, Implicit Conversion, Join Rewrite, Mixed Sorting, EXISTS, Condition Pushdown, and More
Big Data Technology & Architecture
Big Data Technology & Architecture
Sep 16, 2020 · Databases

Optimizing a Complex MySQL Slow Query for Article Comments

This article analyzes a 60‑second MySQL query that retrieves article comments with multiple filters, explains why the optimizer chooses a small table as the driver, and presents a step‑by‑step optimization—including avoiding semi‑joins, improving index usage, refining range conditions, and moving GROUP BY into a subquery—that reduces execution time to 1.3 seconds, achieving a 60‑fold speedup.

MySQLSQL optimizationbig data
0 likes · 13 min read
Optimizing a Complex MySQL Slow Query for Article Comments
Laravel Tech Community
Laravel Tech Community
Jun 22, 2020 · Databases

MySQL Optimization Tips Frequently Asked in Interviews

This article presents a comprehensive collection of MySQL performance‑tuning techniques—including proper use of EXPLAIN, limiting IN lists, selecting explicit columns, using LIMIT 1, avoiding costly ORDER BY RAND(), choosing between IN and EXISTS, preferring UNION ALL, applying full‑text indexes, and optimizing joins, pagination, and index usage—to help developers write faster, more efficient queries.

IndexingMySQLSQL optimization
0 likes · 10 min read
MySQL Optimization Tips Frequently Asked in Interviews
Selected Java Interview Questions
Selected Java Interview Questions
Jun 5, 2020 · Databases

MySQL Query Optimization Techniques and Common Pitfalls

This article explains MySQL execution order, highlights performance problems with large OFFSET LIMIT queries, demonstrates how implicit type conversion, sub‑query rewriting, EXISTS removal, condition push‑down, and intermediate result push‑down can dramatically improve query speed, and provides practical SQL rewrite examples.

JOINLIMITMySQL
0 likes · 13 min read
MySQL Query Optimization Techniques and Common Pitfalls
Architect
Architect
May 22, 2020 · Databases

Performance Analysis of Elasticsearch Queries: Lucene Internals and Benchmark Results

This article examines Elasticsearch query performance by explaining Lucene's underlying data structures, describing how composite queries are merged, and presenting benchmark numbers for various query types such as term, range, and combined queries, highlighting optimization techniques and practical conclusions.

BKD-TreeBenchmarkElasticsearch
0 likes · 13 min read
Performance Analysis of Elasticsearch Queries: Lucene Internals and Benchmark Results
Architect's Tech Stack
Architect's Tech Stack
Sep 13, 2019 · Databases

MySQL Query Optimization: Indexing, Subqueries, and Join Strategies

This article demonstrates how to dramatically improve MySQL query performance by analyzing execution plans, adding appropriate single‑column and composite indexes, converting slow subqueries to efficient joins, and applying covering indexes and ordering optimizations, with detailed examples and timing results.

IndexingMySQLSQL optimization
0 likes · 12 min read
MySQL Query Optimization: Indexing, Subqueries, and Join Strategies
Efficient Ops
Efficient Ops
Jun 28, 2019 · Databases

Master MySQL Optimization: 19 Practical EXPLAIN Tips and Best Practices

This guide presents nineteen essential MySQL optimization techniques—including effective use of EXPLAIN, proper handling of IN and EXISTS, avoiding SELECT *, limiting rows, optimizing ORDER BY, leveraging UNION ALL, employing full‑text indexes, and advanced join strategies—to help developers write faster, more efficient queries.

EXPLAINJOINMySQL
0 likes · 12 min read
Master MySQL Optimization: 19 Practical EXPLAIN Tips and Best Practices
dbaplus Community
dbaplus Community
May 5, 2019 · Databases

Essential MySQL Tips: Data Types, Indexes, and Query Optimization

This article compiles practical MySQL tips for teams without a DBA, covering MySQL's storage‑engine characteristics, data‑type choices, index design strategies, and query‑performance improvements with concrete examples, code snippets, and best‑practice recommendations.

Data TypesDatabase OptimizationMySQL
0 likes · 18 min read
Essential MySQL Tips: Data Types, Indexes, and Query Optimization
Java Captain
Java Captain
Mar 29, 2019 · Databases

MySQL Query Optimization: Indexing Strategies and Execution Plan Analysis

This article demonstrates how to dramatically improve MySQL query performance by analyzing execution plans, adding appropriate single‑column and composite indexes, converting subqueries to EXISTS or JOINs, and leveraging index coverage and ordering, with detailed examples and timing results.

Index OptimizationMySQLSQL Tuning
0 likes · 12 min read
MySQL Query Optimization: Indexing Strategies and Execution Plan Analysis
ITPUB
ITPUB
Jul 17, 2018 · Backend Development

Master MySQL Index Optimization: From Full Table Scans to Covering Indexes

This guide walks through common MySQL performance pitfalls, explains how to interpret EXPLAIN output, and demonstrates step‑by‑step index creation, composite and covering indexes, as well as force‑index usage to dramatically speed up order‑related queries in high‑volume systems.

Composite IndexCovering IndexEXPLAIN
0 likes · 18 min read
Master MySQL Index Optimization: From Full Table Scans to Covering Indexes
ITPUB
ITPUB
Dec 11, 2017 · Databases

19 Essential MySQL Optimization Tips to Boost Query Performance

This guide walks through practical MySQL performance improvements—including using EXPLAIN, limiting IN lists, selecting specific columns, applying LIMIT 1, avoiding ORDER BY RAND(), preferring UNION ALL, proper index usage, forcing indexes, handling range queries, and optimizing JOINs—to help developers write faster, more efficient SQL statements.

EXPLAINMySQLSQL optimization
0 likes · 12 min read
19 Essential MySQL Optimization Tips to Boost Query Performance
ITPUB
ITPUB
Feb 19, 2017 · Databases

Turning an 89ms Query into 0ms: Real‑World SQL Server Index Optimization

The article recounts a performance bottleneck in a Windows service that processed only about 12 records per second, analyzes why a simple SELECT TOP 1 query without proper indexes caused 89 ms latency, and demonstrates step‑by‑step how adding appropriate clustered and non‑clustered indexes reduced the execution time to virtually zero, while also discussing broader indexing strategies and common LEFT JOIN pitfalls.

Index OptimizationNon-clustered IndexSQL Server
0 likes · 9 min read
Turning an 89ms Query into 0ms: Real‑World SQL Server Index Optimization
ITPUB
ITPUB
Jul 12, 2016 · Databases

Practical Guide to PostgreSQL Index Optimization and Cost Analysis

This article walks through practical steps for identifying performance bottlenecks in PostgreSQL, selecting appropriate columns and index types, interpreting system statistics, and evaluating cost estimates with real‑world examples to dramatically reduce query latency.

GiST indexPostgreSQLcost estimation
0 likes · 13 min read
Practical Guide to PostgreSQL Index Optimization and Cost Analysis
21CTO
21CTO
Aug 14, 2015 · Databases

Mastering Elasticsearch: Real-World Indexing & Query Performance Tips

This article shares practical Elasticsearch experience covering index and query performance optimization, shard routing strategies, JVM tuning, daily maintenance, and answers to common production questions, providing actionable guidance for building high‑availability search clusters.

ElasticsearchJVMquery performance
0 likes · 16 min read
Mastering Elasticsearch: Real-World Indexing & Query Performance Tips
Java High-Performance Architecture
Java High-Performance Architecture
Jul 29, 2015 · Databases

How Indexes Enable Efficient GROUP BY Queries in MySQL

This article explains why GROUP BY requires sorting like ORDER BY, how a suitable index can satisfy both operations, and outlines the precise conditions—including single‑table queries, left‑most index prefixes, constant values, and limited aggregates—that allow MySQL to execute GROUP BY using an index scan.

GROUP BYIndex OptimizationMySQL
0 likes · 3 min read
How Indexes Enable Efficient GROUP BY Queries in MySQL