Below is the list of changes that have just been committed into a local
5.1 repository of psergey. When psergey does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet
1.2083 06/01/26 20:17:17 sergefp@stripped +4 -0
WL#2985 "Partition pruning": addition2: perform partition pruning for
single-table UPDATE/DELETE.
sql/sql_update.cc
1.185 06/01/26 20:17:12 sergefp@stripped +15 -0
WL#2985 "Partition pruning": addition2: perform partition pruning for UPDATE/DELETE:
Added partition pruning call in mysql_update()
sql/sql_delete.cc
1.173 06/01/26 20:17:12 sergefp@stripped +15 -2
WL#2985 "Partition pruning": addition2: perform partition pruning for UPDATE/DELETE:
Added partition pruning call in mysql_delete()
mysql-test/t/partition_pruning.test
1.6 06/01/26 20:17:12 sergefp@stripped +46 -0
WL#2985 "Partition pruning": addition2: perform partition pruning for UPDATE/DELETE:
testcases added
mysql-test/r/partition_pruning.result
1.6 06/01/26 20:17:12 sergefp@stripped +39 -0
WL#2985 "Partition pruning": addition2: perform partition pruning for UPDATE/DELETE:
testcases added
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: sergefp
# Host: newbox.mylan
# Root: /home/psergey/mysql-5.1-wl2985-postfixes
--- 1.172/sql/sql_delete.cc 2006-01-20 14:33:53 +03:00
+++ 1.173/sql/sql_delete.cc 2006-01-26 20:17:12 +03:00
@@ -100,6 +100,21 @@
/* Handler didn't support fast delete; Delete rows one by one */
}
+#ifdef WITH_PARTITION_STORAGE_ENGINE
+ if (prune_partitions(thd, table, conds))
+ {
+ free_underlaid_joins(thd, select_lex);
+ thd->row_count_func= 0;
+ send_ok(thd); // No matching records
+ DBUG_RETURN(0);
+ }
+ /*
+ Update the table->records number (note: we probably could remove the
+ previous file->info() call)
+ */
+ table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK);
+#endif
+
table->used_keys.clear_all();
table->quick_keys.clear_all(); // Can't use 'only index'
select=make_select(table, 0, 0, conds, 0, &error);
@@ -111,13 +126,11 @@
free_underlaid_joins(thd, select_lex);
thd->row_count_func= 0;
send_ok(thd,0L);
-
/*
We don't need to call reset_auto_increment in this case, because
mysql_truncate always gives a NULL conds argument, hence we never
get here.
*/
-
DBUG_RETURN(0); // Nothing to delete
}
--- 1.184/sql/sql_update.cc 2006-01-04 11:25:45 +03:00
+++ 1.185/sql/sql_update.cc 2006-01-26 20:17:12 +03:00
@@ -244,6 +244,21 @@
}
// Don't count on usage of 'only index' when calculating which key to use
table->used_keys.clear_all();
+
+#ifdef WITH_PARTITION_STORAGE_ENGINE
+ if (prune_partitions(thd, table, conds))
+ {
+ free_underlaid_joins(thd, select_lex);
+ send_ok(thd); // No matching records
+ DBUG_RETURN(0);
+ }
+ /*
+ Update the table->records number (note: we probably could remove the
+ previous file->info() call)
+ */
+ table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK);
+#endif
+
select= make_select(table, 0, 0, conds, 0, &error);
if (error || !limit ||
(select && select->check_quick(thd, safe_update, limit)))
--- 1.5/mysql-test/r/partition_pruning.result 2006-01-04 11:08:49 +03:00
+++ 1.6/mysql-test/r/partition_pruning.result 2006-01-26 20:17:12 +03:00
@@ -304,3 +304,42 @@
explain partitions select * from t1 where b > 1 and b < 3 and (a =1 or a =2);
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t1 p1_sp2,p2_sp2 ALL NULL NULL NULL NULL 3 Using where
+drop table t1;
+create table t1 (a int) partition by list(a) (
+partition p0 values in (1,2),
+partition p1 values in (3,4)
+);
+insert into t1 values (1),(1),(2),(2),(3),(4),(3),(4);
+flush status;
+update t1 set a=100 where a=5;
+show status like 'Handler_read_rnd_next';
+Variable_name Value
+Handler_read_rnd_next 0
+flush status;
+update t1 set a=100 where a+1=5+1;
+show status like 'Handler_read_rnd_next';
+Variable_name Value
+Handler_read_rnd_next 10
+flush status;
+delete from t1 where a=5;
+show status like 'Handler_read_rnd_next';
+Variable_name Value
+Handler_read_rnd_next 0
+flush status;
+delete from t1 where a+1=5+1;
+show status like 'Handler_read_rnd_next';
+Variable_name Value
+Handler_read_rnd_next 10
+create table t2 like t1;
+insert into t2 select * from t2;
+flush status;
+update t1,t2 set t1.a=1000, t2.a=1000 where t1.a=5 and t2.a=5;
+show status like 'Handler_read_rnd_next';
+Variable_name Value
+Handler_read_rnd_next 3
+flush status;
+delete t1,t2 from t1, t2 where t1.a=5 and t2.a=5;
+show status like 'Handler_read_rnd_next';
+Variable_name Value
+Handler_read_rnd_next 3
+drop table t1,t2;
--- 1.5/mysql-test/t/partition_pruning.test 2006-01-04 11:08:49 +03:00
+++ 1.6/mysql-test/t/partition_pruning.test 2006-01-26 20:17:12 +03:00
@@ -269,6 +269,52 @@
explain partitions select * from t1 where b > 1 and b < 3;
explain partitions select * from t1 where b > 1 and b < 3 and (a =1 or a =2);
+drop table t1;
+
+# Test partition pruning for single-table UPDATE/DELETE.
+# TODO: Currently we test only "all partitions pruned away" case. Add more
+# tests when the patch that makes use of partition pruning results at
+# execution phase is pushed.
+
+create table t1 (a int) partition by list(a) (
+ partition p0 values in (1,2),
+ partition p1 values in (3,4)
+);
+insert into t1 values (1),(1),(2),(2),(3),(4),(3),(4);
+
+# This won't do any table access
+flush status;
+update t1 set a=100 where a=5;
+show status like 'Handler_read_rnd_next';
+
+# ... as compared to this, which will scan both partitions
+flush status;
+update t1 set a=100 where a+1=5+1;
+show status like 'Handler_read_rnd_next';
+
+# Same as above for DELETE:
+flush status;
+delete from t1 where a=5;
+show status like 'Handler_read_rnd_next';
+
+flush status;
+delete from t1 where a+1=5+1;
+show status like 'Handler_read_rnd_next';
+
+# Same as above multi-table UPDATE/DELETE
+create table t2 like t1;
+insert into t2 select * from t2;
+
+flush status;
+update t1,t2 set t1.a=1000, t2.a=1000 where t1.a=5 and t2.a=5;
+show status like 'Handler_read_rnd_next';
+# ^ This shows 3 accesses, these are caused by const table reads.
+# They should vanish when partition pruning results are used.
+
+flush status;
+delete t1,t2 from t1, t2 where t1.a=5 and t2.a=5;
+show status like 'Handler_read_rnd_next';
+drop table t1,t2;
# No tests for NULLs in RANGE(monotonic_expr()) - they depend on BUG#15447
# being fixed.
| Thread |
|---|
| • bk commit into 5.1 tree (sergefp:1.2083) | Sergey Petrunia | 26 Jan |
