Does the query optimize a low-cardinality index prefix range search? - Source

Revision: 16539
User: Mathew Johnston
Date: 2013-06-11 18:22

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

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

Does the query optimize a low-cardinality index prefix range search? - Source

Answer

Products

Solutions

Resources

Company

Pricing

Download MariaDB