MySQL 8.4 Query Optimization 3.1 | When an Index Makes a Query Slower: Selectivity and Full Table Scans

7 Video Views·Nov 13, 2026  #MySQL #MySQL84 #InnoDB

One index, one query, two status values. One run takes 0.03 seconds. The other takes 8.7 seconds, and the slow one is still using the index.

On a 10-million-row orders table with an index on status, we compare a rare value (processing, 0.3% of rows) with a common one (delivered, 92%). Then we take the index away with IGNORE INDEX: the full table scan finishes in 2.9 seconds, three times faster than the plan the optimizer chose. Finally, we look at why the optimizer got it wrong.

All results are from a live MySQL 8.4.7 recording. The whole table was cached in an 8 GB buffer pool, so both plans ran from memory.

⏱️ Chapters
0:00 Same index, two values: 0.03 s vs. 8.7 s
0:25 The data: 92% delivered, 0.3% processing
0:59 Case 1: processing uses the index
1:36 Case 2: delivered uses the same plan
2:02 The twist: IGNORE INDEX and a full table scan
2:50 Why the optimizer misjudged: the half-table cap
3:23 Three lessons
3:54 Next: composite indexes

📌 What you'll learn
• Selectivity is the share of rows a value matches. processing (29,700 rows) is highly selective; delivered (9,207,170 rows) is not.
• For processing, EXPLAIN shows type ref on idx_m31_status, and EXPLAIN ANALYZE reads 29,700 rows in about 0.03 s.
• For delivered, MySQL chose the same index. Every one of the 9.2 million matches needed its own lookup in the clustered index: about 8.7 s, three runs in a row.
• With IGNORE INDEX, MySQL read the whole table in primary-key order instead: 10 million rows scanned, 9.2 million kept, about 2.9 s.
• The optimizer estimated 4,979,394 matching rows, exactly half of the table's 9,958,788-row estimate. InnoDB caps a range estimate at half of the table's estimated rows, so the index plan looked about half as expensive as a scan (cost 530,970 vs. 1,010,000).
• When a plan looks reasonable but the query is slow, compare estimated rows with actual rows in EXPLAIN ANALYZE.
• IGNORE INDEX is a good way to test another plan, not a permanent fix. The real fix is better index design.

🧪 Lab
ddt_lab.orders, 10,000,000 rows, MySQL 8.4.7, with a single-column index idx_m31_status on status.
[Paste the link to the Episode 3.1 SQL scripts here]

──────────
中文說明

同一個索引、同一個查詢,只換一個 status 值:一次只要 0.03 秒,另一次要 8.7 秒,而慢的那次用的還是同一個索引。

我們在一千萬列的 orders 表上,替 status 建立索引,比較少見的 processing(0.3%)和常見的 delivered(92%)。接著用 IGNORE INDEX 把索引拿掉:全表掃描只要 2.9 秒,比最佳化器選的計畫快了三倍。最後說明最佳化器為什麼會看錯。

本集數據全部來自 MySQL 8.4.7 的實際錄影。整張表都放在 8 GB 的 buffer pool 裡,所以兩個計畫都是從記憶體讀取資料。

重點:
・選擇性是指一個值符合的列數佔整張表的比例。processing 只有 29,700 列,選擇性高;delivered 有 9,207,170 列,選擇性很低。
・delivered 一樣走 idx_m31_status,九百多萬筆都要各自回聚簇索引取整列,三次都要大約 8.7 秒。
・加上 IGNORE INDEX 後改成全表掃描,讀完一千萬列,留下九百二十萬列,只要大約 2.9 秒。
・最佳化器估計符合的列數是 4,979,394,剛好是全表估計 9,958,788 的一半。InnoDB 估算範圍內的列數時,最多只估到整張表的一半,所以走索引的成本被低估了。
・查詢變慢時,用 EXPLAIN ANALYZE 比較估計列數和實際列數。
・IGNORE INDEX 適合拿來測試,不是長久的解法;真正的解法是把索引設計好。

📚 MySQL 8.4 Indexing & Query Optimization
Module 3 · Episode 3.1
Previous: 2.4 B+Tree Basics: Full Table Scan vs. Index Lookup
Next: 3.2 Composite Indexes: Why Column Order Matters

上一集:2.4 B+Tree 入門:全表掃描 vs. 索引查詢
下一集:3.2 複合索引:欄位順序為什麼重要

👍 If this helped, like, subscribe, and share the slow queries you have run into.
覺得有幫助,歡迎按讚、訂閱,也可以留言分享你遇過的慢查詢。

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