Tagged articles
clustered index
38 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 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 explains why indexes speed up queries by reducing disk I/O, dives into MySQL's page structure and B+Tree evolution, compares clustered and secondary indexes, clarifies composite index rules, lists common index‑misuse scenarios, and provides seven practical guidelines for designing efficient MySQL indexes.
B+TreeComposite IndexDatabase Performance
0 likes · 20 min read

The article explains how database indexes, built on sorted data and binary search, dramatically reduce query time compared to full table scans, while also covering storage fundamentals, the cost of excessive indexes, clustered indexes, index failure scenarios, and practical SQL optimization techniques.
SQL Optimizationbinary searchclustered index
0 likes · 12 min read

The article explains how InnoDB implements indexes as B+ trees, detailing their evolution from simple page directories, the differences between clustered and secondary indexes, the costs of indexing, and the specific query patterns and scenarios where such indexes are effective.
B+TreeInnoDBMySQL
0 likes · 14 min read

The article explains how database indexes act like a book's table of contents, reducing full‑table scans and disk I/O by using B+Tree structures, and compares clustered, non‑clustered, and covering indexes while highlighting their benefits and trade‑offs.
B+TreeCovering IndexDatabase Performance
0 likes · 11 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 guide explains MySQL’s B+‑tree index architecture, compares clustered and secondary indexes, demonstrates covering and prefix indexes, outlines design principles such as high‑selectivity columns and the left‑most prefix rule, and shows how to use EXPLAIN and real‑world examples to diagnose and improve query performance.
B+TreeCovering IndexEXPLAIN
0 likes · 12 min read

This article explains MySQL index fundamentals, covering what indexes are, their advantages and drawbacks, underlying data structures such as hash tables and B‑/B+‑trees, and the classification of clustered, non‑clustered, primary, secondary, unique, prefix, and full‑text indexes, with practical SQL examples and diagrams.
B-TreeMySQLNon-clustered Index
0 likes · 9 min read

This article explains the fundamentals of database indexing, covering what indexes are, their advantages and drawbacks, underlying structures such as hash tables, B‑tree and B+‑tree, different index types (clustered, non‑clustered, primary, secondary) in MySQL, and practical examples with code snippets.
B+TreeDatabase IndexesMySQL
0 likes · 9 min read

This article explains MySQL indexes, their working principle, advantages and drawbacks, different index types, the B+Tree structure, comparisons with B‑Tree, and the differences between clustered and non‑clustered (auxiliary) indexes, providing clear examples and practical insights.
B+TreeIndexMySQL
0 likes · 13 min read

Understanding MySQL's clustered indexes—including their definition, selection rules, customization via primary key design, practical creation examples, and key design considerations—helps developers optimize query performance and storage efficiency in InnoDB tables for various workloads.
InnoDBMySQLSQL
0 likes · 8 min read

This article provides a comprehensive review of MySQL’s two primary index types—clustered (clustered index) and non‑clustered (non‑clustered index)—explaining their storage mechanisms, performance trade‑offs, typical use cases, and best‑practice guidelines for selecting the appropriate index in database design.
MySQLNon-clustered Indexclustered index
0 likes · 14 min read

This article explains how databases store data on various storage devices, why indexes dramatically improve query performance through sorted structures and binary search, and outlines practical SQL optimization techniques while warning about the trade‑offs of excessive indexing.
SQL Performancebinary searchclustered index
0 likes · 11 min read

This article explains how databases store data on various storage media, how indexes—especially clustered indexes—leverage sorted structures and binary search to dramatically reduce query time, and outlines best practices and pitfalls of index usage and SQL optimization.
Performance TuningSQL Optimizationbinary search
0 likes · 12 min read

This article explains what MySQL indexes are, how they work, their advantages and drawbacks, the different index types, the inner workings of B+Tree structures, and the differences between clustered and non‑clustered indexes, providing practical insights for database optimization.
B+TreeIndexInnoDB
0 likes · 10 min read

This article explains MySQL indexes, their purpose and working principle, compares primary, ordinary, composite and full‑text indexes, describes the B+Tree storage structure versus B‑Tree, and clarifies the differences between clustered and non‑clustered (auxiliary) indexes along with their advantages and drawbacks.
B+TreeMySQLclustered index
0 likes · 10 min read

This article explains what MySQL indexes are, how they work, their advantages and drawbacks, the different index types—including primary, ordinary, composite, full‑text, clustered and non‑clustered—and compares B‑Tree with B+Tree structures to help you design faster, more efficient queries.
B+TreeInnoDBMySQL
0 likes · 12 min read

This article compares UUID v4 and UUID v7 as clustered index keys, explains their structures, lists pros and cons, describes a Docker‑based MySQL experiment using Node.js and Go, presents single‑ and multi‑thread insertion timings, and analyses why the time‑ordered UUID v7 (and serial IDs) outperform random UUID v4.
DockerGoMySQL
0 likes · 14 min read

This article explains the fundamental concepts of MySQL index structures, detailing how clustered (primary) indexes store data physically in order, how non‑clustered (secondary) indexes reference primary keys, and compares their performance implications and practical usage scenarios.
Database PerformanceIndexInnoDB
0 likes · 8 min read

This article explains how computer storage works, why database indexes dramatically speed up queries, the mechanics of binary search, the differences between clustered and non‑clustered indexes, common pitfalls of over‑indexing, and practical SQL optimization strategies to avoid full table scans and index invalidation.
IndexSQL OptimizationStorage
0 likes · 12 min read

This article provides a comprehensive deep‑dive into MySQL indexing, covering the definition, pros and cons, differences between clustered and non‑clustered indexes, InnoDB vs MyISAM implementations, B+Tree structure, capacity calculations, adaptive hash indexes, index pushdown optimization, and how to interpret EXPLAIN output to verify index usage.
Adaptive Hash IndexB+TreeIndex Pushdown
0 likes · 18 min read


ITPUBFeb 26, 2023 · Databases
This article explains InnoDB’s clustered and secondary index structures, why secondary index leaf nodes store primary key values, how lookups trigger “row‑lookup” (回表) operations, and how covering indexes can eliminate those extra scans, illustrated with MySQL examples.
Covering IndexInnoDBMySQL
0 likes · 7 min read

This article explains how database indexes improve query speed by leveraging storage principles, binary search, and B‑tree structures, discusses clustered versus non‑clustered indexes, outlines the trade‑offs of indexing, and provides practical SQL optimization tips to avoid full table scans and index misuse.
IndexSQLStorage
0 likes · 12 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, clustered versus non‑clustered indexes, and common SQL optimization techniques.
IndexSQLStorage
0 likes · 11 min read

This article explains how data is physically stored, why database indexes dramatically speed up queries through sorted structures and binary search, discusses clustered versus non‑clustered indexes, their trade‑offs, and provides practical SQL optimization tips to avoid common pitfalls.
IndexSQL Optimizationbinary search
0 likes · 13 min read

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.
Performance TuningSQL Optimizationbinary search
0 likes · 12 min read

This article explains the classification of InnoDB indexes into clustered and secondary types, describes how they are stored and accessed, and introduces optimization concepts such as back‑to‑table queries, covering indexes, and index pushdown to improve MySQL performance.
IndexInnoDBMySQL
0 likes · 11 min read

This article explains MySQL index fundamentals, classifies indexes by data structure, storage, field characteristics and column count, compares B+Tree with B‑Tree, hash and red‑black trees, and demonstrates clustered and secondary indexes with practical SQL examples and diagrams.
B+TreeMySQLclustered index
0 likes · 15 min read

This article dissects MySQL's InnoDB storage engine by first exposing the internal page layout, then explaining the clustered index organization, and finally detailing the slot mechanism used for intra‑page queries, while illustrating insertion strategies and space‑reclamation techniques.
Database InternalsInnoDBMySQL
0 likes · 11 min read

This article explains how databases store data on physical storage devices, describes the role of indexes and binary search in accelerating query performance, discusses clustered versus non‑clustered indexes, outlines the trade‑offs of excessive indexing, and lists common SQL optimization techniques.
IndexSQL OptimizationStorage
0 likes · 12 min read

This article investigates how clustered and non‑clustered indexes affect query, insert, update, and delete performance in SQL Server 2008R2 and MySQL 5.5 using three test tables, presenting detailed timing results and concluding that clustered indexes are optimal for small datasets while indexes dramatically speed up queries in MySQL.
Non-clustered IndexSQL Serverclustered index
0 likes · 7 min read

This article explains the fundamentals of database indexing, detailing B‑Tree structures, the differences between clustered and non‑clustered indexes, their storage formats, and how they affect query, insert, and delete operations, including the concept of index covering.
B+TreeIndex CoveringNon-clustered Index
0 likes · 15 min read

This article explains the fundamental concepts and practical design of MySQL indexes, covering implementation-level index types, application-level index categories, the differences between clustered and non‑clustered indexes, B‑Tree and hash structures, covering indexes, and best‑practice strategies for optimal query performance.
B+TreeCovering IndexMySQL
0 likes · 14 min read

This article explains the fundamentals of MySQL indexes, covering their conceptual purpose, logical classifications, underlying data structures such as hash, B‑Tree, and B+Tree, physical storage differences between clustered and non‑clustered indexes, and practical tips for effective index optimization.
B+TreeBTreeDatabase Indexes
0 likes · 22 min read

This article explains the fundamentals of MySQL indexing, tracing the evolution from binary search trees and balanced AVL trees to B‑trees and B+‑trees, and details how InnoDB implements clustered and non‑clustered indexes, including search processes and performance considerations.
B+TreeIndexInnoDB
0 likes · 14 min read

Database indexes, akin to a dictionary’s table of contents, dramatically speed up data retrieval, and this article explains what indexes are, how composite indexes work, the left‑most prefix rule, and the difference between clustered and non‑clustered indexes, using clear analogies and examples.
Composite IndexSQLclustered index
0 likes · 8 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
