=== added file 'mysql-test/include/ndb_init_scan_counts.inc' --- mysql-test/include/ndb_init_scan_counts.inc 1970-01-01 00:00:00 +0000 +++ mysql-test/include/ndb_init_scan_counts.inc 2009-04-08 09:09:52 +0000 @@ -0,0 +1,6 @@ +--disable_query_log +--disable_result_log +select @ndb_init_scan_count:=VARIABLE_VALUE from information_schema.session_status where variable_name like 'NDB_SCAN_COUNT'; +select @ndb_init_pruned_scan_count:=VARIABLE_VALUE from information_schema.session_status where variable_name like 'NDB_PRUNED_SCAN_COUNT'; +--enable_query_log +--enable_result_log === added file 'mysql-test/include/ndb_scan_counts.inc' --- mysql-test/include/ndb_scan_counts.inc 1970-01-01 00:00:00 +0000 +++ mysql-test/include/ndb_scan_counts.inc 2009-04-08 09:18:45 +0000 @@ -0,0 +1,4 @@ +--disable_query_log +select @ndb_scan_count:=VARIABLE_VALUE-@ndb_init_scan_count AS ndb_scan_count from information_schema.session_status where variable_name like 'NDB_SCAN_COUNT'; +select @ndb_pruned_scan_count:=VARIABLE_VALUE-@ndb_init_pruned_scan_count AS ndb_pruned_scan_count from information_schema.session_status where variable_name like 'NDB_PRUNED_SCAN_COUNT'; +--enable_query_log === modified file 'mysql-test/suite/ndb/r/ndb_basic.result' --- mysql-test/suite/ndb/r/ndb_basic.result 2009-01-23 09:55:57 +0000 +++ mysql-test/suite/ndb/r/ndb_basic.result 2009-04-08 11:48:18 +0000 @@ -16,6 +16,8 @@ Ndb_number_of_data_nodes # Ndb_number_of_ready_data_nodes # Ndb_connect_count # Ndb_execute_count # +Ndb_scan_count # +Ndb_pruned_scan_count # Ndb_cluster_connection_pool # Ndb_conflict_fn_max # Ndb_conflict_fn_old # === modified file 'mysql-test/suite/ndb/r/ndb_partition_hash.result' --- mysql-test/suite/ndb/r/ndb_partition_hash.result 2009-03-06 18:06:17 +0000 +++ mysql-test/suite/ndb/r/ndb_partition_hash.result 2009-04-08 09:21:15 +0000 @@ -13,11 +13,23 @@ insert into t1 values (2,0,0,0,0),(4,0,0 insert into t1 values (0,1,1,4,0),(0,1,1,5,0); UPDATE t1 force index (primary) SET ol_tmp = 1 WHERE ol_o_id = 0 AND ol_d_id = 1 AND ol_w_id = 1; +ndb_scan_count +1 +ndb_pruned_scan_count +1 SELECT * FROM t1 WHERE ol_w_id = 1 AND ol_d_id = 1 AND ol_o_id = 0; ol_o_id ol_d_id ol_w_id ol_number ol_tmp 0 1 1 4 1 0 1 1 5 1 +ndb_scan_count +1 +ndb_pruned_scan_count +1 SELECT SUM(ol_number) FROM t1 WHERE ol_o_id = 0 AND ol_w_id = 1 AND ol_d_id = 1; SUM(ol_number) 9 +ndb_scan_count +1 +ndb_pruned_scan_count +1 drop table t1; === modified file 'mysql-test/suite/ndb/r/ndb_partition_key.result' --- mysql-test/suite/ndb/r/ndb_partition_key.result 2009-01-19 10:49:56 +0000 +++ mysql-test/suite/ndb/r/ndb_partition_key.result 2009-04-08 10:25:59 +0000 @@ -287,3 +287,61 @@ delete from t1 where a = 5; select * from t1 order by 1; a b c drop table t1; +create table nott1 (a int, +b int, +c int, +primary key(a,b)) +engine=ndb partition by key(a); +insert into nott1 values (0,0,0); +insert into nott1 values (0,1,1); +insert into nott1 values (0,2,2); +insert into nott1 values (0,3,3); +insert into nott1 values (1,0,4); +insert into nott1 values (1,1,5); +insert into nott1 values (1,2,6); +insert into nott1 values (2,22,7); +All partitions scan +select * from nott1 order by c; +a b c +0 0 0 +0 1 1 +0 2 2 +0 3 3 +1 0 4 +1 1 5 +1 2 6 +2 22 7 +ndb_scan_count +1 +ndb_pruned_scan_count +0 +Single partition scan +select * from nott1 where a=0 order by c; +a b c +0 0 0 +0 1 1 +0 2 2 +0 3 3 +ndb_scan_count +1 +ndb_pruned_scan_count +1 +Single partition scan +select * from nott1 where a=4 order by c; +a b c +ndb_scan_count +1 +ndb_pruned_scan_count +1 +MRR scan on one partition +select * from nott1 where a=0 and (( b > 0 and b < 3) or +( b > 5 and b < 10) or +( b > 22 and b < 50)) order by c; +a b c +0 1 1 +0 2 2 +ndb_scan_count +1 +ndb_pruned_scan_count +1 +drop table nott1; === modified file 'mysql-test/suite/ndb/r/ndb_partition_list.result' --- mysql-test/suite/ndb/r/ndb_partition_list.result 2007-06-27 12:28:02 +0000 +++ mysql-test/suite/ndb/r/ndb_partition_list.result 2009-04-08 10:28:07 +0000 @@ -49,3 +49,63 @@ f_int1 f_int2 f_char1 f_char2 f_charbig 1 1 1 1 ===1=== 2 1 1 1 ===1=== DROP TABLE t1; +create table t1 ( a int, b int, c int, primary key (a,b)) engine=ndb +partition by list (a) +(partition part0 values in (0,1,2), +partition part1 values in (3,4,5)); +insert into t1 values (0, 0, 0); +insert into t1 values (0, 1, 1); +insert into t1 values (0, 2, 2); +insert into t1 values (1, 0, 3); +insert into t1 values (1, 1, 4); +insert into t1 values (1, 2, 5); +insert into t1 values (4, 0, 6); +insert into t1 values (4, 1, 7); +insert into t1 values (4, 2, 8); +All partitions scan +select * from t1 order by c; +a b c +0 0 0 +0 1 1 +0 2 2 +1 0 3 +1 1 4 +1 2 5 +4 0 6 +4 1 7 +4 2 8 +ndb_scan_count +1 +ndb_pruned_scan_count +0 +Single partition scan +select * from t1 where a=0 order by c; +a b c +0 0 0 +0 1 1 +0 2 2 +ndb_scan_count +1 +ndb_pruned_scan_count +1 +Single partition scan +select * from t1 where a=4 order by c; +a b c +4 0 6 +4 1 7 +4 2 8 +ndb_scan_count +1 +ndb_pruned_scan_count +1 +MRR single partition scan +select * from t1 where a in (0, 2) order by c; +a b c +0 0 0 +0 1 1 +0 2 2 +ndb_scan_count +1 +ndb_pruned_scan_count +1 +drop table t1; === modified file 'mysql-test/suite/ndb/r/ndb_partition_range.result' --- mysql-test/suite/ndb/r/ndb_partition_range.result 2009-01-29 13:17:17 +0000 +++ mysql-test/suite/ndb/r/ndb_partition_range.result 2009-04-08 10:37:14 +0000 @@ -287,3 +287,76 @@ f1 f2 5 ---1--- 6 ---2--- drop table t1; +create table t1 ( a int, b int, c int, primary key (a,b)) engine=ndb +partition by range (a) +(partition part0 values less than (3), +partition part1 values less than (6)); +insert into t1 values (0, 0, 0); +insert into t1 values (0, 1, 1); +insert into t1 values (0, 2, 2); +insert into t1 values (1, 0, 3); +insert into t1 values (1, 1, 4); +insert into t1 values (1, 2, 5); +insert into t1 values (4, 0, 6); +insert into t1 values (4, 1, 7); +insert into t1 values (4, 2, 8); +All partitions scan +select * from t1 order by c; +a b c +0 0 0 +0 1 1 +0 2 2 +1 0 3 +1 1 4 +1 2 5 +4 0 6 +4 1 7 +4 2 8 +ndb_scan_count +1 +ndb_pruned_scan_count +0 +Single partition scan +select * from t1 where a=0 order by c; +a b c +0 0 0 +0 1 1 +0 2 2 +ndb_scan_count +1 +ndb_pruned_scan_count +1 +Single partition scan +select * from t1 where a=4 order by c; +a b c +4 0 6 +4 1 7 +4 2 8 +ndb_scan_count +1 +ndb_pruned_scan_count +1 +Single partition scan +select * from t1 where a<3 order by c; +a b c +0 0 0 +0 1 1 +0 2 2 +1 0 3 +1 1 4 +1 2 5 +ndb_scan_count +1 +ndb_pruned_scan_count +1 +MRR single partition scan +select * from t1 where a in (0, 2) order by c; +a b c +0 0 0 +0 1 1 +0 2 2 +ndb_scan_count +1 +ndb_pruned_scan_count +1 +drop table t1; === modified file 'mysql-test/suite/ndb/t/ndb_partition_hash.test' --- mysql-test/suite/ndb/t/ndb_partition_hash.test 2009-03-06 18:06:17 +0000 +++ mysql-test/suite/ndb/t/ndb_partition_hash.test 2009-04-08 09:20:40 +0000 @@ -24,6 +24,8 @@ drop table if exists t1; # Testcase from Mikael Ronstrom. # Note that the testcase did not fail previously, but accessed # partitions unnecessarily +# Testcase now checks that all scan accesses are pruned at +# NDBAPI level # CREATE TABLE t1 ( ol_o_id int NOT NULL, @@ -39,11 +41,18 @@ insert into t1 values (1,0,0,0,0),(1,0,0 insert into t1 values (2,0,0,0,0),(4,0,0,0,0),(4,0,0,1,0); insert into t1 values (0,1,1,4,0),(0,1,1,5,0); # Only rows affected. +--source include/ndb_init_scan_counts.inc UPDATE t1 force index (primary) SET ol_tmp = 1 WHERE ol_o_id = 0 AND ol_d_id = 1 AND ol_w_id = 1; +--source include/ndb_scan_counts.inc +--source include/ndb_init_scan_counts.inc SELECT * FROM t1 WHERE ol_w_id = 1 AND ol_d_id = 1 AND ol_o_id = 0; +--source include/ndb_scan_counts.inc + +--source include/ndb_init_scan_counts.inc SELECT SUM(ol_number) FROM t1 WHERE ol_o_id = 0 AND ol_w_id = 1 AND ol_d_id = 1; +--source include/ndb_scan_counts.inc drop table t1; === modified file 'mysql-test/suite/ndb/t/ndb_partition_key.test' --- mysql-test/suite/ndb/t/ndb_partition_key.test 2008-09-02 16:31:03 +0000 +++ mysql-test/suite/ndb/t/ndb_partition_key.test 2009-04-08 09:37:25 +0000 @@ -242,3 +242,45 @@ delete from t1 where a = 5; select * from t1 order by 1; drop table t1; + +# Create table with distribution key != partition key to +# test some partition pruning stuff +# +create table nott1 (a int, + b int, + c int, + primary key(a,b)) + engine=ndb partition by key(a); + +insert into nott1 values (0,0,0); +insert into nott1 values (0,1,1); +insert into nott1 values (0,2,2); +insert into nott1 values (0,3,3); +insert into nott1 values (1,0,4); +insert into nott1 values (1,1,5); +insert into nott1 values (1,2,6); +insert into nott1 values (2,22,7); + +--echo All partitions scan +--source include/ndb_init_scan_counts.inc +select * from nott1 order by c; +--source include/ndb_scan_counts.inc + +--echo Single partition scan +--source include/ndb_init_scan_counts.inc +select * from nott1 where a=0 order by c; +--source include/ndb_scan_counts.inc + +--echo Single partition scan +--source include/ndb_init_scan_counts.inc +select * from nott1 where a=4 order by c; +--source include/ndb_scan_counts.inc + +--echo MRR scan on one partition +--source include/ndb_init_scan_counts.inc +select * from nott1 where a=0 and (( b > 0 and b < 3) or + ( b > 5 and b < 10) or + ( b > 22 and b < 50)) order by c; +--source include/ndb_scan_counts.inc + +drop table nott1; === modified file 'mysql-test/suite/ndb/t/ndb_partition_list.test' --- mysql-test/suite/ndb/t/ndb_partition_list.test 2007-07-04 20:38:53 +0000 +++ mysql-test/suite/ndb/t/ndb_partition_list.test 2009-04-08 10:30:42 +0000 @@ -66,3 +66,41 @@ SELECT * FROM t1 ORDER BY f_int1; DROP TABLE t1; +# Check partition pruning at NdbApi level for list partitioned table +# +create table t1 ( a int, b int, c int, primary key (a,b)) engine=ndb +partition by list (a) +(partition part0 values in (0,1,2), + partition part1 values in (3,4,5)); + +insert into t1 values (0, 0, 0); +insert into t1 values (0, 1, 1); +insert into t1 values (0, 2, 2); +insert into t1 values (1, 0, 3); +insert into t1 values (1, 1, 4); +insert into t1 values (1, 2, 5); +insert into t1 values (4, 0, 6); +insert into t1 values (4, 1, 7); +insert into t1 values (4, 2, 8); + +--echo All partitions scan +--source include/ndb_init_scan_counts.inc +select * from t1 order by c; +--source include/ndb_scan_counts.inc + +--echo Single partition scan +--source include/ndb_init_scan_counts.inc +select * from t1 where a=0 order by c; +--source include/ndb_scan_counts.inc + +--echo Single partition scan +--source include/ndb_init_scan_counts.inc +select * from t1 where a=4 order by c; +--source include/ndb_scan_counts.inc + +--echo MRR single partition scan +--source include/ndb_init_scan_counts.inc +select * from t1 where a in (0, 2) order by c; +--source include/ndb_scan_counts.inc + +drop table t1; === modified file 'mysql-test/suite/ndb/t/ndb_partition_range.test' --- mysql-test/suite/ndb/t/ndb_partition_range.test 2009-01-29 13:17:17 +0000 +++ mysql-test/suite/ndb/t/ndb_partition_range.test 2009-04-08 10:34:34 +0000 @@ -274,3 +274,48 @@ select * from t1 order by f1; UPDATE t1 SET f1 = f1 + 4 WHERE f1 = 1; select * from t1 order by f1; drop table t1; + + +# Check partitioning at NdbApi level for range partitioned table +# +create table t1 ( a int, b int, c int, primary key (a,b)) engine=ndb +partition by range (a) +(partition part0 values less than (3), + partition part1 values less than (6)); + +insert into t1 values (0, 0, 0); +insert into t1 values (0, 1, 1); +insert into t1 values (0, 2, 2); +insert into t1 values (1, 0, 3); +insert into t1 values (1, 1, 4); +insert into t1 values (1, 2, 5); +insert into t1 values (4, 0, 6); +insert into t1 values (4, 1, 7); +insert into t1 values (4, 2, 8); + +--echo All partitions scan +--source include/ndb_init_scan_counts.inc +select * from t1 order by c; +--source include/ndb_scan_counts.inc + +--echo Single partition scan +--source include/ndb_init_scan_counts.inc +select * from t1 where a=0 order by c; +--source include/ndb_scan_counts.inc + +--echo Single partition scan +--source include/ndb_init_scan_counts.inc +select * from t1 where a=4 order by c; +--source include/ndb_scan_counts.inc + +--echo Single partition scan +--source include/ndb_init_scan_counts.inc +select * from t1 where a<3 order by c; +--source include/ndb_scan_counts.inc + +--echo MRR single partition scan +--source include/ndb_init_scan_counts.inc +select * from t1 where a in (0, 2) order by c; +--source include/ndb_scan_counts.inc + +drop table t1; === modified file 'sql/ha_ndbcluster.cc' --- sql/ha_ndbcluster.cc 2009-03-06 18:06:17 +0000 +++ sql/ha_ndbcluster.cc 2009-04-07 16:18:07 +0000 @@ -192,6 +192,8 @@ struct st_ndb_status { long number_of_ready_data_nodes; long connect_count; long execute_count; + long scan_count; + long pruned_scan_count; }; static struct st_ndb_status g_ndb_status; @@ -222,6 +224,8 @@ static int update_status_variables(Thd_n if (thd_ndb) { ns->execute_count= thd_ndb->m_execute_count; + ns->scan_count= thd_ndb->m_scan_count; + ns->pruned_scan_count= thd_ndb->m_pruned_scan_count; } return 0; } @@ -236,6 +240,8 @@ SHOW_VAR ndb_status_variables_dynamic[]= (char*) &g_ndb_status.number_of_ready_data_nodes, SHOW_LONG}, {"connect_count", (char*) &g_ndb_status.connect_count, SHOW_LONG}, {"execute_count", (char*) &g_ndb_status.execute_count, SHOW_LONG}, + {"scan_count", (char*) &g_ndb_status.scan_count, SHOW_LONG}, + {"pruned_scan_count", (char*) &g_ndb_status.pruned_scan_count, SHOW_LONG}, {NullS, NullS, SHOW_LONG} }; @@ -648,6 +654,8 @@ Thd_ndb::Thd_ndb() (hash_get_key)thd_ndb_share_get_key, 0, 0); m_unsent_bytes= 0; m_execute_count= 0; + m_scan_count= 0; + m_pruned_scan_count= 0; m_max_violation_count= 0; m_old_violation_count= 0; m_conflict_fn_usage_count= 0; @@ -2923,6 +2931,8 @@ int ha_ndbcluster::ordered_index_scan(co ERR_RETURN(trans->getNdbError()); DBUG_PRINT("info", ("Is scan pruned to 1 partition? : %u", op->getPruned())); + m_thd_ndb->m_scan_count++; + m_thd_ndb->m_pruned_scan_count += (op->getPruned()? 1 : 0); if (uses_blob_value(table->read_set) && get_blob_values(op, NULL, table->read_set) != 0) @@ -3074,6 +3084,9 @@ int ha_ndbcluster::full_table_scan(const (uchar *)(table->read_set->bitmap), &options, sizeof(NdbScanOperation::ScanOptions)))) ERR_RETURN(trans->getNdbError()); + + m_thd_ndb->m_scan_count++; + m_thd_ndb->m_pruned_scan_count += (op->getPruned()? 1 : 0); } m_active_cursor= op; @@ -10386,6 +10399,8 @@ ndb_get_table_statistics(THD *thd, ha_nd error= pTrans->getNdbError(); goto retry; } + thd_ndb->m_scan_count++; + thd_ndb->m_pruned_scan_count += (pOp->getPruned()? 1 : 0); thd_ndb->m_execute_count++; DBUG_PRINT("info", ("execute_count: %u", thd_ndb->m_execute_count)); @@ -10891,6 +10906,8 @@ ha_ndbcluster::read_multi_range_first(KE { DBUG_PRINT("info", ("Is MRR scan pruned to 1 partition? :%u", m_multi_cursor->getPruned())); + m_thd_ndb->m_scan_count++; + m_thd_ndb->m_pruned_scan_count += (m_multi_cursor->getPruned()? 1 : 0); }; if (any_real_read) === modified file 'sql/ha_ndbcluster.h' --- sql/ha_ndbcluster.h 2009-02-16 15:54:40 +0000 +++ sql/ha_ndbcluster.h 2009-04-07 15:40:30 +0000 @@ -354,6 +354,9 @@ class Thd_ndb uint m_old_violation_count; uint m_conflict_fn_usage_count; + uint m_scan_count; + uint m_pruned_scan_count; + NdbTransaction *global_schema_lock_trans; uint global_schema_lock_count; uint global_schema_lock_error;