Description:
When using MRR optimization for a DELETE/UPDATE situation, we may use ordered scan for the table, I guess this optimization is to avoid IO cost since random select and delete may cause the performance.
566 ha_rows check_quick_select(THD *thd, RANGE_OPT_PARAM *param, uint idx,
567 bool index_only, SEL_ROOT *tree,
568 bool update_tbl_stats, enum_order order_direction,
569 bool skip_records_in_range, uint *mrr_flags,
570 uint *bufsize, Cost_estimate *cost,
571 bool *is_ror_scan, bool *is_imerge_scan) {
......
619
620 if (current_thd->lex->sql_command != SQLCOM_SELECT)
621 *mrr_flags |= HA_MRR_SORTED; // Assumed to give faster ins/upd/del
However, this is not suitable for partition key condition. Because if we use ordered scan for partition table, we may delete a record between partitions and still cause IO problem.
How to repeat:
create table t1(id int primary key, name char(5)) PARTITION BY HASH (id);
-- using sorted MRR
delete from `t1` where `id` = 0 or (`id` > 500) or (`id` < 500 and `id` > 200);
-- no sorted MRR
select * from `t1` where `id` = 0 or (`id` > 500) or (`id` < 500 and `id` > 200);
Suggested fix:
We may consider such situation, multi-range condition on partition key and keep using unordered scan on partition tables. And just use local index order to DELETE/UPDATE the record.
Description: When using MRR optimization for a DELETE/UPDATE situation, we may use ordered scan for the table, I guess this optimization is to avoid IO cost since random select and delete may cause the performance. 566 ha_rows check_quick_select(THD *thd, RANGE_OPT_PARAM *param, uint idx, 567 bool index_only, SEL_ROOT *tree, 568 bool update_tbl_stats, enum_order order_direction, 569 bool skip_records_in_range, uint *mrr_flags, 570 uint *bufsize, Cost_estimate *cost, 571 bool *is_ror_scan, bool *is_imerge_scan) { ...... 619 620 if (current_thd->lex->sql_command != SQLCOM_SELECT) 621 *mrr_flags |= HA_MRR_SORTED; // Assumed to give faster ins/upd/del However, this is not suitable for partition key condition. Because if we use ordered scan for partition table, we may delete a record between partitions and still cause IO problem. How to repeat: create table t1(id int primary key, name char(5)) PARTITION BY HASH (id); -- using sorted MRR delete from `t1` where `id` = 0 or (`id` > 500) or (`id` < 500 and `id` > 200); -- no sorted MRR select * from `t1` where `id` = 0 or (`id` > 500) or (`id` < 500 and `id` > 200); Suggested fix: We may consider such situation, multi-range condition on partition key and keep using unordered scan on partition tables. And just use local index order to DELETE/UPDATE the record.