Understanding Cardinality and HINT Usage in MySQL Query Optimization

Before diving into the demo, two key concepts are introduced: the cardinality of a column (the number of distinct values) and the use of HINTs to influence the MySQL optimizer.

Cardinality is the statistical information the optimizer retrieves to estimate how many rows each predicate will match. For a table t1 with 100 rows, the column f1 can have anywhere from 1 to 100 distinct values; the higher the cardinality, the more selective the column, which is why indexes are typically built on high‑cardinality columns.

HINT is a manual directive that helps the optimizer generate a better execution plan in special scenarios where the automatically chosen plan is not optimal. For example, after heavy updates the stored cardinality may become stale, causing the optimizer to pick a full table scan.

The article then presents a sample table t1 with columns id , rank1 , rank2 , rank3 , log_time , prefix_uid , and desc1 , each having appropriate indexes (e.g., idx_rank1 , idx_rank2 , idx_rank3 ).

Two classic queries are defined:

select * from t1 where f1 = 20;

select * from t1 where f1 = 30;

Because the value 30 is frequently updated, its cardinality may be inaccurate, making the second query less efficient.

Next, three example queries are examined:

-- SQL C (low selectivity)
select * from t1 where rank1 = 1 or rank2 = 2 or rank3 = 2;

-- SQL D (high selectivity)
select * from t1 where rank1 = 100 and rank2 = 100 and rank3 = 100;

Running EXPLAIN FORMAT=JSON on SQL C without any hint shows an ALL access type, scanning 32,034 rows with a cost of 3,243.65. Adding the hint /*+ index_merge(t1) */ changes the access type to index_merge , scans only 1,103 rows and reduces the cost to 441.09, a ten‑fold improvement.

For SQL D, the optimizer already chooses a single index ( idx_rank1 ) with a cost of 534.34. Adding the same index_merge hint forces the optimizer to intersect all three indexes, resulting in a cost of just 5.23 and scanning a single row, demonstrating the dramatic benefit of the hint when the optimizer’s default plan is sub‑optimal.

In summary, the cardinality value of a column directly influences the optimizer’s execution plan; when cardinality is stale, manually adding HINTs such as index_merge can substantially improve performance. Future MySQL releases are expected to provide more sophisticated hint mechanisms.