mirror of
https://github.com/MariaDB/server.git
synced 2026-03-21 15:58:41 +01:00
04254ffed1Also 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
|
||
|---|---|---|
| .. | ||
| federated_gcol.result | ||
| gcol_archive.result | ||
| gcol_blackhole.result | ||
| gcol_blocked_sql_funcs_innodb.result | ||
| gcol_blocked_sql_funcs_myisam.result | ||
| gcol_bug20746926.result | ||
| gcol_bugfixes.result | ||
| gcol_column_def_options_innodb.result | ||
| gcol_column_def_options_myisam.result | ||
| gcol_csv.result | ||
| gcol_falcon.result | ||
| gcol_handler_innodb.result | ||
| gcol_handler_myisam.result | ||
| gcol_ins_upd_innodb.result | ||
| gcol_ins_upd_myisam.result | ||
| gcol_keys_innodb.result | ||
| gcol_keys_myisam.result | ||
| gcol_memory.result | ||
| gcol_merge.result | ||
| gcol_ndb.result | ||
| gcol_non_stored_columns_innodb.result | ||
| gcol_non_stored_columns_myisam.result | ||
| gcol_partition_innodb.result | ||
| gcol_partition_myisam.result | ||
| gcol_purge.result | ||
| gcol_rejected_innodb.result | ||
| gcol_rollback.result | ||
| gcol_select_innodb.result | ||
| gcol_select_myisam.result | ||
| gcol_supported_sql_funcs_innodb.result | ||
| gcol_supported_sql_funcs_myisam.result | ||
| gcol_trigger_sp_innodb.result | ||
| gcol_trigger_sp_myisam.result | ||
| gcol_update.result | ||
| gcol_view_innodb.result | ||
| gcol_view_myisam.result | ||
| innodb_partition.result | ||
| innodb_prefix_index_check.result | ||
| innodb_virtual_basic.result | ||
| innodb_virtual_blob.result | ||
| innodb_virtual_debug.result | ||
| innodb_virtual_debug_purge.result | ||
| innodb_virtual_fk.result | ||
| innodb_virtual_fk_restart.result | ||
| innodb_virtual_index.result | ||
| innodb_virtual_purge.result | ||
| innodb_virtual_rebuild.result | ||
| innodb_virtual_stats.result | ||
| innodb_wl8114.result | ||
| main_alter_table.result | ||
| main_mysqldump.result | ||
| rpl_gcol.result | ||
| virtual_index_drop.result | ||