🔥 5 Rare MySQL 8 Interview Questions on Query Planning, Statistics & Cardinality Estimation

These questions focus on how MySQL thinks, not how you write SQL.
They test your understanding of statistics, cost models, and optimizer behavior — areas that most candidates never study.

1) Cardinality Estimation in MySQL

Answer:
Cardinality estimation is MySQL’s attempt to predict how many rows a query will return.

MySQL estimates cardinality using:

index statistics

histograms (if available)

sampling, not full scans

Why it fails:

skewed data

correlated columns

outdated statistics

Bad cardinality → bad join order → slow query.

2) Persistent Statistics

Answer:
Persistent statistics store table and index stats on disk, not just in memory.

Enabled by default in MySQL 8.

Benefits:

Stable query plans across restarts

More predictable performance

Stored in:

mysql.innodb_table_stats
mysql.innodb_index_stats
Without persistent stats, MySQL may generate different plans after restart.

3) Index Selectivity for Multi-Column Indexes

Answer:
For a composite index (a, b, c), MySQL estimates selectivity as:

how many distinct values exist

how rows are distributed

Problem:
MySQL often assumes independence between columns.

Example:

(country, city)
If cities depend on country, MySQL may overestimate or underestimate row counts, leading to poor plans.

4) Index Merge Strategies

Answer:
Index Merge allows MySQL to use multiple indexes in one query.

Types:

index_merge_union

index_merge_intersection

index_merge_sort_union

Example:

SELECT *
FROM users
WHERE age v 30 OR country = 'IN';
MySQL may scan two indexes and merge results.

Interview tip:

Index merge is usually slower than a well-designed composite index.

5) Stale Statistics & Fixing Them

Answer:
Statistics become stale when:

large inserts/deletes occur

bulk loads happen

data distribution changes

Symptoms:

sudden slow queries

optimizer choosing table scans

wrong join order

Fix:

ANALYZE TABLE orders;
For deeper issues:

update histograms

rebuild indexes

review execution plans with EXPLAIN ANALYZE

🧠 Interview Takeaways

Cardinality drives everything

Persistent stats stabilize plans

Composite index estimation is tricky

Index merge is a fallback, not a solution

Stale stats silently kill performance

Видео 🔥 5 Rare MySQL 8 Interview Questions on Query Planning, Statistics & Cardinality Estimation канала CodeVisium