List:Commits« Previous MessageNext Message »
From:Sergey Petrunia Date:January 26 2006 5:17pm
Subject:bk commit into 5.1 tree (sergefp:1.2083)
View as plain text  
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 Petrunia26 Jan