Tagged articles
Query Performance
90 articles · Page 1 of 1
This article explains why database indexes accelerate queries by detailing disk storage mechanics, demonstrating binary search on 100,000 records reducing block scans from 20,000 to 14, covering clustered vs non-clustered indexes, index trade-offs, common failure cases like OR conditions, and practical SQL optimization techniques.
B+TreeInterview questionsQuery Performance
0 likes · 14 min read

This article explains why MySQL GROUP BY + ORDER BY queries become extremely slow on hundred-million-row tables even when EXPLAIN shows index usage, covering temporary tables, filesort, loose index scan limitations, scanning overhead, and solutions like pre-aggregated summary tables, partitioning, columnar engines, and query scope reduction.
ClickHouseIndex OptimizationMySQL
0 likes · 12 min read

This article explains how database indexes accelerate queries by leveraging sorted data structures and binary search, detailing disk I/O mechanics, clustered vs. non-clustered indexes, index trade-offs on write performance, and common SQL optimization pitfalls like OR conditions and leading wildcards that cause index invalidation.
MySQLQuery PerformanceSQL Optimization
0 likes · 13 min read

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

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

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

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

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

A MySQL query on a varchar column with an unquoted numeric literal triggered implicit type conversion, forcing a full scan of 2 million rows instead of an index lookup; adding quotes cut latency from 19 seconds to 50 ms. The article explains the mechanism, lists four other common index-invalidation patterns, and provides a quick EXPLAIN-based diagnosis checklist.
EXPLAINIndex OptimizationMySQL
0 likes · 7 min read

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

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

This comprehensive guide explains MySQL index fundamentals using a dictionary analogy, covers four essential index types with SQL examples, details six common optimization pitfalls that cause index failure, and demonstrates how to verify index usage with EXPLAIN and SHOW INDEX commands.
B+TreeComposite IndexEXPLAIN
0 likes · 18 min read

This article presents a comprehensive guide to advanced MySQL techniques, covering CTEs, window functions, and query optimization strategies, complete with step‑by‑step code examples, performance tips, and practical demonstrations such as ranking employees and creating materialized views.
CTEMySQLQuery Performance
0 likes · 9 min read

This guide walks developers through the fundamentals of database indexes, explaining why they matter, how they work, common pitfalls, and best‑practice design tips with concrete SQL examples and visual illustrations.
B+TreeCovering IndexDatabase Indexes
0 likes · 10 min read


ITPUBNov 22, 2025 · Databases
This article explains the purpose of MySQL's optimizer_switch prefer_ordering_index, shows why the default ON setting can hurt performance with skewed data, and demonstrates with code and EXPLAIN output how turning it OFF can lead to faster queries for ORDER BY and GROUP BY scenarios.
MySQLQuery Performanceindex usage
0 likes · 7 min read

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

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

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

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

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

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

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

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

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

This article walks through a MySQL performance case study, showing how a slow sub‑query can be accelerated by adding single‑column and composite indexes, rewriting the query with EXISTS or JOIN, and applying best‑practice indexing strategies such as covering indexes and left‑most prefix rules.
Index OptimizationMySQLQuery Performance
0 likes · 12 min read

NAVER migrated its massive analytics platform from ClickHouse to StarRocks, achieving dramatic improvements in multi‑table JOIN performance, real‑time aggregation speed, and horizontal scalability while simplifying data integration across heterogeneous sources on a Kubernetes‑based architecture.
ClickHouseKubernetesQuery Performance
0 likes · 13 min read

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

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

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

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

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

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

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

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

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

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

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

This article presents a comprehensive guide to database performance improvement, covering index tuning, query rewriting, proper table design, caching strategies, hardware choices, server parameter tweaks, monitoring, connection pooling, and data cleanup, all illustrated with practical examples and diagrams.
Query PerformanceTable Designindex tuning
0 likes · 9 min read

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

This article demonstrates how to dramatically improve MySQL query performance by analyzing execution plans, adding appropriate single‑column and multi‑column indexes, rewriting subqueries as joins, and applying covering indexes, showing step‑by‑step measurements and visual explanations.
IndexingMySQLQuery Performance
0 likes · 9 min read

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

This article explains how MySQL's optimizer calculates execution costs for different indexes, why it may select a full table scan instead of an apparently better index, and shows how to use EXPLAIN and optimizer trace to reveal and improve the chosen execution plan.
EXPLAINIndex OptimizationMySQL
0 likes · 11 min read

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

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

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

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

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

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

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

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

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

This article explains the evolution of data storage, the fundamentals of computer storage devices, how database indexes function like a book's table of contents, the role of binary search, the benefits and drawbacks of indexes, and practical SQL optimization techniques to enhance query speed.
IndexQuery PerformanceSQL Optimization
0 likes · 12 min read

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.
CTELIMITMySQL
0 likes · 13 min read

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

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.
MySQLQuery PerformanceSQL Optimization
0 likes · 18 min read

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.
Database DesignIndexingMySQL
0 likes · 18 min read

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.
MySQLQuery PerformanceSQL Optimization
0 likes · 14 min read

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

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

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

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

This article provides a comprehensive guide to MySQL index optimization, covering selectivity calculation, prefix length, composite index rules, EXPLAIN analysis, common slow‑query patterns, and practical sharding and partitioning techniques to improve query performance.
EXPLAINMySQLQuery Performance
0 likes · 20 min read

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

This article explains MySQL index fundamentals, different index structures, the concept of covering indexes and the left‑most prefix rule, and presents three real‑world optimization cases that illustrate how to design effective composite and partial indexes to avoid table scans and improve query performance.
Database DesignIndex OptimizationMySQL
0 likes · 14 min read

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.
CTEMySQLQuery Performance
0 likes · 13 min read

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 PushdownMySQLQuery Performance
0 likes · 14 min read

This article explains why MySQL queries become slow, how proper index design, MDL locks, sharding, read‑write separation, and the use of ElasticSearch or HBase can improve query performance in large‑scale systems, and provides practical tips and code examples for each technique.
HBaseIndexingQuery Performance
0 likes · 20 min read

This article examines a slow MySQL query that scans millions of rows, explains why the optimizer chose a low‑selectivity index, demonstrates how forcing the correct index dramatically improves performance, and shows how to create a covering composite index to resolve the issue permanently.
Covering IndexEXPLAIN PlanIndex Optimization
0 likes · 8 min read


ITPUBJan 19, 2021 · Databases
This article explains how database indexes improve query performance by exploring binary trees, binary search, balanced trees, B‑trees, and B+‑trees, illustrating their structures, advantages, disadvantages, and the impact of disk I/O on overall efficiency.
B+TreeB-treeQuery Performance
0 likes · 17 min read

This article walks through a MySQL 5.6 scenario where a query that originally took over 30,000 seconds is optimized using proper indexing, sub‑query to join conversion, and composite indexes, demonstrating dramatic performance gains and offering practical tips for future query tuning.
Index OptimizationMySQLQuery Performance
0 likes · 11 min read

This article provides a comprehensive guide to MySQL best practices, covering storage engine characteristics, optimal data type selection, index design strategies, and query‑performance tuning techniques for teams without dedicated DBAs.
MySQLQuery Performancedata types
0 likes · 17 min read

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.
MySQLQuery PerformanceSQL Optimization
0 likes · 12 min read

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.
MySQLQuery PerformanceSQL Optimization
0 likes · 14 min read

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.
MySQLQuery PerformanceSQL Optimization
0 likes · 13 min read

This guide introduces MySQL 8's Explain Analyze tool, demonstrates how to prepend EXPLAIN ANALYZE to a query, interprets its detailed output—including cost, actual time, rows, and loops—using a sample query on the Sakila database, and provides steps to set up the test environment.
EXPLAIN ANALYZEMySQLQuery Performance
0 likes · 5 min read

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.
IndexingMySQLQuery Performance
0 likes · 10 min read

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.
LIMITMySQLQuery Performance
0 likes · 13 min read

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

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

This article explains the functional and performance differences among MySQL's count(*), count(1), and count(column) expressions, illustrates how NULL values affect each, and provides a practical example with query results to guide optimal usage.
COUNTMySQLQuery Performance
0 likes · 5 min read

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

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

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


ITPUBJul 17, 2018 · Backend Development
This guide walks through common MySQL performance pitfalls, explains how to interpret EXPLAIN output, and demonstrates step‑by‑step index creation, composite and covering indexes, as well as force‑index usage to dramatically speed up order‑related queries in high‑volume systems.
Composite IndexCovering IndexEXPLAIN
0 likes · 18 min read


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

This article explains common MongoDB connection problems, authentication mechanisms, read‑preference tuning, index types and creation, query‑plan analysis, pagination techniques, and array handling, offering practical tips to improve performance and reliability for developers.
AuthenticationConnection ManagementIndex Optimization
0 likes · 9 min read


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


ITPUBJul 12, 2016 · Databases
This article walks through practical steps for identifying performance bottlenecks in PostgreSQL, selecting appropriate columns and index types, interpreting system statistics, and evaluating cost estimates with real‑world examples to dramatically reduce query latency.
GiST indexPostgreSQLQuery Performance
0 likes · 13 min read


21CTOAug 14, 2015 · Databases
This article shares practical Elasticsearch experience covering index and query performance optimization, shard routing strategies, JVM tuning, daily maintenance, and answers to common production questions, providing actionable guidance for building high‑availability search clusters.
ElasticsearchJVMQuery Performance
0 likes · 16 min read

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