BestHub
Sign in
Tagged articles

query optimization

448 articles · Page 4 of 5
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+ TreeMySQLSQL
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.

AggregationGROUP BYHAVING
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.

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

OraclePerformanceSQL
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 IndexDatabase IndexingMySQL
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.

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

LIMITMySQLORDER BY
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.

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

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

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

SQLexecution planindexes
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.

Data ModelingData 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.

MySQLSlow QueriesVirtual Columns
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.

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

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

Buffer PoolLIMIT OFFSETMySQL
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.

MySQLStorage EnginesTransactions
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.

Buffer PoolLIMIT OFFSETMySQL
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.

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

EXPLAINMySQLdatabase performance
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.

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

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

MySQLSQLdatabase performance
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.

EXPLAINMySQLPerformance
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
Open Source Linux
Open Source Linux
Oct 23, 2020 · Databases

Boost MySQL Performance: 8 Proven Query Optimization Techniques

This article explores eight common MySQL performance pitfalls—including inefficient LIMIT usage, implicit type conversion, subquery updates, mixed sorting, EXISTS clauses, condition push‑down, early result narrowing, and intermediate result push‑down—and provides rewritten SQL examples that dramatically reduce execution time.

CTEIndexingMySQL
0 likes · 13 min read
Boost MySQL Performance: 8 Proven Query Optimization Techniques
ITPUB
ITPUB
Oct 18, 2020 · Databases

Boost MySQL Pagination Speed: Practical Query Optimizations and Benchmarks

This article examines MySQL pagination on a multi‑million‑row table, compares plain LIMIT queries with subquery, ID‑range, and temporary‑table techniques, provides SQL examples and performance measurements, and offers recommendations for fast, scalable paging.

MySQLPerformanceSQL
0 likes · 10 min read
Boost MySQL Pagination Speed: Practical Query Optimizations and Benchmarks
DataFunTalk
DataFunTalk
Oct 10, 2020 · Product Management

Search Product Optimization: From System Architecture to User Demand and Content Strategies

This article outlines a comprehensive approach for search product managers to drive system improvements, covering overall architecture, query understanding, recall and ranking optimization, business and presentation rules, content enrichment, frontend design, and methods for uncovering user needs through data and behavior analysis.

Data Analysiscontent strategyproduct management
0 likes · 24 min read
Search Product Optimization: From System Architecture to User Demand and Content Strategies
ITFLY8 Architecture Home
ITFLY8 Architecture Home
Oct 2, 2020 · Databases

Essential MySQL Optimization Tips: From EXPLAIN to Advanced Index Strategies

This article presents a comprehensive guide to MySQL performance tuning, covering the use of EXPLAIN, proper handling of IN clauses, selective column queries, limit usage, index best practices, avoiding costly operations, pagination techniques, full‑text search, join optimization, and advanced directives such as FORCE INDEX and STRAIGHT_JOIN.

IndexingMySQLPerformance
0 likes · 12 min read
Essential MySQL Optimization Tips: From EXPLAIN to Advanced Index Strategies
Top Architect
Top Architect
Sep 22, 2020 · Databases

Understanding MySQL Query Execution and Optimization Techniques

This article explains MySQL’s logical architecture, query processing stages, client‑server protocol, query cache, parsing, optimization, execution engine, and provides practical performance‑tuning advice such as index design, data‑type choices, covering indexes, limit pagination, and handling UNION and JOIN operations.

MySQLSQLdatabase performance
0 likes · 35 min read
Understanding MySQL Query Execution and Optimization Techniques
Programmer DD
Programmer DD
Sep 19, 2020 · Databases

Boost MySQL Pagination Performance: Proven Techniques and Benchmarks

This article examines various MySQL pagination strategies—including simple LIMIT clauses, sub‑query optimizations, ID‑range queries, and temporary‑table methods—by testing them on a table with millions of rows and presenting detailed timing results that reveal significant speed improvements.

LIMITMySQLpagination
0 likes · 9 min read
Boost MySQL Pagination Performance: Proven Techniques and Benchmarks
JavaEdge
JavaEdge
Aug 30, 2020 · Databases

Master MySQL EXPLAIN: Decode Every Column and Boost Query Performance

This guide explains each column returned by MySQL's EXPLAIN output, details join types, possible keys, extra information, and shows how to use EXPLAIN EXTENDED, SHOW PROFILE, and optimizer trace to analyze and optimize SQL queries effectively.

EXPLAINMySQLSQL profiling
0 likes · 14 min read
Master MySQL EXPLAIN: Decode Every Column and Boost Query Performance
Java Captain
Java Captain
Aug 15, 2020 · Databases

Comprehensive SQL Server Cheat Sheet: Basics, Advanced Queries, Administration, and Replication

This article provides a detailed collection of SQL Server commands and techniques covering database creation, table and index management, common DML statements, advanced set operators, various join types, grouping, pagination, maintenance tasks, replication setup, linked‑server usage, and a synchronization stored procedure.

Database AdministrationLinked ServerSQL
0 likes · 22 min read
Comprehensive SQL Server Cheat Sheet: Basics, Advanced Queries, Administration, and Replication
dbaplus Community
dbaplus Community
Jul 21, 2020 · Databases

What Are the Different Types of OLAP and How Do They Impact Performance?

This article provides a comprehensive overview of OLAP systems, classifying them by data volume and modeling approach, comparing MOLAP, ROLAP, HOLAP and HTAP, reviewing popular open‑source products, and detailing architectural, query‑optimization, vectorization, storage and resource‑management techniques that affect analytical warehouse performance.

HTAPMOLAPOLAP
0 likes · 30 min read
What Are the Different Types of OLAP and How Do They Impact Performance?
Java Backend Technology
Java Backend Technology
Jul 21, 2020 · Databases

Boost MySQL Pagination Speed: Proven Techniques and Benchmarks

This article examines why full‑table scans on massive MySQL tables are slow, explains standard LIMIT‑based pagination, and presents several optimization strategies—including sub‑queries, ID‑range filtering, and temporary tables—backed by concrete performance measurements.

MySQLPerformancequery optimization
0 likes · 10 min read
Boost MySQL Pagination Speed: Proven Techniques and Benchmarks
JD Retail Technology
JD Retail Technology
Jul 13, 2020 · Databases

Real‑Time Analytics Engine Based on ClickHouse: Architecture, MergeTree, Data Ingestion, and Query Optimization

This article describes how JD.com’s Algorithmic Intelligence team built a ClickHouse‑based real‑time analytics engine, covering ClickHouse fundamentals, MergeTree table design, Kafka‑Flink data pipelines, JDBC batch loading, query‑optimization techniques, and monitoring for handling billions of rows with sub‑second response times.

ClickHouseData IngestionMergeTree
0 likes · 14 min read
Real‑Time Analytics Engine Based on ClickHouse: Architecture, MergeTree, Data Ingestion, and Query Optimization
Selected Java Interview Questions
Selected Java Interview Questions
Jul 3, 2020 · Databases

Understanding MySQL Index Structures: From Simple Tables to B+ Trees

This article explains how MySQL stores data using pages, page directories, and multi‑page structures, demonstrates why MySQL silently sorts rows on insert, and shows how these mechanisms combine into the B+‑tree index that optimizes query performance and supports clustering, non‑clustering, and composite indexes.

B+ TreeComposite IndexDatabase Pages
0 likes · 24 min read
Understanding MySQL Index Structures: From Simple Tables to B+ Trees
360 Quality & Efficiency
360 Quality & Efficiency
Jun 12, 2020 · Databases

MongoDB Pagination, Query Optimization, and Index Design Guide

This article explains various MongoDB pagination strategies—including skip/limit, range queries, and sorting—covers performance monitoring with profiling, details index creation and design principles, and describes how to interpret explain plans for efficient query optimization.

IndexingMongoDBdatabase
0 likes · 13 min read
MongoDB Pagination, Query Optimization, and Index Design Guide
Top Architect
Top Architect
Jun 7, 2020 · Databases

MySQL Performance Optimization: Data Volume, Concurrency, Index Design, and SQL Tuning

This article presents a comprehensive guide to MySQL performance improvement, covering maximum data volume, connection limits, query latency targets, practical implementation principles, optimal table design, index classification and tuning, as well as detailed SQL optimizations such as batch processing, operator rewriting, and pagination techniques.

Index OptimizationMySQLPerformance
0 likes · 16 min read
MySQL Performance Optimization: Data Volume, Concurrency, Index Design, and SQL Tuning
ITPUB
ITPUB
Jun 3, 2020 · Databases

Why MySQL Ignored My Index: Charset Mismatch Triggers Full Table Scans

A MySQL join between a large and a small table ran for seconds because differing character sets forced implicit conversions that disabled the index, and the article shows how to diagnose the issue with EXPLAIN, fix it by aligning charsets, and avoid similar pitfalls.

CharsetJOINMySQL
0 likes · 7 min read
Why MySQL Ignored My Index: Charset Mismatch Triggers Full Table Scans
Architecture Digest
Architecture Digest
Apr 30, 2020 · Databases

Understanding MySQL Query Optimization and Performance Tuning

This article explains MySQL's logical architecture, query processing steps, and the underlying principles of query optimization, covering topics such as client‑server protocol, query cache, parsing, optimizer cost model, index structures, and practical performance‑tuning strategies for efficient database operations.

MySQLindexesquery optimization
0 likes · 33 min read
Understanding MySQL Query Optimization and Performance Tuning
Java Captain
Java Captain
Apr 5, 2020 · Databases

Optimizing MySQL LIMIT Queries by Reducing Row Lookups

This article explains why large OFFSET values in MySQL LIMIT queries cause severe performance degradation and demonstrates how rewriting the query to fetch primary keys first and then joining reduces execution time from seconds to milliseconds, supported by buffer‑pool analysis and practical test results.

Buffer PoolLIMITMySQL
0 likes · 8 min read
Optimizing MySQL LIMIT Queries by Reducing Row Lookups
Java Backend Technology
Java Backend Technology
Jan 26, 2020 · Databases

Why LIMIT with Large Offsets Slows MySQL Queries and How to Fix It

The article explains how using LIMIT with a large offset on a MySQL table forces the engine to scan hundreds of thousands of index rows and cluster‑index pages, dramatically increasing I/O, and shows a sub‑query join technique that reduces the scan to just the needed rows, cutting execution time from minutes to fractions of a second while also illustrating the impact on the InnoDB buffer pool.

LIMITMySQLPerformance
0 likes · 10 min read
Why LIMIT with Large Offsets Slows MySQL Queries and How to Fix It
Architecture Digest
Architecture Digest
Jan 13, 2020 · Databases

Understanding Index Usage and Slow Queries in MySQL

This article explains why using an index does not guarantee a fast query in MySQL, analyzes how slow‑query detection works, demonstrates full‑index scans, discusses index selectivity, the cost of row‑lookup (back‑table) operations, and shows how virtual columns and index condition push‑down can improve performance.

MySQLindexesquery optimization
0 likes · 12 min read
Understanding Index Usage and Slow Queries in MySQL
Big Data Technology & Architecture
Big Data Technology & Architecture
Dec 14, 2019 · Databases

MySQL Logical Architecture, Query Process, and Performance Optimization

This article explains MySQL's three‑layer logical architecture, the end‑to‑end query execution flow—including client/server protocol, query cache, parsing, cost‑based optimization, execution engine, and result delivery—followed by practical performance‑tuning advice on schema design, data types, index creation, and specific query optimizations such as COUNT(), JOINs, LIMIT pagination, and UNION handling.

IndexingMySQLSQL
0 likes · 34 min read
MySQL Logical Architecture, Query Process, and Performance Optimization
MaGe Linux Operations
MaGe Linux Operations
Dec 1, 2019 · Databases

Unlock MySQL Performance: Deep Dive into Query Optimization & Index Design

This article explains MySQL's logical architecture, query execution steps, client‑server protocol, query cache behavior, cost‑based optimizer mechanics, and practical index design strategies—including B+Tree fundamentals, covering indexes, and common pitfalls—to help developers achieve measurable performance improvements.

MySQLcost-based optimizerdatabase performance
0 likes · 32 min read
Unlock MySQL Performance: Deep Dive into Query Optimization & Index Design
ITPUB
ITPUB
Aug 18, 2019 · Databases

100 Essential MySQL Interview Questions: Indexes, Transactions, and More

This comprehensive guide answers 100 common MySQL interview questions, covering index types and trade‑offs, transaction concepts and isolation levels, table design best practices, storage engine differences, and various performance‑related tips for developers.

Table Designquery optimizationstorage-engine
0 likes · 22 min read
100 Essential MySQL Interview Questions: Indexes, Transactions, and More
Qunar Tech Salon
Qunar Tech Salon
Jul 9, 2019 · Databases

SQL Fundamentals and Query Optimization Techniques for PostgreSQL

This article introduces SQL fundamentals, explains DDL/DML/DCL categories, and provides practical PostgreSQL query optimization techniques—including index selection, condition rewriting, sub‑query removal, expression and partial indexes, DDL decomposition, CTE usage, and key configuration parameters—to help developers and DBAs improve performance and maintainability.

PostgreSQLSQLSQL Fundamentals
0 likes · 12 min read
SQL Fundamentals and Query Optimization Techniques for PostgreSQL
Java Captain
Java Captain
May 31, 2019 · Databases

Common MySQL Query Performance Issues and Optimization Techniques

This article examines frequent MySQL performance problems such as inefficient LIMIT pagination, implicit type conversion, sub‑query updates, mixed ordering, EXISTS usage, condition push‑down limits, early result set reduction, and intermediate result set optimization, providing rewritten SQL examples that dramatically improve execution speed.

MySQLSQLdatabase
0 likes · 11 min read
Common MySQL Query Performance Issues and Optimization Techniques
AntTech
AntTech
May 29, 2019 · Databases

OceanBase Query Optimizer: Challenges, Techniques, and Engineering Practices

This article examines the core challenges of query optimization in relational databases—accurate statistics, massive plan spaces, and efficient plan management—and explains how OceanBase addresses them through logical/physical row concepts, real‑time statistics, distributed two‑stage planning, adaptive caching, and plan evolution mechanisms.

Cost ModelLSM-TreeOceanBase
0 likes · 15 min read
OceanBase Query Optimizer: Challenges, Techniques, and Engineering Practices
dbaplus Community
dbaplus Community
May 26, 2019 · Databases

Why Is MySQL Query Slow? Common Causes and How to Diagnose Them

This article explains why a MySQL statement may run slowly—whether only occasionally or every time—by examining redo‑log flushing, lock contention, missing or misused indexes, optimizer mis‑estimates, and provides practical commands to identify and resolve each issue.

MySQLSQLdatabase
0 likes · 12 min read
Why Is MySQL Query Slow? Common Causes and How to Diagnose Them
Programmer DD
Programmer DD
May 26, 2019 · Databases

How to Speed Up Large‑Scale MySQL Pagination: Practical Tips & Benchmarks

This article explains why traditional MySQL pagination (ORDER BY … LIMIT N,M) becomes painfully slow on tens of millions of rows, analyzes the root causes, and presents concrete optimizations such as covering‑index delayed joins, bookmark‑based scans, and query rewrites that can cut execution time to a third of the original.

Bookmark ScanCovering IndexMySQL
0 likes · 9 min read
How to Speed Up Large‑Scale MySQL Pagination: Practical Tips & Benchmarks
MaGe Linux Operations
MaGe Linux Operations
Mar 19, 2019 · Databases

Unlock MySQL Performance: Deep Dive into Query Optimization & Architecture

This article explains MySQL's logical architecture, query processing steps, and the inner workings of its optimizer, cache, and execution engine, then offers practical performance‑tuning advice on schema design, index creation, and specific query patterns to help developers make informed optimization decisions.

MySQLdatabase architectureindex design
0 likes · 35 min read
Unlock MySQL Performance: Deep Dive into Query Optimization & Architecture
Liangxu Linux
Liangxu Linux
Feb 20, 2019 · Databases

19 Essential MySQL Optimization Techniques Every Developer Should Know

This guide presents 19 practical MySQL optimization tips—including using EXPLAIN, avoiding SELECT *, limiting IN lists, preferring UNION ALL, improving pagination, leveraging full‑text indexes, and proper join strategies—to help developers write faster, more efficient queries.

EXPLAINJOINMySQL
0 likes · 12 min read
19 Essential MySQL Optimization Techniques Every Developer Should Know
Zhuanzhuan Tech
Zhuanzhuan Tech
Jan 11, 2019 · Databases

Differences Between TiDB and MySQL: Transactions, Queries, Server‑Side Prepared Statements, and Batch Processing

This article examines TiDB, a world‑class open‑source distributed NewSQL database, comparing its transaction and query behavior with MySQL, discussing underlying Percolator model, server‑side prepared statements, batch processing techniques, and practical optimization strategies for developers.

Batch ProcessingMySQLTiDB
0 likes · 10 min read
Differences Between TiDB and MySQL: Transactions, Queries, Server‑Side Prepared Statements, and Batch Processing
360 Tech Engineering
360 Tech Engineering
Jan 7, 2019 · Databases

SQL Query Analysis and Access Path Strategies

This article explains how relational optimizers validate, analyze, and transform SQL statements into executable code, detailing the four-step optimization process, query‑analysis factors, cost‑based access‑path selection, table scans, index lookups, and the role of data prefetching in improving performance.

Access PathIndex ScanSQL
0 likes · 12 min read
SQL Query Analysis and Access Path Strategies
Youzan Coder
Youzan Coder
Nov 16, 2018 · Databases

Optimizing Large-Scale Pagination Queries in MySQL

When paginating millions of rows in MySQL, avoid large OFFSET scans by using delayed joins or bookmark techniques that first fetch primary‑key values via covering indexes, then retrieve the needed rows, dramatically reducing I/O and query latency.

MySQLbookmark paginationdatabase
0 likes · 10 min read
Optimizing Large-Scale Pagination Queries in MySQL
360 Quality & Efficiency
360 Quality & Efficiency
Sep 29, 2018 · Databases

Diagnosing and Optimizing MySQL Query Performance Issues

This article explains how large data volumes and differing environments can degrade MySQL query performance, demonstrates using EXPLAIN to analyze index usage and row scans, and offers practical steps such as adding filters, pagination, and code adjustments to improve speed.

EnvironmentIndexingMySQL
0 likes · 6 min read
Diagnosing and Optimizing MySQL Query Performance Issues
ITPUB
ITPUB
Jul 7, 2018 · Databases

Unlocking MySQL: How SQL Parsing Works and Boosts DBA Efficiency

This article explains why protecting database systems is critical, reviews existing SQL‑analysis tools, and dives deep into MySQL's lexical and syntax parsing techniques—including Bison‑generated parsers, core data structures, and practical applications such as useless‑condition removal and SQL feature generation—to help DBAs automate and optimize their workflows.

BisonDBA toolsMySQL
0 likes · 15 min read
Unlocking MySQL: How SQL Parsing Works and Boosts DBA Efficiency
MaGe Linux Operations
MaGe Linux Operations
May 21, 2018 · Databases

Unlock MySQL Performance: Deep Dive into Query Optimization Principles

This article explains MySQL's logical architecture, query execution flow, and the inner workings of its optimizer, then provides practical performance‑tuning advice—including index design, query cache handling, and specific query‑type optimizations—to help developers make informed decisions for real‑world workloads.

index designperformance tuningquery optimization
0 likes · 38 min read
Unlock MySQL Performance: Deep Dive into Query Optimization Principles
Efficient Ops
Efficient Ops
May 7, 2018 · Databases

Unveiling MySQL Query Optimization: Architecture, Execution, and Practical Tips

This article demystifies MySQL query optimization by explaining the server's logical architecture, the end‑to‑end query processing flow, the role of the client/server protocol, query cache, parsing, optimizer, execution engine, and result delivery, and then offers concrete performance‑tuning recommendations on schema design, data types, indexing strategies, and specific query patterns such as COUNT(), JOIN, LIMIT pagination, and UNION.

IndexingMySQLSQL
0 likes · 35 min read
Unveiling MySQL Query Optimization: Architecture, Execution, and Practical Tips
Java Captain
Java Captain
Apr 6, 2018 · Databases

Understanding MySQL Indexes and Using EXPLAIN for Query Optimization

This article explains how MySQL indexes work, why queries can become slow, how to interpret the EXPLAIN output—including id, select_type, table, type, possible_keys, key, key_len, ref, rows, and extra columns—and provides practical examples and optimization cases to improve query performance while balancing the cost of maintaining indexes.

MySQLSQLdatabase performance
0 likes · 13 min read
Understanding MySQL Indexes and Using EXPLAIN for Query Optimization
ITPUB
ITPUB
Mar 26, 2018 · Databases

30 Essential SQL Query Optimization Tips to Avoid Full Table Scans

This article presents thirty practical SQL tuning guidelines—ranging from avoiding certain WHERE‑clause operators and functions to proper index usage and set‑based alternatives—that help prevent full table scans and improve overall database performance.

SQLbest practicesdatabase performance
0 likes · 11 min read
30 Essential SQL Query Optimization Tips to Avoid Full Table Scans
ITPUB
ITPUB
Mar 13, 2018 · Databases

Optimizing Large-Scale Oracle Queries with Temporary Tables

This article examines a high‑volume Oracle query scenario, explains why using multiple IN clauses on millions of rows leads to costly full‑table scans, and presents three alternative strategies—including a single‑SQL join, UNION ALL view, and temporary‑table approaches—along with practical examples and performance considerations.

OracleSQLdatabase performance
0 likes · 10 min read
Optimizing Large-Scale Oracle Queries with Temporary Tables