Details
-
New Feature
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
None
Description
The task is to add optimization for the following type of not that uncommon
expressions:
... WHERE key1=value1 or key1=value2 or ...
Currently MariaDB cannot use key1 for expression like the above and will
instead use another key or a full scan.
Here value1 or value2 can be constants, other fields or any expression
that the optimizer can use for 'key1=value'.
We could also use the same optimization of key1 IN (value1, value2,...)
Note that key1 above could also be a key part (key1_part2)
Some implementation suggestions:
- Add a JT_EQ_REF_MANY type and extend expression that test for
JT_EQ_REF to also handle JT_EQ_REF_MANY if appropriate (For example
in add_not_null_conds) - Change in KEY_USE Item *val to be Item **val (null terminated array)
- update_ref_and_keys() should be updated to fill in all values for a key part
- In best_access_patch add support for multiple *val. The cost should be
cost(key=value1) + cost(key=value2). - Note that capping of blocks read from disk, should not change
(this should probably work automatically if we use
Cost_estimate::add() to add the costs). - Add a new read method to pick_table_access_method(). This should be
similar to what we have for JT_REF_OR_NULL, with the following
exceptions: - While looping over all key different key values, we should
remember the previous ones and ignore the new one if it matches
any of the previous ones. - Update 'optimize_keyuse() for multiple values
- We should check if we can move JT_REF_OR_NULL to be handled by the new code.