This article presents five practical MySQL index usage questions, each with multiple‑choice options, followed by detailed answers and explanations that illustrate when an index is appropriate, how function usage affects it, and the impact on query performance.
Attending VLDB 2017 in Munich, the report summarizes the conference’s broad coverage of database research—from new hardware‑accelerated prototypes and Spark‑based big‑data processing to Oracle and SAP HANA case studies, keynotes, notable papers, and reflections on industry trends and Chinese contributions.
This article explains how PostgreSQL transforms and rewrites query trees, why a completed transform‑rewrite tree may not be optimal, and details the subquery_planner’s role, tuple_fraction handling, and the step‑by‑step logical optimization process used to generate efficient execution plans.
This article explains MySQL's logical architecture, the step‑by‑step query execution process, and provides practical performance‑tuning advice—including schema design, data‑type choices, index strategies, and specific query optimizations such as COUNT, JOIN, LIMIT pagination, and UNION—backed by code examples and diagrams.
This article outlines essential database design principles and a comprehensive set of query optimization techniques, covering row size limits, appropriate data types, field length choices, index usage, avoiding costly WHERE clause patterns, and best practices for temporary tables, transactions, and result set handling to boost SQL performance.
The article examines why a MySQL DELETE statement with a composite index on biz_date and status still triggers a full‑table scan, shows EXPLAIN output, tests forced index usage, and ultimately demonstrates that narrowing the date range in the query dramatically reduces rows examined and improves performance.
This article walks through diagnosing a sluggish Oracle search query with many optional filters, removing unnecessary joins and function calls, applying conditional MyBatis joins, and creating composite indexes to dramatically lower the execution plan cost from 13902 to under 100.
This guide outlines best practices for SQL Server database design, covering recommended field types, primary key and null policies, index creation rules, low‑selectivity column handling, and a comprehensive set of query‑writing conventions to improve performance and maintainability.
This article explains MySQL's logical architecture, query processing steps, client/server protocol, query cache behavior, parsing, optimizer cost model, execution engine, result delivery, and provides practical performance‑tuning advice on schema design, index creation, and specific query patterns.
This guide explains how MySQL decides whether to use an index for range queries, LIKE patterns, and ORDER BY operations, detailing key_len calculation, index key vs filter vs table filter concepts, and practical examples for BETWEEN and sorting optimization.
This article explains the inner workings of relational databases, covering their core components, query optimization flow, indexing, merge sort, transaction handling, concurrency control, and lock management, while illustrating concepts with examples and diagrams.
A colleague handed over a seemingly simple SQL Server query that ran in five seconds for early dates but never returned for later dates, and the article walks through the diagnosis, execution‑plan analysis, statistics update, and final fix that restored performance.
This article explains the logical and physical execution order of SQL statements, offers practical tips for returning only needed data, reducing redundant work, using temporary tables wisely, optimizing subqueries and indexes, and provides concrete code examples to improve query performance.
This article explains SQL Server's logical and physical query execution order, offers practical techniques such as converting loops to batch operations, using temporary tables, optimizing indexes, and avoiding common pitfalls, all illustrated with code samples and performance measurements.
This article explains SQL Server's logical query execution sequence, details the physical step‑by‑step processing, and provides practical optimization advice such as avoiding SELECT *, using proper indexes, handling temporary tables, rewriting subqueries, and safely managing transactions to improve performance and maintainability.
This article walks through common traps in Oracle's Cost‑Based Optimizer (CBO), explains why query transformations, FILTER operations, and inaccurate statistics can cripple performance, and provides practical solutions—including NULL‑AWARE anti‑joins, hint usage, SQLT tooling, and SQL rewrites—to dramatically speed up problematic queries.
This article explains SQL join types, the three normal forms, table creation and alteration commands, data integrity constraints, practical query‑optimization tips, the roles and differences of clustered and non‑clustered indexes, and the distinctions between stored procedures and functions.
This article provides practical guidelines for designing efficient database schemas and optimizing SQL queries, covering row size limits, data type choices, index usage, common pitfalls in WHERE clauses, and best practices for temporary tables and transaction handling to improve overall database performance.
This article presents five useful Oracle SQL techniques—including string concatenation, DISTINCT, WHERE clauses, flexible COUNT usage, and selecting only necessary columns—to improve query readability, eliminate duplicate rows, and enhance overall database performance.
This article describes Hulu’s real‑world OLAP query optimization, covering the fundamentals of OLAP, comparisons of row‑ and column‑based storage formats, detailed indexing mechanisms of Parquet, ORC and CarbonData, and the specific schema, shuffle, block size, speculation and GC tuning techniques that enabled CarbonData to dramatically accelerate wide‑table queries on SparkSQL.
This article presents a comprehensive collection of practical SQL performance tuning guidelines, covering index usage, query rewriting, avoiding costly operators, proper data types, efficient joins, and view creation to help developers write faster, more resource‑efficient database queries.
After a typhoon delayed work, the author tackled a 347‑line Oracle SQL that took over four minutes to return results, dissected its massive subqueries and views, applied three rounds of restructuring with WITH clauses and driver‑table tactics, and ultimately reduced the total runtime to just 3.5 seconds while dramatically speeding up deep data extraction.
This article examines a high‑volume Oracle query scenario, critiques the current multi‑IN approach, and presents three alternative solutions—including a single‑SQL join, a UNION ALL view, and the use of global temporary tables—detailing their performance implications and practical trade‑offs.
This article explains the various timeout scenarios in SQL Server‑backed web applications—including ASP.NET, IIS, WebService, SSMS, remote login, and linked‑server timeouts—provides diagnostic queries such as the Connectivity Ring Buffer, and offers concrete tuning steps and best‑practice recommendations to resolve and prevent them.
This guide presents a concise, experience‑based checklist for optimizing SQL SELECT statements, covering index verification, dataset reduction, column selection, unnecessary table removal, outer join elimination, and computed column cleanup, each illustrated with real‑world examples and visual aids.
This article demonstrates how InnoDB returns rows in primary‑key order during full‑table scans, while queries that use a secondary index follow the leaf‑node order of that index, and it compares performance implications of index‑only scans versus clustered‑index scans.
This article presents a comprehensive set of practical strategies for improving database query performance, covering index design, SQL statement refinements, Java backend considerations, hardware tuning, and storage‑procedure usage to accelerate operations on large‑scale relational databases.
This guide shows how to use Oracle 12c's DBMS_STATS.SEED_COL_USAGE and REPORT_COL_USAGE to automatically identify useful column groups, create extended statistics, and verify the impact on execution plans with concrete SQL examples.
This guide presents thirty practical SQL optimization techniques—ranging from index usage and avoiding costly operators to rewriting expressions and minimizing temp objects—to help developers prevent full table scans and boost database performance.
This guide explains how to set up MySQL master‑slave replication, configure binlog, create users, synchronize data, troubleshoot common issues, and apply query‑performance optimizations such as limiting result sets, using covering indexes, and refactoring complex joins.
This article explains the theory behind SQL Server's parallel execution, illustrates its performance gains with Amdahl's Law, lists operators that block parallelism, discusses configuration settings, warns of deadlocks and thread starvation, and presents practical MapReduce‑style optimizations for real‑world workloads.
A high‑load MySQL server was investigated, revealing that a seemingly simple subquery caused full table scans, but rewriting it as a JOIN or using an equality subquery enabled index usage, and newer MySQL versions have fixed the optimizer bug.
This comprehensive guide enumerates over forty common causes of slow SQL Server queries and provides detailed optimization techniques—including indexing strategies, hardware upgrades, partitioning, cursor handling, and configuration tweaks—to dramatically improve query performance and reduce resource bottlenecks.
This article analyzes the characteristics of log data, explains why HBase is chosen for log storage, discusses the shortcomings of self‑built indexes, and presents optimization strategies such as rowKey design, filter usage, coprocessor integration, and third‑party indexing to improve query performance.
A simple MySQL query on a million‑row picture table was sped up by adding a composite covering index, reducing average execution time from about 40 ms to 30 ms and eliminating the need for a table lookup.
This article explains SparkSQL's query processing pipeline—from parsing and logical planning through optimization and physical execution—highlighting why it often outperforms Hive on MapReduce by reducing I/O, minimizing shuffle stages, and reusing JVMs.
This article explains MySQL's indexing mechanisms—including BTREE and hash structures, page layout, index types, and practical query patterns—while showing how to interpret EXPLAIN output and avoid common pitfalls for efficient database performance.
Adding the right indexes to a complex 12‑table query dramatically cut I/O but unexpectedly increased execution time, and the article explains how an undocumented SQL Server hint forced parallelism to bring the runtime back down.
An expert MySQL case study compares two similar queries—one using a materialized subquery and the other with an OR condition that triggers index reads—showing the former runs about 40 times slower, and explains the impact of materialization, semi‑join optimization, and index usage on performance.
This article explains the inner workings of the DB2 optimizer, its four-step processing flow, cost‑based decision making, and detailed examples comparing full‑table and index scans, followed by practical tuning tips and a Q&A session for real‑world query optimization.
This guide explains how to use the SQL EXPLAIN statement to analyze query execution plans, detailing each output column such as id, select_type, table, possible_key, key, key_len, type, rows, and extra, and interpreting their meanings for effective optimization.
This article explains why replacing OR conditions with UNION queries in MySQL often yields faster execution, using a concrete users table example, EXPLAIN analysis, and a comparison of type and ref values to illustrate the performance advantage of UNION over OR.
This article explains MongoDB's three explain modes, details the fields returned by queryPlanner and executionStats, shows how IndexFilters influence the optimizer, and walks through practical examples that demonstrate how proper indexing eliminates collection scans and in‑memory sorts for faster query performance.
Optimizing queries should focus on those that, when improved, deliver the greatest overall system benefit—typically high‑concurrency, low‑cost queries—because small savings on frequently run statements can vastly reduce I/O, CPU usage, and risk of catastrophic performance failures.
This article explains how STRAIGHT_JOIN differs from INNER JOIN in MySQL, why the optimizer’s automatic table order may not always be optimal, and how specifying STRAIGHT_JOIN lets you manually control the loading sequence of tables to improve query performance.
This article explains MySQL’s logical architecture, detailing its three main layers, the responsibilities of each layer, connection management, authentication, query parsing, optimization, caching, and the key modules that together enable MySQL’s pluggable storage engine design.
The article analyzes a slow PostgreSQL query that joins multiple tables to fetch flight numbers, compares several alternative query formulations with their execution plans, and presents a final solution using array aggregation to achieve sub‑millisecond performance despite existing indexes.
This guide walks through MongoDB index fundamentals, showing how to view query plans, create single and compound indexes, manage index types like unique, sparse, and multikey, force specific indexes or full scans, and handle in‑memory sorting and index management for optimal performance.
