mirror of
https://github.com/MariaDB/server.git
synced 2025-09-18 23:25:30 +02:00
8cdee25952Also expand vcol field index coverings to include indexes covering all
the fields in the expression. The reasoning goes as follows: let f(c1,
c2, ..., cn) be a function on applied to columns c1, c2, ..., cn, if
f(...) is covered by an index, so should vc whose expression is
f(...).
For example, if t.vf = t.c1 + t.c2, and t has three indexes (vf), (c1,
c2), (c1).
Before this change, vf's index covering is a singleton {(vf)}. Let's call
that the "conventional" index covering.
After this change vf's index covering is now {(vf), (c1, c2)}, since
(c1, c2) covers both c1 and c2. Let's call (c1, c2) in this case the
"extra" covering.
With the coverings updated, when an index in the "extra" covering is
chosen for keyread, the vcol also needs to be calculated. In this case
we mark vcol in the table read_set, and ensure it is computed.
With these changes, we see various improvements, including from using
full table scan + filesort to full index scan + filesort when ORDER BY
an indexed vcol (here vc = c + 1 is a vcol and both c and vc are
indexes):
explain select c + 1 from t order by vc;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t ALL NULL NULL NULL NULL 10000 Using filesort
+1 SIMPLE t index NULL c 5 NULL 10000 Using index; Using filesort
The substitutions are followed updates to all_fields which include a
copy of the ORDER BY/GROUP BY item pointers, as well as corresponding
updates to ref_pointer_array so that the all_fields and
ref_pointer_array remain in sync.
Another, related change is the recomputation of table index covering
on substitutions. It not only reflects the correct table index
covering after the substitutions, but also improve executions where
the vcol index can be chosen, such as this example (here vc = c + 1
and vc is the only index in the table), from full table scan +
filesort to full index scan:
select vc from t order by c + 1;
We do it in SELECT as well as in single table DELETE/UPDATE.
|
||
|---|---|---|
| .. | ||
| alter_inplace-9045.result | ||
| binlog.result | ||
| charsets.result | ||
| cross_db.result | ||
| delayed.result | ||
| func_regexp.result | ||
| index.result | ||
| innodb_autoinc_vcol.result | ||
| innodb_virtual_fk.result | ||
| load_data.result | ||
| mrr.result | ||
| myisam_repair_prefix_varchar.result | ||
| not_supported.result | ||
| order_by_group_by_subst.result | ||
| order_by_group_by_subst_notembedded.result | ||
| partition.result | ||
| races.result | ||
| range.result | ||
| rpl_vcol.result | ||
| update.result | ||
| update_binlog.result | ||
| upgrade.result | ||
| vcol_archive.result | ||
| vcol_blackhole.result | ||
| vcol_blocked_sql_funcs.result | ||
| vcol_column_def_options_innodb.result | ||
| vcol_column_def_options_myisam.result | ||
| vcol_csv.result | ||
| vcol_handler_aria.result | ||
| vcol_handler_innodb.result | ||
| vcol_handler_myisam.result | ||
| vcol_ins_upd_innodb.result | ||
| vcol_ins_upd_myisam.result | ||
| vcol_keys_aria.result | ||
| vcol_keys_innodb.result | ||
| vcol_keys_myisam.result | ||
| vcol_memory.result | ||
| vcol_merge.result | ||
| vcol_misc.result | ||
| vcol_non_stored_columns_innodb.result | ||
| vcol_non_stored_columns_myisam.result | ||
| vcol_partition_innodb.result | ||
| vcol_partition_myisam.result | ||
| vcol_sargable.result | ||
| vcol_sargable_debug.result | ||
| vcol_select_innodb.result | ||
| vcol_select_myisam.result | ||
| vcol_sql_mode.result | ||
| vcol_sql_mode_datetime.result | ||
| vcol_sql_mode_time.result | ||
| vcol_sql_mode_timestamp.result | ||
| vcol_sql_mode_upgrade.result | ||
| vcol_supported_sql_funcs.result | ||
| vcol_syntax.result | ||
| vcol_trigger_sp_innodb.result | ||
| vcol_trigger_sp_myisam.result | ||
| vcol_utf32.result | ||
| vcol_view_innodb.result | ||
| vcol_view_myisam.result | ||
| wrong_arena.result | ||