Does the query optimize a low-cardinality index prefix range search?
Conventionally, using a range condition on a field which forms a prefix of an index prevents the suffix of the index from being used.
E.g. CREATE TABLE example ( low_cardinality int, high_cardinality int, somedata varchar(255), key (low_cardinality, high_cardinality));
Assume low_cardinality contains only 500 unique values, while high_cardinality is in the 10s of millions.
Consider the query: SELECT somedata FROM example where low_cardinality > X and high_cardinality = Y;
Assume that only 10% of low_cardinality values match > X and that the total result set of the query should be only a few hundred rows.
As far as I know, MySQL (at least the 5.0 series), would apply a range search to low_cardinality and would not use the index to optimize the high_cardinality = Y part.
Will any version of MariaDB (or stock MySQL) apply a reasonable optimization, or do I need to manually iterate over low_cardinality values?
Also, what's the proper name for this optimization, if there is one? Obviously, the optimization can be generalized to apply to any range type - even against string prefixes.
Thanks! Mat
Answer
It can be automatic, if you'll see in the EXPLAIN that "Range checked for each record (index map: N)". But it's pretty expensive operation, so the optimizer doesn't use that often.
