Skip to content
Tagged articles

Query Optimization

462 articles · Page 4 of 5
Top Architect
Top Architect
Jun 28, 2021 · Databases

Understanding Slow Queries and Index Optimization in MySQL, ElasticSearch, and HBase

This article examines common causes of slow queries in MySQL, presents practical indexing techniques, and extends the discussion to performance considerations in ElasticSearch and HBase, offering combined solutions such as sharding, read/write splitting, and using ES as a search layer for large‑scale systems.

ElasticsearchHBaseQuery Optimization
0 likes · 19 min read
Understanding Slow Queries and Index Optimization in MySQL, ElasticSearch, and HBase
Python Programming Learning Circle
Python Programming Learning Circle
Jun 22, 2021 · Databases

Decorrelation of Correlated Subqueries: Theory, Operators, and Optimization Rules

This article explains why correlated subqueries are a performance bottleneck, introduces the Apply (Correlated Join) operator, classifies subquery types, shows how to transform them into efficient joins, and discusses rule‑based push‑down techniques for Project, Filter, Aggregate, and Set operations.

Apply OperatorDecorrelationQuery Optimization
0 likes · 15 min read
Decorrelation of Correlated Subqueries: Theory, Operators, and Optimization Rules
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.

Cost-Based OptimizationMPPOLAP
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.

Data EngineeringOLAPQuery Optimization
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.

LEFT JOINQuery OptimizationSQL
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.

IndexingLIMITLarge Data
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 PerformancePaginationQuery Optimization
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.

IndexMemory ManagementQuery Optimization
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+ TreeIndexingQuery Optimization
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.

EXPLAINQuery OptimizationSQL
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 PerformanceQuery Optimization
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.

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

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

IndexOracleQuery Optimization
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 IndexingQuery Optimization
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.

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

LIMITORDER BYQuery Optimization
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.

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

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

Performance TuningQuery OptimizationSQL
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.

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

EXPLAINIndexQuery Optimization
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 PerformanceQuery OptimizationSlow Queries
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.

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

Performance TuningQuery Optimizationdatabase
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 PoolIndexLIMIT OFFSET
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.

Query OptimizationStorage 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 OFFSETQuery Optimization
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.

Query OptimizationSQLdatabase
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 PerformanceEXPLAINIndex
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.

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

PaginationQuery Optimizationdatabase
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 PerformanceQuery OptimizationSQL
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.

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

EXPLAINQuery Optimizationexecution 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.

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

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

Query Optimizationcontent strategydata analysis
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.

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

Database PerformanceQuery OptimizationSQL
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.

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

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

Linked ServerQuery OptimizationSQL
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.

Query Optimizationmysqlperformance
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
Full-Stack Internet Architecture
Full-Stack Internet Architecture
Jun 26, 2020 · Databases

Understanding MySQL Query Execution Process and Optimization

This article explains how MySQL processes a SELECT statement—from client request, cache check, parsing, optimization, and execution—to return results, using a sample User table, a detailed query example, and a step‑by‑step walkthrough of the parser, optimizer, and executor components.

Query OptimizationSQLdatabase
0 likes · 9 min read
Understanding MySQL Query Execution Process and Optimization
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.

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

Query OptimizationSQL Tuningindex optimization
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.

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

Query Optimizationindexesmysql
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 PoolIndexLIMIT
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.

LIMITQuery Optimizationmysql
0 likes · 10 min read
Why LIMIT with Large Offsets Slows MySQL Queries and How to Fix It
Big Data Technology & Architecture
Big Data Technology & Architecture
Jan 15, 2020 · Databases

Understanding and Using Hash Join in MySQL 8.0

This article explains the concept of Hash Join in MySQL 8.0, compares it with Nested Loop joins, shows how to enable or force it with server variables or hints, and presents performance benchmarks that demonstrate its speed advantages on large datasets.

Hash JoinQuery OptimizationSQL
0 likes · 12 min read
Understanding and Using Hash Join in MySQL 8.0
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.

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

Database PerformanceIndexingQuery Optimization
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.

Database PerformanceQuery Optimizationcost-based optimizer
0 likes · 32 min read
Unlock MySQL Performance: Deep Dive into Query Optimization & Index Design
Full-Stack Internet Architecture
Full-Stack Internet Architecture
Nov 23, 2019 · Databases

Understanding MySQL EXPLAIN: Usage, Output Fields, and Practical Examples

This article explains how to use MySQL's EXPLAIN command, describes each column in its output—including id, select_type, table, type, possible_keys, key, key_len, ref, rows, filtered, and Extra—provides syntax examples, discusses join type rankings, and outlines the command's limitations for query performance analysis.

EXPLAINQuery OptimizationSQL
0 likes · 8 min read
Understanding MySQL EXPLAIN: Usage, Output Fields, and Practical Examples
Efficient Ops
Efficient Ops
Aug 27, 2019 · Databases

Why MySQL Scans the Whole Table Before a Subquery? A Deep Dive into Slow Queries

This article investigates a MySQL slow query caused by a subquery that triggers a full‑table scan, explains MySQL's query rewrite behavior, and shows how Django's lazy ORM evaluation can worsen performance, offering practical fixes using explicit query execution or join rewrites.

Database PerformanceDjango ORMQuery Optimization
0 likes · 8 min read
Why MySQL Scans the Whole Table Before a Subquery? A Deep Dive into Slow Queries
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.

Query OptimizationTable Designstorage-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.

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

Performance TuningQuery OptimizationSQL
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.

LSM-TreeOceanBaseQuery Optimization
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.

Query OptimizationSQLdatabase
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 IndexDelayed Join
0 likes · 9 min read
How to Speed Up Large‑Scale MySQL Pagination: Practical Tips & Benchmarks
Tencent Database Technology
Tencent Database Technology
Apr 18, 2019 · Databases

MySQL Execution Process and SemiJoin Optimization Techniques

This article explains MySQL's execution stages, defines SemiJoin, details how IN subqueries can be transformed into SemiJoin queries, outlines conversion constraints, provides example transformations, and describes the various execution strategies MySQL may use for SemiJoins.

Query OptimizationSQLdatabase
0 likes · 13 min read
MySQL Execution Process and SemiJoin Optimization Techniques
Java Captain
Java Captain
Apr 8, 2019 · Databases

Understanding MySQL Execution Process: Architecture, Query and Update Flow

This article explains MySQL's internal architecture and the step‑by‑step execution flow of SELECT and UPDATE statements, covering server layers, storage engines, parsers, optimizers, executors, and the role of binlog and redo log in ensuring data consistency.

InnoDBQuery OptimizationSQL execution
0 likes · 10 min read
Understanding MySQL Execution Process: Architecture, Query and Update Flow
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.

Performance TuningQuery Optimizationdatabase architecture
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.

EXPLAINPaginationQuery Optimization
0 likes · 12 min read
19 Essential MySQL Optimization Techniques Every Developer Should Know
dbaplus Community
dbaplus Community
Feb 13, 2019 · Big Data

How Zhihu Scaled Its Real-Time Analytics with Druid and Smart Redis Caching

Zhihu built a self‑service analytics platform on Druid, introduced a multi‑level Redis caching strategy, split long‑duration queries across multiple brokers, and added automatic cache invalidation to dramatically improve query latency and resource usage for massive daily request volumes.

AnalyticsDruidQuery Optimization
0 likes · 13 min read
How Zhihu Scaled Its Real-Time Analytics with Druid and Smart Redis Caching