BestHub
Sign in
Tag

composite index

0 views collected around this technical thread.

Top Architect
Top Architect
Mar 29, 2023 · Databases

MySQL Query Optimization and Index Tuning: From Subqueries to Joins and Composite Indexes

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.

Covering IndexDatabase PerformanceIndexing
0 likes · 12 min read
MySQL Query Optimization and Index Tuning: From Subqueries to Joins and Composite Indexes
Architecture & Thinking
Architecture & Thinking
Feb 14, 2023 · Databases

How to Choose the Best Index Order for MySQL: Boost Query Performance

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-TreeIndex OptimizationMySQL
0 likes · 8 min read
How to Choose the Best Index Order for MySQL: Boost Query Performance
Wukong Talks Architecture
Wukong Talks Architecture
Oct 25, 2021 · Databases

MySQL Index Optimization and Code Review Practices

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.

Code ReviewCovering IndexDatabase Performance
0 likes · 13 min read
MySQL Index Optimization and Code Review Practices
Aikesheng Open Source Community
Aikesheng Open Source Community
Apr 2, 2021 · Databases

Why MySQL Chooses a Composite Index Even When the Query Skips the Leftmost Column

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.

Covering IndexDatabase PerformanceMySQL
0 likes · 3 min read
Why MySQL Chooses a Composite Index Even When the Query Skips the Leftmost Column
Aikesheng Open Source Community
Aikesheng Open Source Community
Mar 10, 2021 · Databases

Unsuitable Scenarios for MySQL Composite Indexes and Refactoring Strategies

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.

MySQLQuery Optimizationcomposite index
0 likes · 11 min read
Unsuitable Scenarios for MySQL Composite Indexes and Refactoring Strategies
Aikesheng Open Source Community
Aikesheng Open Source Community
Feb 24, 2021 · Databases

Understanding Composite (Multi‑Column) Indexes in MySQL

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.

MySQLQuery PlanningSQL Optimization
0 likes · 14 min read
Understanding Composite (Multi‑Column) Indexes in MySQL
Selected Java Interview Questions
Selected Java Interview Questions
Jul 3, 2020 · Databases

Understanding MySQL Index Structures: From Simple Tables to B+ Trees

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-TreeDatabase PagesIndexing
0 likes · 24 min read
Understanding MySQL Index Structures: From Simple Tables to B+ Trees
Selected Java Interview Questions
Selected Java Interview Questions
Dec 10, 2019 · Databases

When Composite Indexes Fail in MySQL: Conditions and Examples

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 PerformanceIndex OptimizationMySQL
0 likes · 7 min read
When Composite Indexes Fail in MySQL: Conditions and Examples