Tagged articles
Composite Index
28 articles · Page 1 of 1
This article explains common MySQL index invalidation causes—implicit type conversion, functions on indexed columns, composite index ordering, and optimizer cost decisions—using EXPLAIN output analysis, and demonstrates a full slow query troubleshooting process from log to fix.
Composite IndexDatabase PerformanceEXPLAIN Plan
0 likes · 23 min read

This comprehensive guide walks through a complete MySQL performance optimization cycle: classifying three types of gradual slowdown, setting up slow logs and digest metrics, using EXPLAIN ANALYZE and optimizer trace to pinpoint a full-table scan on a 62M-row orders table, adding a composite index (user_id, status, created_at) via safe online DDL with three safety barriers, and establishing monitoring to prevent regression.
Composite IndexEXPLAINIndexing
0 likes · 47 min read

A step-by-step MySQL query optimization case study showing how indexing, join rewriting, composite indexes, and covering indexes reduced execution time from 30,248 seconds to 0.001 seconds with execution plan analysis.
Composite IndexCovering IndexIndexing
0 likes · 12 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

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 why placing column C immediately after the equality column A in a composite index (A, C, B) avoids filesort and keeps performance stable regardless of how many values appear in the B IN list, outperforming other index orders such as (A, B, C).
Composite IndexFilesortIN Clause
0 likes · 10 min read

The article explains why MySQL may still report Using filesort despite a composite index that appears to satisfy both WHERE and ORDER BY clauses, covering index ordering rules, the impact of range conditions, mismatched ORDER BY field order, ASC/DESC mixing, and practical optimization techniques.
Composite IndexFilesortIndex 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 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.
Composite IndexIndex SelectivityLeftmost Prefix
0 likes · 11 min read

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.
Composite IndexCovering IndexPerformance Tuning
0 likes · 11 min read

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.
Composite IndexMySQLSQL
0 likes · 10 min read

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.
Composite IndexCovering IndexDatabase Performance
0 likes · 12 min read

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.
B+TreeComposite IndexIndex Optimization
0 likes · 8 min read

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.
Composite IndexEXPLAINMySQL
0 likes · 9 min read

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

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.
Composite IndexEXPLAINMySQL
0 likes · 4 min read

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.
Composite IndexDatabase DebuggingJava Validation
0 likes · 8 min read

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.
Composite IndexCovering IndexIndex Optimization
0 likes · 13 min read

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.
Composite IndexCovering IndexMySQL
0 likes · 3 min read

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.
Composite IndexMySQLdatabase indexing
0 likes · 11 min read

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.
Composite IndexMySQLQuery Planning
0 likes · 14 min read

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.
B+TreeComposite IndexCovering Index
0 likes · 10 min read


ITPUBSep 8, 2020 · Databases
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.
Composite IndexDebuggingJava Validation
0 likes · 9 min read

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.
B+TreeComposite IndexDatabase Pages
0 likes · 24 min read

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.
Composite IndexDatabase PerformanceIndex Optimization
0 likes · 7 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


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


21CTOOct 31, 2017 · Databases
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.
Composite IndexInnoDBMySQL
0 likes · 12 min read
