Tagged articles
Index Scan
17 articles · Page 1 of 1

ITPUBApr 4, 2026 · Databases
This article compares PostgreSQL and SQL Server data access methods—including sequential scans, index scans, bitmap heap scans, index‑only scans, and parallel query execution—highlighting PostgreSQL's lack of clustered indexes and the performance implications of each approach.
Bitmap ScanIndex ScanParallel Query
0 likes · 10 min read

MySQL’s ORDER BY DESC queries run slower than ASC because InnoDB stores records in a forward‑oriented singly linked list, using an infimum‑supremum structure, a page directory, and N_OWNED fields, which make backward scans O(n) while forward scans remain O(1).
Database InternalsIndex ScanInnoDB
0 likes · 9 min read

This article explains the purpose of MySQL's optimizer_switch prefer_ordering_index, shows how it affects ORDER BY and GROUP BY queries with LIMIT, provides code examples and EXPLAIN output, and argues that disabling it often yields better performance for skewed data distributions.
Index ScanMySQLoptimizer_switch
0 likes · 7 min read

This article examines MySQL’s optimizer_switch ‘prefer_ordering_index’ setting, explains how it influences ORDER BY/GROUP BY queries with LIMIT, demonstrates cases where keeping it ON harms performance due to costly index scans, and provides concrete SQL examples and recommendations to turn it off in most scenarios.
FilesortIndex ScanMySQL
0 likes · 8 min read

This article explains the purpose of MySQL’s optimizer_switch ‘prefer_ordering_index’, shows how it affects ORDER BY/GROUP BY queries with LIMIT, provides sample tables and procedures, compares EXPLAIN output with the option ON and OFF, and concludes why disabling it is often the safer choice for most workloads.
EXPLAINIndex ScanMySQL
0 likes · 8 min read

This article explains the purpose of MySQL's optimizer_switch prefer_ordering_index, why the default ON can hurt performance on skewed data, and demonstrates with table creation, data‑loading procedures, and EXPLAIN output how turning the option OFF often yields faster queries.
FilesortIndex ScanMySQL
0 likes · 7 min read

This article examines why a SELECT … FOR UPDATE query that appears to use a covering index in MySQL actually performs a table‑row lookup, detailing indirect evidence from performance_schema locks and direct proof through InnoDB source code, and explains the necessity of accessing the primary key for transaction isolation.
Database InternalsIndex ScanInnoDB
0 likes · 9 min read

This article explains how to read MySQL's EXPLAIN EXTRA column, covering SEMI JOIN hints such as FirstMatch, Start/End temporary, LooseScan, and the Range‑checked‑for‑each‑record optimization, with concrete examples and command‑line output to illustrate each case.
Database PerformanceEXPLAINIndex Scan
0 likes · 9 min read

This article explains how MySQL GROUP BY queries can be dramatically accelerated by using appropriate index scans, compares Loose Index Scan and Tight Index Scan, shows practical test data, execution plans, and provides guidelines for when each method is most effective.
Index ScanMySQLSQL Optimization
0 likes · 15 min read

This article explains why locating a record in a B‑Tree index is crucial for MySQL InnoDB operations, describes scan intervals, index page structures, and details the step‑by‑step binary and sequential search process used to find the first matching record, including performance optimizations.
B-TreeIndex ScanInnoDB
0 likes · 28 min read

This article demonstrates how rewriting a MySQL LIMIT OFFSET query to first fetch primary keys in a sub‑query and then join the main table can cut execution time from over 16 seconds to under one second by avoiding massive random I/O and buffer‑pool pollution.
Buffer PoolIndex ScanLIMIT OFFSET
0 likes · 7 min read

This article explains how MySQL's slave_rows_search_algorithms setting and the presence of primary or unique indexes affect the way a replica locates rows for DELETE operations, compares index‑scan, table‑scan, and hash‑scan methods, and provides practical guidance to reduce replication lag.
Hash ScanIndex ScanMySQL
0 likes · 11 min read

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

A recent production issue showed that PostgreSQL dramatically over‑estimated row counts because filter predicates performed implicit type casts, leading to sequential scans and 18‑second queries, but correcting column types or rewriting the predicates restored accurate estimates and reduced execution time to under a second.
Index ScanPostgreSQLQuery Planning
0 likes · 8 min read


ITPUBNov 14, 2016 · Databases
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.
Database PerformanceIndex ScanOracle
0 likes · 10 min read


ITPUBJul 11, 2016 · Databases
This article explains PostgreSQL's kernel architecture, from client connection handling through parsing, rewriting, planning, and execution, illustrates scan and join algorithms, shows how to extend the kernel and build clustered deployments, and introduces Foreign Data Wrappers for integrating external data sources.
ClusterForeign Data WrapperIndex Scan
0 likes · 13 min read

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.
DB2Database PerformanceIndex Scan
0 likes · 19 min read
