BestHub
Sign in
Tagged articles

SQL optimization

362 articles · Page 3 of 4
Laravel Tech Community
Laravel Tech Community
Jan 2, 2023 · Databases

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

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

CTEJOINLIMIT
0 likes · 13 min read
Advanced MySQL Query Optimization Techniques: LIMIT, Implicit Conversion, Join Rewrite, and More
MaGe Linux Operations
MaGe Linux Operations
Dec 29, 2022 · Databases

Master MySQL Monitoring with Built‑in SHOW Commands: A Complete Guide

This article explains how to collect comprehensive MySQL performance data—including connections, buffer cache, locks, statement counts, throughput, server configuration, and slow‑query logs—using only native SHOW commands, providing step‑by‑step SQL snippets, calculation formulas, and best‑practice tips for efficient monitoring.

Database MonitoringSHOW commandsSQL optimization
0 likes · 10 min read
Master MySQL Monitoring with Built‑in SHOW Commands: A Complete Guide
Java Architect Essentials
Java Architect Essentials
Nov 21, 2022 · Databases

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

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

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

Boost MySQL Performance: 20 Proven SQL Optimization Tips

This article presents a comprehensive set of MySQL optimization techniques, covering everything from avoiding SELECT * and OR conditions to proper index usage, join strategies, batch operations, and query planning, all aimed at reducing resource consumption, improving execution speed, and ensuring reliable database design.

IndexingMySQLQuery Tuning
0 likes · 19 min read
Boost MySQL Performance: 20 Proven SQL Optimization Tips
Top Architect
Top Architect
Oct 25, 2022 · Databases

Understanding Database Indexes: Storage Principles, Binary Search, and Optimization Techniques

This article explains how databases store data on various storage media, why indexes dramatically speed up queries through sorted structures and binary search, discusses different index types such as clustered indexes, and outlines common SQL optimization practices while warning against excessive indexing and typical pitfalls.

SQL optimizationbinary searchclustered index
0 likes · 12 min read
Understanding Database Indexes: Storage Principles, Binary Search, and Optimization Techniques
MaGe Linux Operations
MaGe Linux Operations
Oct 15, 2022 · Databases

20 Essential MySQL Optimization Tips to Boost Query Performance

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

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

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

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

MySQLSQL optimizationdatabase
0 likes · 14 min read
Master MySQL Execution Order: 8 Proven Techniques to Supercharge Query Performance
macrozheng
macrozheng
Sep 30, 2022 · Databases

Why MySQL count(*) Slows Down on Large Tables and How to Optimize It

This article explains how MySQL's count() works across different storage engines, compares the performance of various count() forms, and offers practical strategies—such as using EXPLAIN rows, auxiliary count tables, and batch processing—to obtain accurate or approximate row counts efficiently even on massive tables.

COUNTInnoDBMyISAM
0 likes · 13 min read
Why MySQL count(*) Slows Down on Large Tables and How to Optimize It
Code Ape Tech Column
Code Ape Tech Column
Sep 27, 2022 · Databases

How to Quickly Locate and Optimize Slow Query SQL in MySQL

This guide explains how to enable MySQL slow query logging, set appropriate thresholds, locate inefficient SQL statements via logs and EXPLAIN, and optimize queries by using indexes and analyzing execution plans, with practical examples and code snippets.

EXPLAINMySQLSQL optimization
0 likes · 11 min read
How to Quickly Locate and Optimize Slow Query SQL in MySQL
dbaplus Community
dbaplus Community
Sep 22, 2022 · Databases

Why MySQL IN Subqueries Turn Into Slow Full Scans—and How to Fix Them

A large‑scale user‑push system suffers from minutes‑long MySQL queries because the IN subquery materializes a temporary table and triggers a semi‑join that forces full table scans, but disabling the semi‑join optimizer or rewriting the query restores sub‑second performance.

MySQLSQL optimizationSemi-Join
0 likes · 8 min read
Why MySQL IN Subqueries Turn Into Slow Full Scans—and How to Fix Them
dbaplus Community
dbaplus Community
Sep 12, 2022 · Databases

How to Resolve Deep Pagination Performance Problems in MySQL

This article walks through a real‑world incident caused by MySQL deep pagination, explains how the slow queries and CPU spikes were diagnosed, and presents several concrete optimization techniques—including ID‑based queries, range scans, sub‑queries and cursor‑based pagination—culminating in a stable production fix.

Deep PaginationMySQLSQL optimization
0 likes · 10 min read
How to Resolve Deep Pagination Performance Problems in MySQL
DaTaobao Tech
DaTaobao Tech
Sep 6, 2022 · Big Data

SQL Optimization Techniques for ODPS (Open Data Processing Service)

The article presents practical ODPS SQL optimization strategies—including explicit column selection, partition limiting, multi‑insert, proper handling of nulls, join‑type choices, map‑join and skew hints, bucketed tables, and tuned task parameters—illustrated with three real‑world cases that dramatically cut execution time and resource usage.

Data SkewHiveODPS
0 likes · 23 min read
SQL Optimization Techniques for ODPS (Open Data Processing Service)
dbaplus Community
dbaplus Community
Aug 14, 2022 · Databases

Master MySQL Performance: 12 Proven SQL Optimization Techniques

This article explains MySQL's internal architecture and provides twelve practical SQL optimization strategies—including avoiding subqueries, using IN instead of OR, efficient pagination, minimizing ORDER BY, batch inserts, selective column retrieval, proper JOIN handling, and index best practices—to dramatically improve database query performance.

IndexingMySQLQuery Tuning
0 likes · 20 min read
Master MySQL Performance: 12 Proven SQL Optimization Techniques
dbaplus Community
dbaplus Community
Aug 7, 2022 · Databases

Mastering MySQL Performance: Practical Strategies for Slow‑SQL Optimization

This comprehensive guide examines why slow SQL queries degrade system performance, outlines key factors such as data volume, access patterns, and processing methods, and provides concrete optimization techniques, case studies, index design principles, and advanced features like MRR and index push‑down for MySQL‑InnoDB.

InnoDBMySQLQuery Planning
0 likes · 14 min read
Mastering MySQL Performance: Practical Strategies for Slow‑SQL Optimization
Liangxu Linux
Liangxu Linux
Jul 28, 2022 · Databases

Why Your MySQL Queries Are Slow and How to Fix Common Mistakes

This article examines frequent MySQL performance pitfalls—such as misuse of LIMIT, implicit type conversion, sub‑query updates, mixed sorting, EXISTS clauses, and condition push‑down failures—and demonstrates how rewriting queries with proper indexes, JOINs, early range reduction, and WITH statements can dramatically reduce execution time.

MySQLQuery RewriteSQL optimization
0 likes · 15 min read
Why Your MySQL Queries Are Slow and How to Fix Common Mistakes
Top Architect
Top Architect
Jul 22, 2022 · Databases

Why and How to Optimize Slow SQL Queries in MySQL

The article explains the impact of slow SQL on system resources, outlines priorities for addressing performance bottlenecks, describes MySQL's execution process, shows how to enable and analyze slow‑query logs, and provides practical SQL optimization techniques such as avoiding subqueries, using IN, and eliminating unnecessary ORDER BY clauses.

MySQLSQL optimizationdatabase performance
0 likes · 10 min read
Why and How to Optimize Slow SQL Queries in MySQL
Architecture Digest
Architecture Digest
Jul 18, 2022 · Databases

Why MySQL IN Subquery with Semi‑Join Is Slow and How to Optimize It

The article analyzes a slow MySQL query that uses an IN subquery on a massive user table, explains how the optimizer’s semi‑join and materialized temporary tables cause full‑table scans, and demonstrates practical fixes such as disabling the semi‑join optimizer switch or rewriting the SQL to regain index usage.

MySQLSQL optimizationSemi-Join
0 likes · 8 min read
Why MySQL IN Subquery with Semi‑Join Is Slow and How to Optimize It
Programmer DD
Programmer DD
Jul 15, 2022 · Databases

Boost MySQL Performance: 8 Common SQL Pitfalls and How to Fix Them

This article examines frequent MySQL performance problems—such as inefficient LIMIT usage, implicit type conversion, suboptimal joins, mixed ordering, EXISTS subqueries, condition pushdown limitations, early range reduction, and intermediate result handling—and provides concrete rewrites and execution‑plan analyses that dramatically improve query speed.

MySQLQuery RewritingSQL optimization
0 likes · 16 min read
Boost MySQL Performance: 8 Common SQL Pitfalls and How to Fix Them
Aikesheng Open Source Community
Aikesheng Open Source Community
Jul 11, 2022 · Databases

Avoiding Global Mutex Contention in MySQL SHOW PROCESSLIST with performance_schema_show_processlist

This article explains how the default MySQL SHOW PROCESSLIST command can create a global mutex that slows down busy systems, demonstrates the problem with slow INSERTs reproduced via mysqlslap, and shows how enabling the performance_schema_show_processlist variable in MySQL 8.0.22+ eliminates the contention while providing best‑practice recommendations.

MySQLSHOW PROCESSLISTSQL optimization
0 likes · 6 min read
Avoiding Global Mutex Contention in MySQL SHOW PROCESSLIST with performance_schema_show_processlist
Liangxu Linux
Liangxu Linux
May 25, 2022 · Databases

Understanding MySQL Internal vs External Temporary Tables and Their Storage Engines

This article explains the two kinds of MySQL temporary tables—external and internal—covers the scenarios that trigger their creation, details how MySQL chooses MEMORY, MyISAM or InnoDB storage engines based on system variables and data size, and describes indexing, hash‑field fallbacks, and optimization hints such as SQL_SMALL_RESULT and SQL_BIG_RESULT.

MySQLSQL optimizationTemporary tables
0 likes · 22 min read
Understanding MySQL Internal vs External Temporary Tables and Their Storage Engines
Selected Java Interview Questions
Selected Java Interview Questions
Apr 25, 2022 · Databases

MySQL SQL and Index Optimization Techniques

This article provides a comprehensive guide to optimizing MySQL queries and indexes, covering best practices such as avoiding subqueries, using IN instead of OR, proper LIMIT usage, eliminating unnecessary ORDER BY, leveraging UNION ALL, batch inserts, selective column retrieval, distinguishing IN and EXISTS, improving GROUP BY, using numeric fields, optimizing JOINs, and applying index rules like left‑most prefix, avoiding functions on indexed columns, handling type conversions, covering indexes, and effective use of composite and prefix indexes.

IndexingSQL optimizationdatabase
0 likes · 19 min read
MySQL SQL and Index Optimization Techniques
Programmer DD
Programmer DD
Mar 31, 2022 · Databases

Mastering MySQL: Proven Steps to Diagnose and Optimize Slow Queries

This guide walks through systematic MySQL query optimization—from identifying slow statements via logs and EXPLAIN analysis, to profiling, tracing, and applying targeted fixes such as index tuning, query rewrites, and handling special cases like left‑most matching, implicit conversion, large pagination, and IN‑order‑by pitfalls.

MySQLPerformanceQuery Profiling
0 likes · 13 min read
Mastering MySQL: Proven Steps to Diagnose and Optimize Slow Queries
Open Source Linux
Open Source Linux
Mar 29, 2022 · Databases

Essential MySQL SQL Optimization Tips to Speed Up Your Queries

This guide presents a comprehensive set of MySQL optimization techniques—including proper use of EXPLAIN, limiting IN clause size, avoiding SELECT *, using LIMIT 1, minimizing sorting, replacing OR with UNION ALL, preferring UNION ALL over UNION, eliminating ORDER BY RAND(), distinguishing IN versus EXISTS, applying efficient pagination, segmenting large scans, avoiding NULL checks and leading wildcards, rewriting expressions, preventing implicit type conversion, respecting composite index leftmost rules, forcing indexes, handling range queries, and optimizing JOINs—to dramatically improve query performance.

EXPLAINIndexingJOIN
0 likes · 11 min read
Essential MySQL SQL Optimization Tips to Speed Up Your Queries
Java High-Performance Architecture
Java High-Performance Architecture
Mar 24, 2022 · Databases

How to Efficiently Query Millions of Rows in MySQL: Pagination Tricks and Optimizations

This article walks through generating a 10‑million‑row MySQL table, inserting data with a fast batch script, measuring plain LIMIT pagination performance, and applying several optimization techniques—including sub‑queries, ID‑range filtering, and column selection—to dramatically reduce query time on large data sets.

Batch InsertMySQLPerformance
0 likes · 9 min read
How to Efficiently Query Millions of Rows in MySQL: Pagination Tricks and Optimizations
Java Backend Technology
Java Backend Technology
Mar 23, 2022 · Databases

Master MySQL Performance: 10 Proven SQL Optimization Techniques

This article walks through why SQL queries become bottlenecks as data grows and presents a step‑by‑step guide—including slow‑query analysis, EXPLAIN interpretation, profiling, optimizer tracing, index tuning, and scenario‑specific tricks—to dramatically improve MySQL performance.

Explain PlanMySQLSQL optimization
0 likes · 12 min read
Master MySQL Performance: 10 Proven SQL Optimization Techniques
dbaplus Community
dbaplus Community
Mar 20, 2022 · Databases

Master MySQL Performance: Step-by-Step SQL Optimization Techniques

This guide walks through essential MySQL performance tuning methods, from identifying slow queries via logs and EXPLAIN analysis to interpreting Extra information, using profiling and optimizer trace, and applying concrete index, query, and schema adjustments for common scenarios such as left‑most matching, implicit conversion, large pagination, IN‑order‑by, and complex queries.

EXPLAINMySQLSQL optimization
0 likes · 12 min read
Master MySQL Performance: Step-by-Step SQL Optimization Techniques
ITPUB
ITPUB
Mar 14, 2022 · Databases

Master MySQL Performance: Practical SQL, Index, Join, and Hardware Tuning

This article provides a comprehensive guide to MySQL performance improvement, covering pagination, index, join, sorting, UNION, slow‑query‑log, schema design, and hardware optimizations with concrete SQL examples, best‑practice tips, and cautionary notes for real‑world deployments.

IndexingMySQLSQL optimization
0 likes · 16 min read
Master MySQL Performance: Practical SQL, Index, Join, and Hardware Tuning
Xianyu Technology
Xianyu Technology
Mar 2, 2022 · Databases

Optimizing a Slow SQL Query with AnalyticDB and Data Synchronization Strategies

By migrating a latency‑heavy MySQL query to AnalyticDB, employing binlog‑based real‑time sync, refining composite indexes, adding clustering and filter hints, and batching inserts, the team cut execution time from about two seconds to roughly one hundred milliseconds, achieving over 99 % of queries under one second.

AnalyticDBIndexingMySQL
0 likes · 12 min read
Optimizing a Slow SQL Query with AnalyticDB and Data Synchronization Strategies
Alimama Tech
Alimama Tech
Feb 16, 2022 · Databases

Optimizing Hologres Data Tables and Queries for Alibaba Advertising Inventory Management

By redesigning Hologres tables with column orientation, shard‑controlled Table Groups, distribution and clustering keys, adding bitmap indexes, refreshing statistics, caching external data, and tuning optimizer join order and resource scaling, Alibaba’s Mom advertising inventory system cut query latency by up to 35 % and memory use by 98 %, achieving a 5‑10× performance boost.

AdvertisingHologresSQL optimization
0 likes · 21 min read
Optimizing Hologres Data Tables and Queries for Alibaba Advertising Inventory Management
dbaplus Community
dbaplus Community
Jan 3, 2022 · Databases

What Happens When a Single Bad SQL Slows Down an Entire Data Warehouse? Real Cases & Fixes

This article presents four real‑world Oracle SQL cases—from a Cartesian join that halted nightly reports to a varchar primary key causing full scans, non‑standard date syntax breaking partition pruning, and missing statistics leading to unstable plans—detailing analysis, execution‑plan insights, and concrete remediation steps.

IndexingOracleSQL optimization
0 likes · 19 min read
What Happens When a Single Bad SQL Slows Down an Entire Data Warehouse? Real Cases & Fixes
Big Data Technology & Architecture
Big Data Technology & Architecture
Dec 21, 2021 · Big Data

Understanding Spark 3.0 Adaptive Query Execution (AQE) and Dynamic Partition Pruning (DPP)

This article explains the two most important Spark 3.0 features—Adaptive Query Execution and Dynamic Partition Pruning—detailing how AQE dynamically optimizes join strategies, partition coalescing, and skew handling, while DPP reduces I/O by pruning irrelevant fact‑table partitions at runtime.

Dynamic Partition PruningSQL optimizationSpark
0 likes · 10 min read
Understanding Spark 3.0 Adaptive Query Execution (AQE) and Dynamic Partition Pruning (DPP)
Architect's Tech Stack
Architect's Tech Stack
Dec 16, 2021 · Databases

Optimizing MyBatis Batch Inserts with ExecutorType.BATCH and foreach Size Limits

This article explains why MyBatis batch inserts using a foreach loop can become extremely slow when inserting thousands of rows, analyzes the underlying cost of parsing large prepared statements, and presents practical solutions such as using ExecutorType.BATCH, limiting foreach batch size, and employing JDBC batch execution to achieve sub‑second performance.

Batch InsertExecutorType.BATCHJava
0 likes · 8 min read
Optimizing MyBatis Batch Inserts with ExecutorType.BATCH and foreach Size Limits
Senior Brother's Insights
Senior Brother's Insights
Nov 17, 2021 · Databases

How I Fixed Persistent Account Balance Mismatches with Pessimistic Locks and Hibernate

After repeatedly encountering account balance discrepancies in a financial system, I traced the issue to improper use of pessimistic locks and Hibernate caching, then resolved it by correcting lock placement, optimizing SQL queries, and switching to native SQL, sharing the detailed debugging steps and lessons learned.

ConcurrencyHibernateMySQL
0 likes · 13 min read
How I Fixed Persistent Account Balance Mismatches with Pessimistic Locks and Hibernate
Big Data Technology Architecture
Big Data Technology Architecture
Nov 16, 2021 · Big Data

Understanding Adaptive Query Execution and Dynamic Partition Pruning in Apache Spark 3.0

This article explains how Apache Spark 3.0 improves SQL workload performance through Adaptive Query Execution (AQE) and Dynamic Partition Pruning (DPP), detailing their design principles, runtime optimizations, configuration parameters, and practical examples that demonstrate reduced shuffle partitions, smarter join strategies, and handling of data skew.

Dynamic Partition PruningSQL optimizationSpark
0 likes · 9 min read
Understanding Adaptive Query Execution and Dynamic Partition Pruning in Apache Spark 3.0
Big Data Technology & Architecture
Big Data Technology & Architecture
Oct 23, 2021 · Big Data

Understanding Hive Execution Engines: MapReduce, Tez, and Spark – Principles, Optimization, and Explain Usage

This article provides a comprehensive overview of Hive's execution engines—including MapReduce, Tez, and Spark—detailing their architectures, the six-stage Hive SQL compilation process, practical Explain syntax examples, and extensive tuning parameters for each engine to improve performance in big‑data environments.

EXPLAINHiveMapReduce
0 likes · 48 min read
Understanding Hive Execution Engines: MapReduce, Tez, and Spark – Principles, Optimization, and Explain Usage
Java Interview Crash Guide
Java Interview Crash Guide
Oct 1, 2021 · Databases

Why MySQL Queries Slow Down and How to Optimize with Indexes

The article explains why a simple GROUP BY query on a large user‑view table can become inefficient due to temporary tables, filesort, and memory allocation, demonstrates the execution steps with EXPLAIN, and shows how adding a composite index on user_id, viewed_user_age, and viewed_user_sex eliminates temporary tables and improves performance.

IndexingMySQLPerformance
0 likes · 16 min read
Why MySQL Queries Slow Down and How to Optimize with Indexes
Top Architect
Top Architect
Sep 10, 2021 · Databases

Optimizing MySQL LIMIT Queries by Reducing Unnecessary Row Scans

This article explains why using a large OFFSET with MySQL LIMIT can cause severe performance degradation, demonstrates the problem with a real‑world 9.5 million‑row table, and shows how rewriting the query with a sub‑query that selects only primary keys can cut execution time from seconds to milliseconds.

InnoDBLIMITMySQL
0 likes · 8 min read
Optimizing MySQL LIMIT Queries by Reducing Unnecessary Row Scans
Top Architect
Top Architect
Aug 29, 2021 · Databases

MySQL Query Optimization Techniques and Common Pitfalls

This article presents a comprehensive guide to improving MySQL query performance by addressing common issues such as inefficient LIMIT usage, implicit type conversion, sub‑query updates, mixed sorting, EXISTS clauses, condition push‑down, early range reduction, intermediate result push‑down, and demonstrates how using WITH can simplify complex statements.

MySQLQuery TuningSQL optimization
0 likes · 13 min read
MySQL Query Optimization Techniques and Common Pitfalls
21CTO
21CTO
Aug 16, 2021 · Databases

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

This article explains why MySQL queries using LIMIT with a large offset become extremely slow, demonstrates the inefficiency through a 950‑million‑row table example, and shows how rewriting the query with a sub‑select join reduces execution time from seconds to milliseconds while preserving results.

Buffer PoolInnoDBLIMIT OFFSET
0 likes · 9 min read
Why LIMIT with Large Offsets Slows MySQL and How to Fix It
dbaplus Community
dbaplus Community
Aug 11, 2021 · Databases

Turn Slow MySQL Queries into Millisecond Performance with Smart Rewrites

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

CTEJOINMySQL
0 likes · 13 min read
Turn Slow MySQL Queries into Millisecond Performance with Smart Rewrites
ITPUB
ITPUB
Jul 28, 2021 · Databases

Master MySQL Performance: Essential SQL Optimization Techniques & Index Strategies

This comprehensive guide walks you through MySQL performance tuning, covering how to monitor server status, identify slow queries, use EXPLAIN for execution plans, create and manage various index types, and apply practical SQL optimizations such as import tricks, insert batching, efficient GROUP BY/ORDER BY, and pagination improvements.

EXPLAINMySQLSQL optimization
0 likes · 25 min read
Master MySQL Performance: Essential SQL Optimization Techniques & Index Strategies
macrozheng
macrozheng
Jul 8, 2021 · Backend Development

Why Legacy Code Is Killing Your Productivity—and How to Fix It

The article shares painful real‑world cases of poorly written SQL and Java code, explains how tangled microservice dependencies and missing transactions cripple maintainability, and offers concrete guidelines for clean architecture, modular design, and effective code governance to boost team efficiency.

SQL optimizationcode qualitysoftware architecture
0 likes · 8 min read
Why Legacy Code Is Killing Your Productivity—and How to Fix It
Java Architect Essentials
Java Architect Essentials
Jun 18, 2021 · Databases

How to Supercharge MySQL Queries: 8 Proven Optimization Techniques

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

Condition PushdownJOINMySQL
0 likes · 14 min read
How to Supercharge MySQL Queries: 8 Proven Optimization Techniques
Java Architect Essentials
Java Architect Essentials
May 11, 2021 · Databases

Comprehensive MySQL Query Optimization and Best Practices

This article presents an extensive collection of MySQL performance‑tuning techniques, covering index design, query rewriting, use of UNION and EXISTS, temporary tables, NOLOCK hints, join limits, backup strategies, storage engine choices, data‑type selection, and other practical tips to improve query efficiency and overall database reliability.

MySQLSQL optimizationdatabase design
0 likes · 18 min read
Comprehensive MySQL Query Optimization and Best Practices
Programmer DD
Programmer DD
Apr 25, 2021 · Databases

Mastering MySQL: Proven Steps to Optimize Slow Queries

This article outlines a systematic approach to identifying and fixing inefficient MySQL queries, covering slow‑query detection, EXPLAIN analysis, profiling, optimizer tracing, and practical case studies that demonstrate index tuning, query rewriting, and handling large‑scale pagination and complex conditions.

MySQLProfilingSQL optimization
0 likes · 10 min read
Mastering MySQL: Proven Steps to Optimize Slow Queries
Architecture Digest
Architecture Digest
Mar 21, 2021 · Databases

MySQL Interview Essentials: Indexes, Transactions, Storage Engines, and Schema Design

This article compiles a comprehensive set of MySQL interview questions covering index structures, B‑tree vs. hash trade‑offs, primary‑key and clustered‑index design, ACID properties, isolation levels, lock types, storage‑engine choices, data‑type considerations, binlog formats, pagination strategies, sharding, stored procedures, normalization, and MyBatis parameter syntax.

MySQLSQL optimizationStorage Engines
0 likes · 20 min read
MySQL Interview Essentials: Indexes, Transactions, Storage Engines, and Schema Design
DataFunTalk
DataFunTalk
Feb 14, 2021 · Big Data

Impala at NetEase: Architecture, Iceberg Integration, Management System, Optimizations and Future Roadmap

This talk presents NetEase's practical experience with Impala, covering its core architecture, new features in version 3.x, integration with Apache Iceberg, a custom management platform, profiling and statistics enhancements, as well as future plans involving Kubernetes, Alluxio caching and pre‑computation strategies.

Apache IcebergImpalaSQL optimization
0 likes · 13 min read
Impala at NetEase: Architecture, Iceberg Integration, Management System, Optimizations and Future Roadmap
Huawei Cloud Developer Alliance
Huawei Cloud Developer Alliance
Jan 26, 2021 · Backend Development

Master MyBatis: Common Return Values, Exceptions, and SQL Optimization Tips

This article shares practical MyBatis tips, detailing what different return types yield when no records match, common exceptions you may encounter, transaction lock behavior with FOR UPDATE, and essential SQL optimization strategies such as using LIMIT, proper indexing, and avoiding inefficient patterns.

Java persistenceMyBatisSQL optimization
0 likes · 8 min read
Master MyBatis: Common Return Values, Exceptions, and SQL Optimization Tips
Laravel Tech Community
Laravel Tech Community
Jan 24, 2021 · Databases

MySQL Slow Query Optimization: Reducing Execution Time from 30 Seconds to 0.19 Seconds

This article documents a MySQL slow‑query case where a 5‑million‑row table took over 30 seconds to execute, explores several ineffective optimization attempts, uncovers a client‑side LIMIT issue, and finally resolves the performance problem by forcing the use of the idx_end_time index, cutting the runtime to under a second.

MySQLSQL optimizationdatabase
0 likes · 5 min read
MySQL Slow Query Optimization: Reducing Execution Time from 30 Seconds to 0.19 Seconds
DataFunTalk
DataFunTalk
Jan 22, 2021 · Big Data

Practical Experience of Apache Flink at ByteDance: Architecture, Optimizations, and Future Directions

This article presents ByteDance's real‑world use of Apache Flink, covering the platform's overall architecture, SQL extensions, custom connectors, UI‑driven SQL platform, performance optimizations such as window mini‑batch and custom windows, dimension‑table enhancements, checkpoint recovery improvements, stream‑batch integration, and upcoming roadmap items.

Apache FlinkByteDanceCheckpoint Recovery
0 likes · 15 min read
Practical Experience of Apache Flink at ByteDance: Architecture, Optimizations, and Future Directions
Programmer DD
Programmer DD
Dec 4, 2020 · Databases

Boost MySQL Performance: 8 Common SQL Pitfalls and How to Fix Them

This article examines frequent MySQL performance problems such as inefficient LIMIT usage, implicit type conversion, sub‑query updates, mixed sorting, EXISTS clauses, condition push‑down, pre‑filtering, and intermediate result handling, and provides rewritten SQL examples that dramatically reduce execution time.

MySQLQuery TuningSQL optimization
0 likes · 14 min read
Boost MySQL Performance: 8 Common SQL Pitfalls and How to Fix Them
Big Data Technology & Architecture
Big Data Technology & Architecture
Dec 3, 2020 · Big Data

Hive Query Optimization Techniques and Best Practices

This article presents a comprehensive guide to optimizing Hive queries, covering limit adjustments, join strategies, local mode execution, parallelism, strict mode, mapper and reducer tuning, JVM reuse, dynamic partitioning, speculative execution, data skew handling, and small‑file mitigation techniques.

HiveMapReduceSQL optimization
0 likes · 20 min read
Hive Query Optimization Techniques and Best Practices
ITPUB
ITPUB
Nov 30, 2020 · Databases

How to Fix Common MySQL Performance Pitfalls: Real-World SQL Optimizations

This article examines frequent MySQL performance problems such as inefficient LIMIT usage, implicit type conversion, subquery updates, mixed ordering, EXISTS clauses, predicate push‑down, early row reduction, and intermediate result set handling, providing concrete rewrites and execution‑plan comparisons that dramatically improve query speed.

MySQLQuery PlanningSQL optimization
0 likes · 14 min read
How to Fix Common MySQL Performance Pitfalls: Real-World SQL Optimizations
Full-Stack Internet Architecture
Full-Stack Internet Architecture
Nov 6, 2020 · Backend Development

Common Backend Performance Optimization Techniques

The article outlines practical backend performance optimization methods such as SQL tuning, eliminating duplicate calls, on‑demand queries, parallel execution, caching, asynchronous processing, JVM parameter tweaks, and horizontal scaling, providing code examples and actionable advice for developers.

CachingJVM TuningPerformance
0 likes · 8 min read
Common Backend Performance Optimization Techniques
Laravel Tech Community
Laravel Tech Community
Oct 28, 2020 · Databases

Common MySQL Query Performance Pitfalls and Their Optimizations

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

MySQLSQL optimizationdatabase
0 likes · 12 min read
Common MySQL Query Performance Pitfalls and Their Optimizations
Open Source Linux
Open Source Linux
Oct 14, 2020 · Databases

100 MySQL Interview Questions: Indexes, Transactions, and Storage Engines Explained

This article presents a curated list of 100 essential MySQL interview questions covering indexes, transaction fundamentals, storage engine differences, table design, query optimization, and common pitfalls, providing concise explanations and practical examples to deepen developers’ understanding and prepare them for technical interviews.

MySQLSQL optimizationStorage Engines
0 likes · 23 min read
100 MySQL Interview Questions: Indexes, Transactions, and Storage Engines Explained
Java Captain
Java Captain
Sep 26, 2020 · Databases

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

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

JOINMySQLSQL optimization
0 likes · 14 min read
MySQL Performance Optimization Techniques: LIMIT, Implicit Conversion, Join Rewrite, Mixed Sorting, EXISTS, Condition Pushdown, and More
Alibaba Cloud Developer
Alibaba Cloud Developer
Sep 23, 2020 · Databases

How TDDL Revolutionized Database Scaling at Alibaba: Lessons from 2007

This article recounts the 2007 challenges faced by Taobao's monolithic MySQL setup and explains how the TDDL middleware introduced SQL optimization, sharding, read‑write separation, caching, and dynamic scaling to transform the system into a distributed, high‑performance database solution.

Read-Write SeparationSQL optimizationTDDL
0 likes · 16 min read
How TDDL Revolutionized Database Scaling at Alibaba: Lessons from 2007
Big Data Technology & Architecture
Big Data Technology & Architecture
Sep 16, 2020 · Databases

Optimizing a Complex MySQL Slow Query for Article Comments

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

MySQLSQL optimizationbig data
0 likes · 13 min read
Optimizing a Complex MySQL Slow Query for Article Comments
dbaplus Community
dbaplus Community
Sep 8, 2020 · Databases

Achieving Billion‑Row Second‑Level Queries with ClickHouse Real‑Time Engine

JD’s Algorithmic Intelligence team built a ClickHouse‑based real‑time analytics engine that ingests Kafka and offline data, uses MergeTree tables with strategic partitioning and sorting, and employs batch writes, materialized views, and monitoring to achieve second‑level queries over billions of rows.

ClickHouseMaterialized ViewMergeTree
0 likes · 17 min read
Achieving Billion‑Row Second‑Level Queries with ClickHouse Real‑Time Engine
ITPUB
ITPUB
Aug 31, 2020 · Databases

Mastering MySQL: Keys, Indexes, Transactions, Storage Engines, and Optimization Explained

This comprehensive guide covers MySQL fundamentals—including primary, foreign, candidate, and super keys—auto‑increment primary keys, triggers, stored procedures, views, cursor usage, index types and design, transaction properties and isolation levels, storage engine differences, query execution order, EXPLAIN analysis, lock mechanisms, replication strategies, high‑concurrency solutions, and crash‑recovery via REDO and UNDO logs, providing practical examples and code snippets for each concept.

Database fundamentalsMySQLSQL optimization
0 likes · 52 min read
Mastering MySQL: Keys, Indexes, Transactions, Storage Engines, and Optimization Explained
Java Backend Technology
Java Backend Technology
Aug 11, 2020 · Databases

How I Turned a Half‑Hour SSRS Report into a Sub‑Second Query with a Stored Procedure

The author describes how they transformed a sluggish SSRS report that took over thirty minutes to run into a fast, sub‑second query by analyzing the original SQL, adding missing indexes, avoiding full table scans, and rewriting the logic as a flexible stored procedure, complete with code examples and performance tips.

MESSQL optimizationSSRS
0 likes · 24 min read
How I Turned a Half‑Hour SSRS Report into a Sub‑Second Query with a Stored Procedure
Big Data Technology Architecture
Big Data Technology Architecture
Aug 8, 2020 · Big Data

Overview of SQL Performance Improvements in Apache Spark 3.0

Apache Spark 3.0 introduces extensive SQL performance enhancements, including a new explain format, expanded join hints, adaptive query execution, dynamic partition pruning, enhanced nested column pruning, improved aggregation code generation, and support for newer Scala and Java versions, all aimed at optimizing query planning and execution.

Apache SparkSQL optimizationadaptive query execution
0 likes · 14 min read
Overview of SQL Performance Improvements in Apache Spark 3.0
Big Data Technology Architecture
Big Data Technology Architecture
Aug 5, 2020 · Big Data

Understanding Join Execution in Spark SQL

This article explains how Spark SQL processes joins—including inner, outer, semi, and anti joins—by describing the overall query planning flow, the three physical join strategies (sort‑merge, broadcast, and hash), and the specific implementation details for each join type.

DataFramesDistributed ComputingJOIN
0 likes · 10 min read
Understanding Join Execution in Spark SQL
Programmer DD
Programmer DD
Jul 27, 2020 · Databases

Boost MySQL Performance: Bulk Insert, GROUP BY, ORDER BY, and OR Query Optimizations

This article presents practical MySQL performance tricks, covering bulk data insertion with DISABLE/ENABLE KEYS, loading data in primary‑key order, multi‑value INSERTs, INSERT DELAYED, separating index and data files, GROUP BY sorting elimination, index‑driven ORDER BY, OR‑condition indexing, replacing sub‑queries with JOINs, and using SQL hints such as USE, IGNORE, and FORCE INDEX.

Bulk InsertIndexingMySQL
0 likes · 14 min read
Boost MySQL Performance: Bulk Insert, GROUP BY, ORDER BY, and OR Query Optimizations
macrozheng
macrozheng
Jul 14, 2020 · Databases

Why SELECT * Slows Down MySQL: Deep Dive into Indexes and Optimization

This article explains why using SELECT * in MySQL queries degrades performance, covering increased parsing cost, unnecessary data transfer, loss of covering index optimization, and how proper column selection and index design can dramatically improve query speed.

Covering IndexSELECTSQL optimization
0 likes · 10 min read
Why SELECT * Slows Down MySQL: Deep Dive into Indexes and Optimization
Aikesheng Open Source Community
Aikesheng Open Source Community
Jun 30, 2020 · Databases

MySQL View Optimization: Fixing Slow Query Caused by Character Set Conversion

This article analyzes a slow MySQL view query caused by mismatched character sets, demonstrates how the automatic charset conversion prevents index usage, and shows step‑by‑step how altering the table charset and removing the conversion restores index usage and reduces execution time from seconds to fractions of a second.

MySQLSQL optimizationindex
0 likes · 7 min read
MySQL View Optimization: Fixing Slow Query Caused by Character Set Conversion
Laravel Tech Community
Laravel Tech Community
Jun 22, 2020 · Databases

MySQL Optimization Tips Frequently Asked in Interviews

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

IndexingMySQLSQL optimization
0 likes · 10 min read
MySQL Optimization Tips Frequently Asked in Interviews
Programmer DD
Programmer DD
Jun 18, 2020 · Databases

How to Optimize MySQL Joins for Million‑Row Tables in Under 2 Seconds

This article explains how to use EXPLAIN to analyze MySQL join queries, identifies causes of temporary tables and filesorts, and provides practical techniques—including driver‑table selection, STRAIGHT_JOIN usage, and index strategies—to optimize two‑ and multi‑table joins so that million‑row results return within two seconds.

EXPLAINJOINMySQL
0 likes · 8 min read
How to Optimize MySQL Joins for Million‑Row Tables in Under 2 Seconds
Big Data Technology & Architecture
Big Data Technology & Architecture
Jun 15, 2020 · Big Data

Hive Optimization Techniques and Best Practices for Big Data Processing

This article provides a comprehensive guide to improving Hive query performance by covering column and partition pruning, predicate pushdown, replacing ORDER BY with SORT BY, using GROUP BY instead of DISTINCT, tuning MapReduce jobs, handling data skew in joins, and selecting appropriate storage formats for large‑scale data warehouses.

Data SkewHiveHiveQL
0 likes · 19 min read
Hive Optimization Techniques and Best Practices for Big Data Processing
Laravel Tech Community
Laravel Tech Community
Jun 11, 2020 · Databases

MySQL Performance Optimization Techniques: Bulk Inserts, INSERT, GROUP BY, ORDER BY, OR, Nested Queries, and SQL Hints

This article presents a comprehensive guide to MySQL performance tuning, covering bulk‑insert optimizations, efficient INSERT statements, GROUP BY and ORDER BY improvements, OR‑condition handling, nested query replacement, and the use of SQL hints such as USE INDEX, IGNORE INDEX, and FORCE INDEX, with practical code examples and benchmark results.

Bulk InsertMySQLQuery Tuning
0 likes · 13 min read
MySQL Performance Optimization Techniques: Bulk Inserts, INSERT, GROUP BY, ORDER BY, OR, Nested Queries, and SQL Hints
Selected Java Interview Questions
Selected Java Interview Questions
Jun 5, 2020 · Databases

MySQL Query Optimization Techniques and Common Pitfalls

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

JOINLIMITMySQL
0 likes · 13 min read
MySQL Query Optimization Techniques and Common Pitfalls
macrozheng
macrozheng
Jun 3, 2020 · Databases

Master MySQL EXPLAIN: Decode Execution Plans for Faster Queries

This article explains the purpose of the MySQL EXPLAIN command, details each column in its output (id, select_type, table, type, possible_keys, etc.), provides real‑world SQL examples, and shows how to interpret the plan to identify indexing, join, and sorting issues for query optimization.

EXPLAINMySQLQuery Plan
0 likes · 18 min read
Master MySQL EXPLAIN: Decode Execution Plans for Faster Queries
Java Captain
Java Captain
May 10, 2020 · Databases

MySQL Performance Optimization and Best Practices

This article shares practical MySQL performance optimization techniques, covering table size limits, connection settings, data type choices, index design, query rewriting, pagination, and batch processing, with code examples and actionable guidelines to improve query speed and scalability.

IndexingMySQLPerformance
0 likes · 13 min read
MySQL Performance Optimization and Best Practices
Meituan Technology Team
Meituan Technology Team
Apr 9, 2020 · Big Data

Dual-Engine MOLAP + ROLAP Architecture with Apache Doris for Meituan Takeaway Data Warehouse

Meituan Takeaway’s data warehouse combines Apache Kylin’s MOLAP cubes for stable dimensions with Apache Doris’s MPP‑driven ROLAP engine to handle changing dimensions, detail queries, and near‑real‑time analytics, achieving millisecond‑level responses, reduced storage/compute costs, and simplifying operations across diverse analytical workloads.

Apache DorisMOLAPROLAP
0 likes · 18 min read
Dual-Engine MOLAP + ROLAP Architecture with Apache Doris for Meituan Takeaway Data Warehouse
Full-Stack Internet Architecture
Full-Stack Internet Architecture
Mar 29, 2020 · Databases

How to Locate and Analyze Slow SQL Queries in MySQL

This article explains how to identify slow MySQL queries using the slow‑query log and SHOW PROCESSLIST, configure logging parameters, and analyze query performance with EXPLAIN, covering key output fields, common values, and practical code examples for effective SQL optimization.

EXPLAINMySQLSQL optimization
0 likes · 14 min read
How to Locate and Analyze Slow SQL Queries in MySQL
Big Data Technology Architecture
Big Data Technology Architecture
Mar 19, 2020 · Big Data

Handling Data Skew in Hive: Join, Group By, and COUNT(DISTINCT) Optimizations

Data skew in Hive MapReduce jobs, caused by uneven key distribution during joins, group‑by, or COUNT(DISTINCT) operations, can severely slow tasks, and the article explains common scenarios and practical solutions such as using MapJoin, enabling map‑side aggregation, load‑balancing, and rewriting queries to mitigate skew.

Data SkewHiveMapJoin
0 likes · 7 min read
Handling Data Skew in Hive: Join, Group By, and COUNT(DISTINCT) Optimizations
HomeTech
HomeTech
Mar 11, 2020 · Big Data

Streaming SQL with Apache Flink: Theory, Platform Optimizations, and Real‑Time Use Cases

This article introduces Apache Flink's Streaming SQL, explains its theoretical foundations such as the table‑stream relationship and watermark semantics, describes the platform's practical enhancements—including source/sink wrappers, built‑in functions, and native Retract Stream support—and showcases several real‑time computation examples.

Apache FlinkDataStreamReal-Time Computing
0 likes · 31 min read
Streaming SQL with Apache Flink: Theory, Platform Optimizations, and Real‑Time Use Cases