MySQL 8.4 Query Optimization 3.2 | Composite Indexes: Why Column Order Matters

Same two columns, different order: 2 milliseconds, or 8.8 seconds.
One query, one customer's 20 most recent orders, tested against three indexes on a 10-million-row orders table: (customer_id), (order_time, customer_id) and (customer_id, order_time). Each index gets a fair turn: all three are INVISIBLE, and only one is made visible per round.
We test two customers: customer 1, our biggest, with 10,116 orders, and customer 500004, a typical customer with 11 orders. All results are from a live MySQL 8.4.7 recording.
⏱️ Chapters
0:00 Same columns, different order
0:27 A fair test: invisible indexes, two customers
1:01 Round 1: (customer_id) and a filesort
1:34 Round 2: (order_time, customer_id)
2:00 The typical customer: 10 million entries, 8.79 s
2:17 Round 3: (customer_id, order_time)
2:46 Why order matters: the phone book
3:18 Scoreboard and the optimizer's estimate
3:45 Rule of thumb, and next: covering indexes
📌 What you'll learn
• An INVISIBLE index still exists and is maintained, but the optimizer cannot use it. Making one index visible at a time gives every candidate the same query and the same data.
• (customer_id): customer 1's 10,116 orders are read and sorted (Using filesort) to keep 20, about 21 ms. The typical customer: 0.1 ms.
• (order_time, customer_id): MySQL scans backwards from the newest order. Customer 1 orders often, so 20 matches turn up after 15,106 entries (13.4 ms). The typical customer never reaches 20, so all 10 million entries are scanned: 8.79 s.
• (customer_id, order_time): MySQL jumps to the customer and reads backwards, stopping after 20 entries, with no sort: about 2 ms for customer 1 and 0.12 ms for the typical customer.
• A composite index is sorted by its first column, and only within each value of that column by the next one, like a phone book sorted by last name, then first name.
• In round 2 the optimizer estimated it would find 20 matches almost right away for both customers. As in Episode 3.1, the estimate decided the plan.
• A good starting rule: columns compared with equals first, then the column you sort or search by range. Then verify with EXPLAIN ANALYZE, for your biggest customers and your typical ones.
🧪 Lab
ddt_lab.orders, 10,000,000 rows, MySQL 8.4.7. Indexes idx_m32_cust, idx_m32_time_cust and idx_m32_cust_time, switched with ALTER TABLE … ALTER INDEX … VISIBLE / INVISIBLE.
[Paste the link to the Episode 3.2 SQL scripts here]
──────────
中文說明
同樣兩個欄位,換個順序:2 毫秒,或 8.8 秒。
同一個查詢:某位客戶最近的 20 筆訂單。我們在一千萬列的 orders 表上比較三個索引:(customer_id)、(order_time, customer_id)、(customer_id, order_time)。三個索引都先設成隱藏(INVISIBLE),每一輪只開一個,條件完全公平。
測試兩位客戶:customer 1 是最大的客戶,有 10,116 筆訂單;customer 500004 是一般客戶,只有 11 筆。所有數據都來自 MySQL 8.4.7 的實際錄影。
重點:
・隱藏索引依然存在、也會持續維護,只是最佳化器不能使用。每次只開一個,就能讓每個索引面對同樣的查詢與資料。
・(customer_id):customer 1 的 10,116 筆要全部讀出再排序(Using filesort),大約 21 毫秒;一般客戶只要 0.1 毫秒。
・(order_time, customer_id):從最新的訂單往回掃。customer 1 下單頻繁,掃 15,106 筆就湊到 20 筆(13.4 毫秒);一般客戶永遠湊不到 20 筆,一千萬筆全部掃完,花了 8.79 秒。
・(customer_id, order_time):直接找到這位客戶,倒著讀 20 筆就停,完全不用排序:customer 1 大約 2 毫秒,一般客戶 0.12 毫秒。
・複合索引先照第一個欄位排,只有在第一個欄位相同的範圍裡,才照第二個欄位排,就像先照姓、再照名排序的電話簿。
・第二輪裡,最佳化器估計兩位客戶都會很快湊到 20 筆。跟第 3.1 集一樣,是估計值決定了執行計畫。
・一個好的起點:先放用等號比較的欄位,再放要排序或範圍查詢的欄位,最後用 EXPLAIN ANALYZE 驗證,大客戶和一般客戶都要測。
📚 MySQL 8.4 Indexing & Query Optimization
Module 3 · Episode 3.2
Previous: 3.1 When an Index Makes a Query Slower: Selectivity and Full Table Scans
Next: 3.3 Covering Indexes: Skip the Trip Back to the Table
上一集:3.1 索引反而讓查詢變慢:選擇性與全表掃描
下一集:3.3 覆蓋索引:省掉回到資料表的那一趟
💬 Subtitles: Chinese (Traditional) and English captions are available. Turn on CC to choose.
字幕:提供繁體中文與英文字幕,請點選 CC 切換。
👍 If this helped, like, subscribe, and share the composite indexes you are designing.
覺得有幫助,歡迎按讚、訂閱,也可以留言分享你正在設計的複合索引。
MySQL is a registered trademark of Oracle Corporation and/or its affiliates. This channel is not affiliated with or endorsed by Oracle.
This video is for educational purposes. Refer to the official MySQL documentation for production use.
© 2026 龍博士 Doctor Dragon Tech. All rights reserved.
歡迎分享影片連結;未經許可,請勿轉載、重製或用於商業用途。
#MySQL #MySQL84 #InnoDB
Video Summary
AI GeneratedThis video explains how the column order in a composite index affects MySQL query performance. By testing three different index configurations on a query retrieving a customer's 20 most recent orders, it demonstrates that placing the equality filter column before the sorting column is the most efficient strategy for both large and small datasets.
