BestHub
Sign in
Tagged articles
409 articles
Page 3 of 5
Top Architect
Top Architect
Aug 2, 2022 · Databases

Understanding MySQL Execution Process: Connectors, Permissions, Cache, Parser, Optimizer, and Executor

This article explains the complete MySQL execution flow, covering the connector's duties, permission tables, caching behavior, parsing of SQL statements, optimizer decision‑making, executor interaction with storage engines, processlist states, logical query order, and practical tips for ordering WHERE‑clause conditions.

CacheExecution ProcessMySQL
0 likes · 11 min read
Understanding MySQL Execution Process: Connectors, Permissions, Cache, Parser, Optimizer, and Executor
Top Architect
Top Architect
Jul 15, 2022 · Databases

SQL Query Optimization Tips to Avoid Full Table Scans

This article presents a comprehensive list of practical SQL query optimization techniques, emphasizing how to avoid full table scans by using proper indexing, rewriting WHERE clauses, avoiding certain operators, functions, and expressions, and providing example code snippets to improve database performance.

SQLT-SQLquery optimization
0 likes · 10 min read
SQL Query Optimization Tips to Avoid Full Table Scans
Youzan Coder
Youzan Coder
Jul 7, 2022 · Big Data

Optimizing Apache Doris Performance: A Case Study in Query Processing

Youzan replaced ClickHouse and Druid with Apache Doris, refined its vectorized engine by eliminating deserialization overhead in the merge‑aggregation phase, achieving roughly a 30 % query‑time boost, and validated compatibility through SQL rewriting and traffic replay, while planning further SIMD‑based optimizations and broader adoption.

Apache DorisClickHouseDruid
0 likes · 8 min read
Optimizing Apache Doris Performance: A Case Study in Query Processing
dbaplus Community
dbaplus Community
Jul 4, 2022 · Databases

Why Large‑Offset LIMIT Is Slow in MySQL and How to Speed It Up

The article explains why a MySQL query with a large LIMIT offset becomes extremely slow, demonstrates the underlying I/O behavior, and shows how rewriting the query with a sub‑query that fetches primary keys first can cut execution time from seconds to milliseconds while also reducing buffer‑pool pollution.

InnoDBLIMITMySQL
0 likes · 9 min read
Why Large‑Offset LIMIT Is Slow in MySQL and How to Speed It Up
Architecture Digest
Architecture Digest
Jun 7, 2022 · Big Data

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

This article examines a business requirement to filter up to 100,000 items from a pool of tens of millions, presenting and evaluating four technical solutions—multithreaded ClickHouse pagination, Elasticsearch scroll‑scan, an ES‑HBase hybrid, and RediSearch + RedisJSON—along with performance data and implementation details.

HBaseRediSearchRedisJSON
0 likes · 10 min read
Design and Optimization Strategies for Querying 100K Records from Tens of Millions Using ClickHouse, Elasticsearch, HBase, and RediSearch
Big Data Technology & Architecture
Big Data Technology & Architecture
May 30, 2022 · Big Data

Doris Architecture, Principles, and Key Features Overview

This article provides a comprehensive overview of Doris's architecture—including its FE and BE components, metadata management, data organization, execution planning—and details its major features such as adaptive join aggregation, vectorized execution, materialized views, and Elasticsearch integration, supplemented with example DDL and query code.

Big DataDatabase ArchitectureElasticsearch
0 likes · 7 min read
Doris Architecture, Principles, and Key Features Overview
Architect
Architect
May 16, 2022 · Databases

Understanding MySQL Execution Process and SQL Execution Order

This article explains the complete MySQL execution pipeline—from client connection, authentication, and permission checks through caching, parsing, optimization, and execution—plus the logical order of SQL clauses, illustrated with diagrams to help developers optimize and comprehend database queries.

Database ArchitectureMySQLPermission management
0 likes · 12 min read
Understanding MySQL Execution Process and SQL Execution Order
ITPUB
ITPUB
May 5, 2022 · Databases

When Does MySQL Use Temporary Tables and How Does It Choose the Storage Engine?

MySQL creates two kinds of temporary tables—external and internal—based on CREATE TEMPORARY TABLE statements or internal query processing, and uses the MEMORY engine by default unless large objects or the big_tables variable force MyISAM or InnoDB, with detailed guidance on scenarios, storage engine selection, conversion thresholds, indexing, and performance tuning.

MySQLSQLquery optimization
0 likes · 19 min read
When Does MySQL Use Temporary Tables and How Does It Choose the Storage Engine?
DataFunSummit
DataFunSummit
Apr 29, 2022 · Big Data

Optimizing Query Performance in Apache Iceberg with Z‑Order Data Organization

This article explains how Apache Iceberg’s DataSkipping technique can lose efficiency when many filter columns are used, and presents a data‑organization optimization using space‑filling curves and Z‑Order to improve query I/O, details the OPTIMIZE implementation, and shares performance benchmark results and future plans.

Apache IcebergBig DataData Skipping
0 likes · 12 min read
Optimizing Query Performance in Apache Iceberg with Z‑Order Data Organization
Big Data Technology & Architecture
Big Data Technology & Architecture
Apr 26, 2022 · Big Data

ByteDance's Internal Presto OLAP Engine: Deployment, Performance Boosts, and Operational Practices

The article details ByteDance's large‑scale deployment of the Presto OLAP engine for ad‑hoc, BI, and near‑real‑time analytics, describing its architecture, multi‑coordinator high‑availability design, routing gateway, adaptive cancel, history server, materialized‑view support, Hudi connector integration, and how these innovations improve performance, stability, and operational efficiency.

Big DataHudi ConnectorMaterialized Views
0 likes · 11 min read
ByteDance's Internal Presto OLAP Engine: Deployment, Performance Boosts, and Operational Practices
DataFunSummit
DataFunSummit
Apr 20, 2022 · Big Data

SuperSQL: A Cross‑Engine, Cross‑DC High‑Performance Big Data SQL Middleware

The article presents SuperSQL, a high‑performance big‑data SQL middleware that enables cross‑engine and cross‑data‑center query processing, detailing its architecture, metadata management, cost‑based optimization, operator push‑down, distributed execution, performance benchmarks, and future roadmap within modern data‑intensive environments.

Big DataCross-DCSQL Middleware
0 likes · 24 min read
SuperSQL: A Cross‑Engine, Cross‑DC High‑Performance Big Data SQL Middleware
DataFunTalk
DataFunTalk
Apr 18, 2022 · Databases

Subgraph Matching in Graph Databases: Concepts, Algorithms, and Optimizations

This article introduces graph databases, explains the subgraph‑matching problem, compares it with relational databases, discusses its computational complexity, and surveys backtracking and multi‑way join algorithms, worst‑case optimal joins, set‑intersection SIMD acceleration, and the gStore system’s research contributions.

RDFSIMDSPARQL
0 likes · 19 min read
Subgraph Matching in Graph Databases: Concepts, Algorithms, and Optimizations
DataFunSummit
DataFunSummit
Apr 9, 2022 · Big Data

Impala Deployment and Optimization: Practical Experience with Sensor Data Multi‑dimensional Analysis Platform

This article presents a comprehensive technical walkthrough of Sensor Data's multi‑dimensional analysis platform, covering product architecture, an Impala‑based real‑time query engine, query performance tuning, resource‑estimation strategies, and future plans, with concrete diagrams, test results, and community contributions.

Big DataData ArchitectureImpala
0 likes · 19 min read
Impala Deployment and Optimization: Practical Experience with Sensor Data Multi‑dimensional Analysis Platform
Architect's Tech Stack
Architect's Tech Stack
Apr 8, 2022 · Databases

Comprehensive Guide to MySQL Query Optimization and Common Pitfalls

This article explains why SQL performance degrades as data grows, outlines a step‑by‑step optimization workflow—including slow‑query detection, EXPLAIN analysis, profiling, optimizer tracing, and corrective actions—while detailing type and extra fields, index usage, and practical scenarios such as large pagination, IN‑order‑by, range queries, and complex joins.

DatabaseMySQLSQL
0 likes · 10 min read
Comprehensive Guide to MySQL Query Optimization and Common Pitfalls
Liangxu Linux
Liangxu Linux
Apr 5, 2022 · Databases

Efficiently Query Multi‑Business‑Entity Sales Data with MySQL Row Comparison

This article explains how to retrieve sales statistics for multiple business units and their selected products in MySQL, compares loop‑based, OR‑concatenated, mixed‑filter, and SQL‑92 row‑comparison approaches, and shows why the row‑comparison technique was ultimately chosen for its index usage and simplicity.

Database DesignDynamic SQLMySQL
0 likes · 6 min read
Efficiently Query Multi‑Business‑Entity Sales Data with MySQL Row Comparison
NiuNiu MaTe
NiuNiu MaTe
Mar 30, 2022 · Databases

Why Is MySQL Query Slow? Hidden Factors and Proven Speed‑Up Tricks

This article explains why MySQL queries can become sluggish beyond missing indexes, covering the full query execution flow, profiling tools, index pitfalls, connection‑pool limits, InnoDB buffer‑pool sizing, and practical steps to diagnose and accelerate database performance.

Connection PoolMySQLbuffer pool
0 likes · 16 min read
Why Is MySQL Query Slow? Hidden Factors and Proven Speed‑Up Tricks
Alibaba Cloud Developer
Alibaba Cloud Developer
Mar 15, 2022 · Big Data

How Modern Data Lake Engines Accelerate Analytics: Inside StarRocks Architecture

This article explains why data lakes are essential for today’s analytics, outlines the three main user demands, defines data lakes, compares rule‑based and cost‑based optimizers, explores record‑oriented versus block‑oriented processing, and details StarRocks’ frontend‑backend architecture and benchmark results.

Analytics EngineBig DataStarRocks
0 likes · 17 min read
How Modern Data Lake Engines Accelerate Analytics: Inside StarRocks Architecture
Top Architect
Top Architect
Mar 5, 2022 · Databases

Why MySQL LIMIT with Large Offsets Is Slow and How to Optimize It

The article demonstrates that MySQL queries using LIMIT with a large offset cause heavy random I/O by scanning many rows, and shows how rewriting the query with a sub‑query that selects only primary keys dramatically reduces execution time from seconds to milliseconds.

LIMITMySQLSQL
0 likes · 8 min read
Why MySQL LIMIT with Large Offsets Is Slow and How to Optimize It
Java Architect Essentials
Java Architect Essentials
Feb 24, 2022 · Databases

Why LIMIT Slows MySQL Queries and How to Speed Them Up

This article analyzes why a MySQL query with a large LIMIT offset can take seconds to run, demonstrates the underlying index and buffer‑pool behavior, and shows how rewriting the query with a sub‑select of primary keys reduces execution time from over 16 seconds to under half a second.

LIMITMySQLbuffer pool
0 likes · 9 min read
Why LIMIT Slows MySQL Queries and How to Speed Them Up
Top Architect
Top Architect
Jan 22, 2022 · Databases

Optimizing MySQL Pagination Queries with Subqueries and Buffer‑Pool Analysis

The article explains why large‑offset LIMIT queries on a MySQL table with millions of rows are slow, demonstrates how rewriting the query to use a subquery that first selects primary‑key IDs dramatically reduces execution time, and validates the improvement by measuring buffer‑pool page usage.

Database PerformanceMySQLbuffer pool
0 likes · 9 min read
Optimizing MySQL Pagination Queries with Subqueries and Buffer‑Pool Analysis
dbaplus Community
dbaplus Community
Jan 16, 2022 · Databases

Essential MySQL Query Optimization Tips to Avoid Full Table Scans

This guide compiles practical MySQL performance techniques—including proper indexing, avoiding costly operators, rewriting OR conditions, using EXISTS, limiting SELECT *, managing temporary tables, configuring threads, choosing storage engines, and optimizing data types—to dramatically reduce full‑table scans and improve query speed.

Database DesignIndexesMySQL
0 likes · 21 min read
Essential MySQL Query Optimization Tips to Avoid Full Table Scans
Java Backend Technology
Java Backend Technology
Jan 15, 2022 · Databases

Why Large OFFSETs Slow MySQL Queries and How Subquery Optimization Fixes It

The article explains how using a large OFFSET in MySQL pagination forces the server to read and discard many rows, causing severe performance degradation, and demonstrates that rewriting the query to first fetch primary‑key IDs via a subquery dramatically reduces I/O and execution time, with buffer‑pool experiments confirming the improvement.

MySQLbuffer poolpagination
0 likes · 9 min read
Why Large OFFSETs Slow MySQL Queries and How Subquery Optimization Fixes It
Programmer DD
Programmer DD
Dec 25, 2021 · Databases

Why SQL Struggles with Complex Queries and How SPL Offers a Solution

The article analyzes four fundamental limitations of SQL—lack of stepwise execution, incomplete set semantics, missing ordered operations, and no object reference mechanism—illustrates them with real queries, and introduces SPL as a more intuitive language that overcomes these issues.

Data AnalysisSPLSQL
0 likes · 19 min read
Why SQL Struggles with Complex Queries and How SPL Offers a Solution
Java Architect Essentials
Java Architect Essentials
Dec 14, 2021 · Databases

Why LIMIT Can Slow Down MySQL Queries and How to Fix It

Using LIMIT with a large offset in MySQL can cause severe performance degradation due to excessive random I/O, but by rewriting the query to first fetch primary keys in a subquery and then joining, execution time can drop from seconds to milliseconds, as demonstrated with real data.

InnoDBLIMITMySQL
0 likes · 10 min read
Why LIMIT Can Slow Down MySQL Queries and How to Fix It
Big Data Technology & Architecture
Big Data Technology & Architecture
Dec 8, 2021 · Big Data

Presto Overview, Architecture, and Query Optimization Techniques

This article introduces Presto, an open‑source MPP SQL engine, explains its coordinator‑worker architecture and connector model, and provides detailed storage, query, and join optimization strategies—including in‑memory parallelism, dynamic plan compilation, and practical SQL code examples—to achieve low‑latency, high‑performance analytics on big data.

Big DataPrestoSQL
0 likes · 7 min read
Presto Overview, Architecture, and Query Optimization Techniques
21CTO
21CTO
Nov 29, 2021 · Databases

Why Moving Business Logic to the Application Boosts Scalability

The article explains how shifting complex filtering, validation, and join operations from the database to the application layer reduces scarce database resource usage, improves scalability, and enables more flexible resource allocation for modern web applications.

Data RetrievalScalabilityapplication architecture
0 likes · 7 min read
Why Moving Business Logic to the Application Boosts Scalability
Ops Development Stories
Ops Development Stories
Nov 12, 2021 · Databases

Unlock MySQL Performance: Mastering EXPLAIN Execution Plans

This article explains what an execution plan is, how to use the EXPLAIN keyword in MySQL 5.7, interprets each column of the EXPLAIN output—including id, select_type, table, type, possible_keys, key, key_len, rows, and Extra—provides detailed examples of different query scenarios, and shows how optimizer settings like derived_merge affect the plan.

Derived TablesEXPLAINIndexes
0 likes · 20 min read
Unlock MySQL Performance: Mastering EXPLAIN Execution Plans
Top Architect
Top Architect
Nov 9, 2021 · Databases

Understanding MySQL Indexes: Types, Structures, and Usage

This article explains MySQL index fundamentals, classifies indexes by data structure, storage engine, field characteristics and column count, compares B+Tree with B‑Tree, hash and red‑black trees, and demonstrates practical creation, usage, and optimization techniques with SQL examples and diagrams.

B+TreeInnoDBMySQL
0 likes · 14 min read
Understanding MySQL Indexes: Types, Structures, and Usage
Senior Brother's Insights
Senior Brother's Insights
Oct 28, 2021 · Databases

Why MySQL LIMIT Can Return Duplicate Rows and How to Fix It

In a production environment, a pagination export feature caused duplicate and missing records due to MySQL’s nondeterministic ordering when multiple rows share the same ORDER BY value, and the article explains the root cause, official documentation, and a reliable solution using additional sorting columns.

LIMITMySQLOrder By
0 likes · 10 min read
Why MySQL LIMIT Can Return Duplicate Rows and How to Fix It
Xiao Lou's Tech Notes
Xiao Lou's Tech Notes
Oct 12, 2021 · Databases

Optimizing ORDER BY/LIMIT in Sharded Databases with Cobar’s Algorithm

This article explains Cobar’s sharding middleware and presents an optimization technique for handling ORDER BY and LIMIT queries across multiple databases, detailing the original approach, its inefficiencies with deep pagination, the step‑by‑step improved algorithm, performance analysis, limitations, and practical applicability.

CobarLIMITOrder By
0 likes · 8 min read
Optimizing ORDER BY/LIMIT in Sharded Databases with Cobar’s Algorithm
macrozheng
macrozheng
Oct 8, 2021 · Databases

How to Speed Up MySQL Deep Pagination: 4 Proven Optimization Techniques

This article explains why MySQL LIMIT deep pagination becomes slow with large offsets and presents four practical optimization methods—including subqueries, delayed INNER JOIN, tag‑recording, and BETWEEN…AND—demonstrated with real‑world production examples.

MySQLSQLpagination
0 likes · 11 min read
How to Speed Up MySQL Deep Pagination: 4 Proven Optimization Techniques
Aikesheng Open Source Community
Aikesheng Open Source Community
Sep 26, 2021 · Databases

Why MySQL Optimizer Chooses Full Table Scan Over Index Scan: Cost Analysis and Experiment

This article explains how MySQL's cost‑based optimizer decides between using an index and performing a full table scan, demonstrates the behavior with a large test table, shows optimizer trace details, and clarifies why index access can sometimes be more expensive than scanning the whole table.

Full Table ScanMySQLcost‑based optimizer
0 likes · 8 min read
Why MySQL Optimizer Chooses Full Table Scan Over Index Scan: Cost Analysis and Experiment
Top Architect
Top Architect
Sep 21, 2021 · Databases

Understanding MySQL LIMIT OFFSET Performance and Buffer‑Pool Impact

The article demonstrates how a large OFFSET in a MySQL LIMIT query forces MySQL to scan millions of index rows and data pages, causing severe slowdown, and shows that rewriting the query with an inner‑join sub‑select dramatically reduces I/O and buffer‑pool usage, confirming the performance difference through experiments.

InnoDBLIMITMySQL
0 likes · 8 min read
Understanding MySQL LIMIT OFFSET Performance and Buffer‑Pool Impact
ITPUB
ITPUB
Aug 13, 2021 · Databases

Why LIMIT with Large OFFSET Slows MySQL and How to Speed It Up

When a MySQL query uses LIMIT with a large offset on a table of millions of rows, the database must scan hundreds of thousands of index entries and corresponding clustered rows, causing massive random I/O; rewriting the query with a sub‑query join reduces I/O dramatically, cutting execution time from over a minute to under a second while also preventing buffer‑pool pollution.

InnoDBLIMITMySQL
0 likes · 9 min read
Why LIMIT with Large OFFSET Slows MySQL and How to Speed It Up
Open Source Linux
Open Source Linux
Aug 1, 2021 · Databases

Why Is MySQL Consuming CPU? Identify and Reduce CPU Bottlenecks

This article explains which components (user processes, IO wait, system interrupts) consume CPU in MySQL, shows their impact on performance, and provides practical strategies to reduce CPU usage through IO optimization, query tuning, data modeling, caching, and hardware upgrades.

CPU BottleneckDatabase OptimizationIO Wait
0 likes · 6 min read
Why Is MySQL Consuming CPU? Identify and Reduce CPU Bottlenecks
Top Architect
Top Architect
Jul 17, 2021 · Databases

Understanding MySQL Query Optimization and Index Design

This article explains MySQL’s logical architecture, query processing steps, and the principles behind query optimization, covering topics such as client‑server protocol, query cache, parsing, cost‑based optimizer, execution engine, and practical index design strategies to improve performance.

Database PerformanceIndexesMySQL
0 likes · 35 min read
Understanding MySQL Query Optimization and Index Design
Aikesheng Open Source Community
Aikesheng Open Source Community
Jul 5, 2021 · Databases

Fetching the Latest Record per Name Group in MySQL 5.6/5.7

The article explains why a simple GROUP BY query in MySQL 5.6 returns the earliest rows instead of the most recent ones, demonstrates how to correctly obtain the latest record per name using sub‑queries or window functions, and discusses the impact of the ONLY_FULL_GROUP_BY mode in MySQL 5.7.

DatabaseGROUP BYMySQL
0 likes · 5 min read
Fetching the Latest Record per Name Group in MySQL 5.6/5.7
Big Data Technology Architecture
Big Data Technology Architecture
Jun 4, 2021 · Big Data

Types of OLAP Data Warehouses and Performance Optimization Techniques

This article explains the various classifications of OLAP data warehouses—including MOLAP, ROLAP, HOLAP, and HTAP—based on data volume and modeling, reviews common open‑source ROLAP products, and details performance‑boosting techniques such as MPP architecture, cost‑based optimization, vectorized execution, and storage optimizations.

Data WarehouseMPPOLAP
0 likes · 27 min read
Types of OLAP Data Warehouses and Performance Optimization Techniques
DeWu Technology
DeWu Technology
May 22, 2021 · Big Data

Unified Semantic Layer for Data Development: Addressing Pain Points and Optimizing Queries

A unified semantic layer for data development solves metric‑change ripple effects, developer burden, and large‑scale query performance problems by offering consistent metric definitions, multi‑view access, concise auto‑generated SQL, instant propagation of updates, and engine‑driven optimal query selection, thereby bridging business and engineering and cutting maintenance effort.

Big DataData EngineeringOLAP
0 likes · 5 min read
Unified Semantic Layer for Data Development: Addressing Pain Points and Optimizing Queries
Java High-Performance Architecture
Java High-Performance Architecture
May 22, 2021 · Backend Development

Boost Elasticsearch Query Speed: Simple Queries & OS Cache Tricks

This article explains how Elasticsearch processes queries across shards, identifies two key performance bottlenecks—query computation time and segment file I/O—and offers practical optimization strategies such as simplifying query logic, maximizing OS file cache usage, increasing memory, reducing stored data, and applying hot‑cold data separation.

ElasticsearchFile CacheI/O
0 likes · 7 min read
Boost Elasticsearch Query Speed: Simple Queries & OS Cache Tricks
Top Architect
Top Architect
May 16, 2021 · Databases

Understanding LEFT JOIN, Common Pitfalls, and Practical Solutions in SQL

This article explains how LEFT JOIN works in SQL, illustrates why duplicate rows can appear when joining tables, and provides practical techniques such as DISTINCT, GROUP BY, MAX, and IN subqueries to ensure a one‑to‑one result set while also covering RIGHT JOIN, INNER JOIN, update and delete operations, and the concept of Cartesian products.

DatabaseJoinLEFT JOIN
0 likes · 14 min read
Understanding LEFT JOIN, Common Pitfalls, and Practical Solutions in SQL
Architecture Digest
Architecture Digest
May 9, 2021 · Databases

Optimizing MySQL Pagination with LIMIT: Methods, Experiments, and Index Strategies

This article examines the performance drawbacks of MySQL's LIMIT pagination on large tables, presents six practical query methods—including direct LIMIT, primary‑key indexing, index‑based ordering, prepared statements, covering indexes, and sub‑query/join techniques—provides extensive benchmark results, and offers concrete indexing recommendations to achieve fast, stable pagination even with millions of rows.

LIMITLarge DataMySQL
0 likes · 12 min read
Optimizing MySQL Pagination with LIMIT: Methods, Experiments, and Index Strategies
Programmer DD
Programmer DD
May 7, 2021 · Databases

Boost MySQL Pagination Performance: Real-World Tests and Optimizations

This article examines why full-table scans become slow on large MySQL tables, demonstrates various pagination techniques—including simple LIMIT, sub‑query, ID‑range, and IN‑clause methods—provides benchmark results for different record counts and offsets, and offers practical tips to dramatically speed up paginated queries.

Database PerformanceMySQLSQL
0 likes · 10 min read
Boost MySQL Pagination Performance: Real-World Tests and Optimizations
Selected Java Interview Questions
Selected Java Interview Questions
May 5, 2021 · Databases

Understanding MySQL Temporary Tables, MEM_ROOT Memory Management, and Query Optimization

This article explains how MySQL processes a GROUP BY query on a user view table, detailing the creation and use of memory and disk temporary tables, the MEM_ROOT allocation mechanism, the execution stages revealed by EXPLAIN, and how adding a composite index can eliminate temporary tables and filesort for better performance.

GROUP BYMySQLindex
0 likes · 13 min read
Understanding MySQL Temporary Tables, MEM_ROOT Memory Management, and Query Optimization
Open Source Linux
Open Source Linux
Apr 30, 2021 · Databases

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

This article explains MySQL's logical architecture, query execution flow, client‑server protocol, query cache behavior, parsing and optimization stages, cost‑based optimizer, execution engine, and provides practical performance‑tuning advice such as schema design, data‑type choices, index creation, B‑Tree fundamentals, covering indexes, and handling COUNT, JOIN, LIMIT, and UNION queries.

B+TreeMySQLindexing
0 likes · 35 min read
Master MySQL Query Optimization: Architecture, Caching, and Index Strategies
Laravel Tech Community
Laravel Tech Community
Apr 28, 2021 · Databases

Understanding and Analyzing MySQL Execution Plans

This article explains the three‑layer architecture of MySQL, how an execution plan is generated and interpreted, and provides a detailed walkthrough of the columns displayed by the EXPLAIN statement to help developers optimize their SQL queries.

DatabaseEXPLAINMySQL
0 likes · 8 min read
Understanding and Analyzing MySQL Execution Plans
MaGe Linux Operations
MaGe Linux Operations
Apr 15, 2021 · Databases

Unlock MySQL Query Performance: Deep Dive into Architecture, Optimizer, and Index Strategies

This article demystifies MySQL’s query execution by exploring its logical architecture, client‑server protocol, query cache, parsing, optimization, and execution engine, then offers practical indexing and performance‑tuning techniques—including B‑Tree fundamentals, covering indexes, and pagination tricks—to help developers write faster, more efficient SQL.

B+TreeDatabase PerformanceMySQL
0 likes · 35 min read
Unlock MySQL Query Performance: Deep Dive into Architecture, Optimizer, and Index Strategies
Programmer DD
Programmer DD
Apr 8, 2021 · Databases

Master SQL HAVING: Filter Groups Like a Pro

This article explains the purpose and proper use of the SQL HAVING clause, demonstrates how it works with GROUP BY to filter aggregated groups, shows examples for finding groups with specific counts, missing IDs, mode, median, and non‑NULL values, and clarifies best practices for query performance and semantics.

GROUP BYHAVINGSQL
0 likes · 13 min read
Master SQL HAVING: Filter Groups Like a Pro
Aikesheng Open Source Community
Aikesheng Open Source Community
Apr 7, 2021 · Databases

Understanding MySQL Index Cardinality and Selectivity

This article explains the concept of MySQL index cardinality, how it influences index selectivity, factors that affect cardinality values, methods to view them, and demonstrates with practical examples how different data distributions and query patterns impact the optimizer's execution plans.

CardinalityDatabaseMySQL
0 likes · 11 min read
Understanding MySQL Index Cardinality and Selectivity
Code Ape Tech Column
Code Ape Tech Column
Apr 6, 2021 · Backend Development

Common Reasons Why Indexes Are Ignored in SQL Queries

This article explains various situations—such as column-to-column comparisons, NULL values, NOT conditions, wildcard LIKE patterns, functions on indexed columns, low‑cardinality leading columns in composite indexes, implicit datatype conversions, arithmetic predicates, virtual and invisible indexes—that can cause an otherwise usable index to be bypassed by the optimizer, leading to full‑table scans.

DatabaseOracleSQL
0 likes · 8 min read
Common Reasons Why Indexes Are Ignored in SQL Queries
Aikesheng Open Source Community
Aikesheng Open Source Community
Mar 10, 2021 · Databases

Unsuitable Scenarios for MySQL Composite Indexes and Refactoring Strategies

This article reviews MySQL composite index syntax, explains the mandatory condition that the leftmost column must appear in the query filter, analyzes seven representative SQL statements that cannot benefit from a composite index, and provides practical refactoring suggestions such as adding single‑column indexes or creating more appropriate composite indexes.

Composite IndexMySQLdatabase indexing
0 likes · 11 min read
Unsuitable Scenarios for MySQL Composite Indexes and Refactoring Strategies
php Courses
php Courses
Feb 9, 2021 · Databases

Impact of Quoting String Primary Keys on MySQL Query Performance

Testing shows that omitting single quotes around a string primary key in MySQL queries can cause a full table scan and increase execution time by about 100‑fold, while quoting the value enables index usage and dramatically improves performance, a difference that disappears when the column is an INT.

DatabaseSQLindexing
0 likes · 4 min read
Impact of Quoting String Primary Keys on MySQL Query Performance
ITPUB
ITPUB
Feb 5, 2021 · Databases

Mastering Subquery Unnesting: Theory, Apply Operator, and Practical Rules

This article explains why correlated subqueries are a performance bottleneck, introduces the Apply (Correlated Join) operator, and presents a comprehensive set of transformation rules—including basic unnesting, Project/Filter push‑down, Aggregate handling, and Set‑operation rewrites—to convert subqueries into efficient join plans for modern SQL engines.

Apply OperatorSQLUnnesting
0 likes · 18 min read
Mastering Subquery Unnesting: Theory, Apply Operator, and Practical Rules
MaGe Linux Operations
MaGe Linux Operations
Jan 15, 2021 · Databases

Why ORDER BY with LIMIT Returns Unexpected Row Order in MySQL

When you combine ORDER BY with LIMIT in MySQL, the result set may appear in a different order than expected, especially if the ordered column contains duplicate values, and understanding the optimizer's behavior and adding deterministic columns can resolve this issue.

DatabaseLIMITMySQL
0 likes · 6 min read
Why ORDER BY with LIMIT Returns Unexpected Row Order in MySQL
Java Interview Crash Guide
Java Interview Crash Guide
Jan 10, 2021 · Databases

Boost MySQL Query Speed: Practical SQL Optimization Tips for 2021

This article explains why SQL performance is critical for fast‑response web services, presents database‑agnostic optimization techniques—such as preferring EXISTS over IN, using JOINs, avoiding unnecessary ORDER BY, leveraging UNION ALL, and applying indexes—to dramatically reduce query latency and resource consumption.

IndexesMySQLSQL
0 likes · 15 min read
Boost MySQL Query Speed: Practical SQL Optimization Tips for 2021
21CTO
21CTO
Jan 5, 2021 · Databases

5 Essential Rules for Writing High‑Performance SQL Queries

This article presents five practical rules—return only needed rows, use proper indexes, avoid subqueries, replace OFFSET pagination with key‑based limits, and understand SQL's logical execution order—to help developers write efficient, high‑performance SQL queries across major relational databases.

IndexesSQLpagination
0 likes · 13 min read
5 Essential Rules for Writing High‑Performance SQL Queries
Code Ape Tech Column
Code Ape Tech Column
Jan 5, 2021 · Databases

5 Essential Rules for Writing High‑Performance SQL Queries

This article explains why SQL query optimization matters and presents five practical rules—return only needed rows, use the right indexes, avoid subqueries, replace OFFSET‑based pagination, and master the logical execution order—to help developers write faster, more efficient database queries across major relational systems.

DatabaseIndexesPerformance tuning
0 likes · 14 min read
5 Essential Rules for Writing High‑Performance SQL Queries
ITPUB
ITPUB
Jan 3, 2021 · Databases

5 Essential Rules for Writing High‑Performance SQL Queries

This article presents five practical rules for optimizing SQL queries—including selecting only needed columns, ensuring proper indexes, avoiding subqueries, using keyset pagination instead of OFFSET, and understanding the logical execution order—along with concrete examples and execution‑plan analysis for major relational databases.

IndexesSQLexecution plan
0 likes · 14 min read
5 Essential Rules for Writing High‑Performance SQL Queries
Architect
Architect
Dec 27, 2020 · Big Data

Optimizing Billion‑Scale Hive Queries: Partitioning, Indexing, Bucketing, Active‑User Segmentation, and Data Structure Refactoring

This article walks through the challenges of querying a 300‑billion‑row Hive table, analyzes why traditional partitioning, indexing, and bucketing fall short, and presents a practical solution that combines active‑user segmentation and a redesigned array‑based data model to cut query time from hours to minutes.

Big DataData PartitioningHive
0 likes · 10 min read
Optimizing Billion‑Scale Hive Queries: Partitioning, Indexing, Bucketing, Active‑User Segmentation, and Data Structure Refactoring
ITPUB
ITPUB
Dec 25, 2020 · Databases

Master MySQL Index Usage: When and How Queries Leverage Indexes

This article explains how MySQL decides whether an index can be used for range scans, LIKE patterns, and ORDER BY operations, detailing key_len calculation, index key vs. filter vs. table filter distinctions, and providing concrete examples and code snippets for better query optimization.

EXPLAINMySQLRange Scan
0 likes · 10 min read
Master MySQL Index Usage: When and How Queries Leverage Indexes
Laravel Tech Community
Laravel Tech Community
Dec 17, 2020 · Databases

Understanding Index Usage and Slow Queries in MySQL

This article explains why using indexes in MySQL does not always prevent slow queries, analyzes full‑index scans, index selectivity, back‑table lookups, virtual columns, and provides practical optimization techniques to reduce scanned rows and improve performance.

Database PerformanceIndexesMySQL
0 likes · 11 min read
Understanding Index Usage and Slow Queries in MySQL
ITPUB
ITPUB
Dec 17, 2020 · Databases

5 Essential Rules to Write High‑Performance SQL Queries

This article presents five practical rules for writing efficient SQL queries—including selecting only needed rows, using proper indexes, avoiding subqueries, replacing OFFSET pagination, and understanding the logical execution order—supported by examples, execution plans, and performance tips for various relational databases.

IndexesSQLperformance
0 likes · 14 min read
5 Essential Rules to Write High‑Performance SQL Queries
Top Architect
Top Architect
Dec 14, 2020 · Databases

Understanding MySQL Query Execution and Optimization Techniques

This article explains MySQL’s logical architecture, query processing steps, caching mechanisms, index structures, and provides practical performance‑tuning advice—including schema design, index creation, query rewriting, and pagination—helping readers grasp the underlying principles and apply effective optimizations in real‑world workloads.

DatabaseIndexesMySQL
0 likes · 34 min read
Understanding MySQL Query Execution and Optimization Techniques
ITFLY8 Architecture Home
ITFLY8 Architecture Home
Dec 12, 2020 · Databases

Why LIMIT Offset Slows MySQL Queries and How to Optimize Them

This article explains how using a large LIMIT offset on a 9.5‑million‑row MySQL table causes massive I/O and slow queries, and demonstrates a sub‑query‑based rewrite that fetches only primary keys before joining, dramatically reducing execution time while also showing buffer‑pool effects.

MySQLbuffer poolindex
0 likes · 8 min read
Why LIMIT Offset Slows MySQL Queries and How to Optimize Them
ITPUB
ITPUB
Dec 11, 2020 · Databases

Master MySQL: 100 Essential Q&A on Indexes, Transactions, and Design

This comprehensive guide answers the most frequent MySQL interview questions, covering index structures, transaction isolation levels, table design best practices, storage engine differences, query optimization techniques, and miscellaneous topics such as data types, binlog formats, and sharding strategies.

Database DesignIndexesMySQL
0 likes · 23 min read
Master MySQL: 100 Essential Q&A on Indexes, Transactions, and Design
Architect
Architect
Dec 10, 2020 · Databases

Optimizing MySQL LIMIT Offset Queries with Subqueries to Reduce Table Scans

This article demonstrates how to dramatically improve MySQL LIMIT offset query performance by moving the filter into a subquery that selects only primary keys, then joining to retrieve full rows, and validates the approach with buffer pool statistics and execution time measurements.

MySQLbuffer poollimit offset
0 likes · 9 min read
Optimizing MySQL LIMIT Offset Queries with Subqueries to Reduce Table Scans
Top Architect
Top Architect
Nov 24, 2020 · Databases

SQL Query Optimization Rules and Best Practices

This article presents five practical rules for writing efficient SQL queries—including using precise WHERE clauses, creating appropriate indexes, avoiding costly subqueries, replacing OFFSET‑based pagination with key‑based limits, and understanding the logical execution order of SQL clauses—to help developers improve database performance across major relational systems.

DatabaseIndexesSQL
0 likes · 14 min read
SQL Query Optimization Rules and Best Practices
ITPUB
ITPUB
Nov 20, 2020 · Databases

How Indexes Supercharge MySQL Queries: A Deep Dive into EXPLAIN

This article explains why indexes dramatically improve MySQL query speed on large tables, outlines common reasons for slow SQL, shows how to use EXPLAIN and SHOW PROFILE to diagnose problems, and provides step‑by‑step examples of creating and evaluating indexes for optimal performance.

Database PerformanceEXPLAINMySQL
0 likes · 15 min read
How Indexes Supercharge MySQL Queries: A Deep Dive into EXPLAIN
Aikesheng Open Source Community
Aikesheng Open Source Community
Nov 20, 2020 · Databases

Optimizing a Slow MySQL 5.7 Query for Detecting Tables Without Primary or Unique Keys

This article examines why a MySQL 5.7 query that finds tables lacking primary or unique keys runs extremely slowly, demonstrates an experiment reproducing the issue, analyzes the optimizer's subquery rewrite, and shows how adding hints and materialization reduces execution time from over 16 seconds to under one second.

Information SchemaMySQLoptimizer hint
0 likes · 4 min read
Optimizing a Slow MySQL 5.7 Query for Detecting Tables Without Primary or Unique Keys
Architect
Architect
Nov 17, 2020 · Databases

MySQL Pagination Optimization Techniques and Performance Testing

The article examines MySQL pagination performance on a large order_history table, presenting various pagination methods—including simple LIMIT, subquery, ID range, and temporary table techniques—along with detailed test results that show how query speed varies with record count and offset.

DatabaseMySQLpagination
0 likes · 10 min read
MySQL Pagination Optimization Techniques and Performance Testing
ITPUB
ITPUB
Nov 13, 2020 · Databases

Master MySQL Indexes: How Explain Optimizes Query Performance

This guide explains why MySQL queries become slow, how to diagnose issues with slow‑query logs, Explain and Show Profile, details the structure and types of indexes, interprets each column of the Explain output, and demonstrates practical optimization steps with real SQL examples.

Database PerformanceIndexesMySQL
0 likes · 14 min read
Master MySQL Indexes: How Explain Optimizes Query Performance
ITPUB
ITPUB
Nov 3, 2020 · Databases

How Indexes Supercharge MySQL Queries: A Deep Dive into EXPLAIN

This article explains why MySQL indexes dramatically improve query speed, outlines common reasons for slow SQL, shows how to use EXPLAIN to analyze execution plans, and provides step‑by‑step optimization examples with code and visual illustrations.

EXPLAINMySQLindex
0 likes · 14 min read
How Indexes Supercharge MySQL Queries: A Deep Dive into EXPLAIN
Big Data Technology & Architecture
Big Data Technology & Architecture
Oct 28, 2020 · Big Data

Optimizing Hive Queries: Fetch Task Conversion, Local Mode, Map‑Side Aggregation, and Dynamic Partitioning

This article explains how to optimize Hive query performance by configuring fetch task conversion, enabling local mode, using map‑side aggregation, handling COUNT(DISTINCT) efficiently, avoiding Cartesian joins, applying partition and column pruning, and setting dynamic partition parameters, with practical command‑line examples.

dynamic partitionlocal modequery optimization
0 likes · 11 min read
Optimizing Hive Queries: Fetch Task Conversion, Local Mode, Map‑Side Aggregation, and Dynamic Partitioning
Programmer DD
Programmer DD
Oct 23, 2020 · Databases

Master MySQL EXPLAIN: Decode Execution Plans and Optimize Queries

This guide walks you through MySQL's EXPLAIN command, explaining each output column, demonstrating how to read execution plans for simple selects, joins, subqueries, UNIONs, and advanced variants, and showing how to use this information to improve query performance.

EXPLAINMySQLexecution plan
0 likes · 24 min read
Master MySQL EXPLAIN: Decode Execution Plans and Optimize Queries