This article explains what MySQL composite (multi‑column) indexes are, how they are built and ordered, the leftmost‑prefix rule that governs their usage, factors such as cardinality and selectivity for optimal design, and common scenarios where the index may become ineffective.
This article demystifies database indexes by explaining their purpose, inner workings, and best‑practice design, covering common pitfalls such as slow queries, over‑indexing, NULL handling, composite index ordering, covering indexes, sorting, grouping, and maintenance across different database systems.
This article explains MySQL’s composite index leftmost‑prefix rule, demonstrates how the Skip Scan Range Access Method introduced in MySQL 8.0.13 can use indexes without following the rule, provides step‑by‑step examples, code snippets, and discusses its implementation, benefits, and limitations.
This article demonstrates MySQL query performance issues with large tables, shows how a subquery without indexes can take hours, and walks through step‑by‑step optimizations including adding single‑column indexes, converting to EXISTS and JOIN queries, creating composite indexes, covering indexes, and sorting improvements, achieving speedups of thousands of times.
This article explains how to determine the optimal order of columns in MySQL composite indexes by analyzing B+Tree structure, index selectivity, and real‑world query performance, providing practical examples and SQL demonstrations to help developers create more efficient indexes.
This article explains the principles, advantages, and pitfalls of MySQL composite indexes, covering the left‑most matching rule, field order effects, when single columns can use an index, and whether a composite index can replace single‑column indexes, with practical EXPLAIN examples.
This article explains MySQL index fundamentals, the B+Tree structure, various index types, the concept of covering indexes to avoid row look‑ups, the left‑most prefix rule, and presents three real‑world optimization cases that illustrate how to design effective composite indexes, reduce index size, and improve query performance while avoiding costly table scans and sorts.
This article examines why a MySQL composite index on (first_name, last_name) initially fails to be used for ordering, demonstrates how adding a range predicate reduces the scanned row count, and explains the threshold at which MySQL switches from filesort to index‑based retrieval.
A production MySQL server suffered CPU overload and half‑hour alerts because a high‑volume query skipped a crucial composite index field, leading to full‑table scans; the post explains the root cause, the temporary index fix, the left‑most index principle, code validation oversights, and lessons learned.
This article explains MySQL's ORDER BY execution process, comparing full-field sorting with rowid sorting, detailing how sort_buffer, temporary files, and index usage affect performance, and showing how composite and covering indexes can eliminate sorting altogether.
This article explains why MySQL may still use a composite index as a covering or range index despite the query not following the leftmost‑prefix rule, by analyzing optimizer trace output and demonstrating the behavior with concrete experiments.
This article reviews MySQL composite index syntax, explains the mandatory condition that the leftmost column must appear in the query filter, analyzes seven representative SQL statements that cannot benefit from a composite index, and provides practical refactoring suggestions such as adding single‑column indexes or creating more appropriate composite indexes.
This article explains how MySQL composite indexes work, the syntax for creating them, the conditions required for their use, and how different query patterns affect index utilization, illustrated with multiple SQL examples and execution‑plan analyses.
This article explains how MySQL indexes work, comparing binary search trees, red‑black trees, hash tables, B‑trees, B+‑trees, and the differences between MyISAM and InnoDB implementations, and it details primary, secondary, covering, and composite index usage with practical examples.
A production MySQL server was overwhelmed by a high‑volume query that ignored a crucial composite index because the leftmost key was missing, leading to full‑table scans, CPU saturation, and a half‑hour outage, and the post explains how the issue was diagnosed and fixed.
This article explains how MySQL stores data using pages, page directories, and multi‑page structures, demonstrates why MySQL silently sorts rows on insert, and shows how these mechanisms combine into the B+‑tree index that optimizes query performance and supports clustering, non‑clustering, and composite indexes.
This article explains how MySQL composite (multi‑column) indexes work, the left‑most prefix rule, demonstrates queries where the index is used or ignored, and lists common situations that cause index failure along with practical optimization tips.
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.
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.
This article explains InnoDB's record storage mechanics, compares auto‑increment and business primary keys, details primary, secondary, and composite index structures, and shares practical table‑design guidelines and real‑world case studies from ZhaiZhai's MySQL usage.
