Added Handler_read_rnd_deleted, number of deleted rows found with ha_read_rnd_first.

18 files changed, 98 insertions, 4 deletions

diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result
index 96abcd34b92..07b3f85d924 100644
--- a/mysql-test/r/create.result
+++ b/mysql-test/r/create.result
@@ -1556,6 +1556,7 @@ Handler_read_key	0
 Handler_read_next	0
 Handler_read_prev	0
 Handler_read_rnd	0
+Handler_read_rnd_deleted	0
 Handler_read_rnd_next	7
 drop table t1,t2;
 CREATE TABLE t1(c1 VARCHAR(33), KEY USING BTREE (c1));
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index cee19237230..7636f79189c 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -79,6 +79,7 @@ Handler_read_key	0
 Handler_read_next	0
 Handler_read_prev	0
 Handler_read_rnd	0
+Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
 flush status;
 select * from (select * from t1 where f1 in (2,3)) tt where f11=2;
@@ -91,6 +92,7 @@ Handler_read_key	0
 Handler_read_next	0
 Handler_read_prev	0
 Handler_read_rnd	0
+Handler_read_rnd_deleted	0
 Handler_read_rnd_next	12
 for merged views
 create view v1 as select * from t1;
@@ -162,6 +164,7 @@ Handler_read_key	0
 Handler_read_next	0
 Handler_read_prev	0
 Handler_read_rnd	0
+Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
 flush status;
 select * from v4 where f2 in (1,3);
@@ -174,6 +177,7 @@ Handler_read_key	0
 Handler_read_next	0
 Handler_read_prev	0
 Handler_read_rnd	0
+Handler_read_rnd_deleted	0
 Handler_read_rnd_next	12
 for materialized derived tables
 explain for simple derived
@@ -224,6 +228,7 @@ Handler_read_key	0
 Handler_read_next	0
 Handler_read_prev	0
 Handler_read_rnd	0
+Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
 flush status;
 select * from t1 join (select * from t2 group by f2) tt on f1=f2;
@@ -238,6 +243,7 @@ Handler_read_key	11
 Handler_read_next	3
 Handler_read_prev	0
 Handler_read_rnd	11
+Handler_read_rnd_deleted	0
 Handler_read_rnd_next	36
 for materialized views
 drop view v1,v2,v3;
@@ -311,6 +317,7 @@ Handler_read_key	22
 Handler_read_next	22
 Handler_read_prev	0
 Handler_read_rnd	0
+Handler_read_rnd_deleted	0
 Handler_read_rnd_next	60
 explain showing late materialization
 flush status;
@@ -326,6 +333,7 @@ Handler_read_key	0
 Handler_read_next	0
 Handler_read_prev	0
 Handler_read_rnd	0
+Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
 flush status;
 select * from t1 join v2 on f1=f2;
@@ -340,6 +348,7 @@ Handler_read_key	11
 Handler_read_next	3
 Handler_read_prev	0
 Handler_read_rnd	11
+Handler_read_rnd_deleted	0
 Handler_read_rnd_next	36
 explain extended select * from v1 join v4 on f1=f2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
@@ -433,6 +442,7 @@ Handler_read_key	2
 Handler_read_next	2
 Handler_read_prev	0
 Handler_read_rnd	8
+Handler_read_rnd_deleted	0
 Handler_read_rnd_next	39
 flush status;
 merged in merged derived join merged in merged derived
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index 9e84010d5ce..60defea291b 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -1240,6 +1240,7 @@ Handler_read_key	5
 Handler_read_next	0
 Handler_read_prev	0
 Handler_read_rnd	0
+Handler_read_rnd_deleted	0
 Handler_read_rnd_next	6
 DROP TABLE t1,t2;
 CREATE TABLE t1 (c int  PRIMARY KEY, e int NOT NULL);
@@ -1499,6 +1500,7 @@ Handler_read_key	4
 Handler_read_next	5
 Handler_read_prev	0
 Handler_read_rnd	0
+Handler_read_rnd_deleted	0
 Handler_read_rnd_next	1048581
 flush status;
 select sum(t3.b) from t2 left join t3 on t3.a=t2.a and t2.a <> 10;
@@ -1511,6 +1513,7 @@ Handler_read_key	4
 Handler_read_next	5
 Handler_read_prev	0
 Handler_read_rnd	0
+Handler_read_rnd_deleted	0
 Handler_read_rnd_next	1048581
 drop table t1,t2,t3;
 #
diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result
index ab9e437c6bf..767d0544f1b 100644
--- a/mysql-test/r/join_outer_jcl6.result
+++ b/mysql-test/r/join_outer_jcl6.result
@@ -1251,6 +1251,7 @@ Handler_read_key	5
 Handler_read_next	9
 Handler_read_prev	0
 Handler_read_rnd	3
+Handler_read_rnd_deleted	0
 Handler_read_rnd_next	6
 DROP TABLE t1,t2;
 CREATE TABLE t1 (c int  PRIMARY KEY, e int NOT NULL);
@@ -1510,6 +1511,7 @@ Handler_read_key	4
 Handler_read_next	5
 Handler_read_prev	0
 Handler_read_rnd	5
+Handler_read_rnd_deleted	0
 Handler_read_rnd_next	1048581
 flush status;
 select sum(t3.b) from t2 left join t3 on t3.a=t2.a and t2.a <> 10;
@@ -1522,6 +1524,7 @@ Handler_read_key	4
 Handler_read_next	5
 Handler_read_prev	0
 Handler_read_rnd	5
+Handler_read_rnd_deleted	0
 Handler_read_rnd_next	1048581
 drop table t1,t2,t3;
 #
diff --git a/mysql-test/r/null_key.result b/mysql-test/r/null_key.result
index 488110b88e3..a21f9190069 100644
--- a/mysql-test/r/null_key.result
+++ b/mysql-test/r/null_key.result
@@ -427,6 +427,7 @@ Handler_read_key	6
 Handler_read_next	2
 Handler_read_prev	0
 Handler_read_rnd	0
+Handler_read_rnd_deleted	0
 Handler_read_rnd_next	5
 DROP TABLE t1,t2,t3,t4;
 CREATE TABLE t1 (
diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result
index adf7ddac8b7..866a6fc1197 100644
--- a/mysql-test/r/partition.result
+++ b/mysql-test/r/partition.result
@@ -382,6 +382,7 @@ Handler_read_key	2
 Handler_read_next	4
 Handler_read_prev	0
 Handler_read_rnd	0
+Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
 EXPLAIN PARTITIONS SELECT c1 FROM t2 WHERE (c1 > 10 AND c1 < 13) OR (c1 > 17 AND c1 < 20);
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
@@ -400,6 +401,7 @@ Handler_read_key	2
 Handler_read_next	4
 Handler_read_prev	0
 Handler_read_rnd	0
+Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
 DROP TABLE t1,t2;
 CREATE TABLE `t1` (
@@ -430,6 +432,7 @@ Handler_read_key	1
 Handler_read_next	2
 Handler_read_prev	0
 Handler_read_rnd	0
+Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
 EXPLAIN PARTITIONS SELECT c1 FROM t2 WHERE (c1 > 2 AND c1 < 5);
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
@@ -446,6 +449,7 @@ Handler_read_key	1
 Handler_read_next	2
 Handler_read_prev	0
 Handler_read_rnd	0
+Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
 EXPLAIN PARTITIONS SELECT c1 FROM t1 WHERE (c1 > 12 AND c1 < 15);
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
@@ -462,6 +466,7 @@ Handler_read_key	1
 Handler_read_next	2
 Handler_read_prev	0
 Handler_read_rnd	0
+Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
 EXPLAIN PARTITIONS SELECT c1 FROM t2 WHERE (c1 > 12 AND c1 < 15);
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
@@ -478,6 +483,7 @@ Handler_read_key	1
 Handler_read_next	2
 Handler_read_prev	0
 Handler_read_rnd	0
+Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
 DROP TABLE t1,t2;
 create table t1 (a int) partition by list ((a/3)*10 div 1)
diff --git a/mysql-test/r/partition_pruning.result b/mysql-test/r/partition_pruning.result
index 6d6371e263f..d5594c7453e 100644
--- a/mysql-test/r/partition_pruning.result
+++ b/mysql-test/r/partition_pruning.result
@@ -2639,7 +2639,10 @@ flush status;
 delete from t2 where b > 5;
 show status like 'Handler_read_rnd_next';
 Variable_name	Value
-Handler_read_rnd_next	1215
+Handler_read_rnd_next	815
+show status like 'Handler_read_rnd_deleted';
+Variable_name	Value
+Handler_read_rnd_deleted	400
 show status like 'Handler_read_key';
 Variable_name	Value
 Handler_read_key	0
@@ -2653,7 +2656,10 @@ flush status;
 delete from t2 where b < 5 or b > 3;
 show status like 'Handler_read_rnd_next';
 Variable_name	Value
-Handler_read_rnd_next	1215
+Handler_read_rnd_next	515
+show status like 'Handler_read_rnd_deleted';
+Variable_name	Value
+Handler_read_rnd_deleted	700
 show status like 'Handler_read_key';
 Variable_name	Value
 Handler_read_key	0
diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result
index 08944da6182..09b3c1a3c67 100644
--- a/mysql-test/r/ps.result
+++ b/mysql-test/r/ps.result
@@ -3082,6 +3082,7 @@ Handler_read_key	1
 Handler_read_next	0
 Handler_read_prev	0
 Handler_read_rnd	0
+Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
 flush status;
 execute st;
@@ -3094,6 +3095,7 @@ Handler_read_key	1
 Handler_read_next	0
 Handler_read_prev	0
 Handler_read_rnd	0
+Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
 flush status;
 select * from t1 use index() where a=3;
@@ -3106,6 +3108,7 @@ Handler_read_key	0
 Handler_read_next	0
 Handler_read_prev	0
 Handler_read_rnd	0
+Handler_read_rnd_deleted	0
 Handler_read_rnd_next	8
 flush status;
 execute st;
@@ -3118,6 +3121,7 @@ Handler_read_key	1
 Handler_read_next	0
 Handler_read_prev	0
 Handler_read_rnd	0
+Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
 deallocate prepare st;
 drop table t1;
diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result
index fc0f7283981..9648860619b 100644
--- a/mysql-test/r/select_pkeycache.result
+++ b/mysql-test/r/select_pkeycache.result
@@ -4342,6 +4342,7 @@ Handler_read_key	2
 Handler_read_next	0
 Handler_read_prev	0
 Handler_read_rnd	0
+Handler_read_rnd_deleted	0
 Handler_read_rnd_next	6
 DROP TABLE t1, t2;
 CREATE TABLE t1 (f1 bigint(20) NOT NULL default '0',
diff --git a/mysql-test/r/status.result b/mysql-test/r/status.result
index 5579728e0b2..b0744726390 100644
--- a/mysql-test/r/status.result
+++ b/mysql-test/r/status.result
@@ -281,6 +281,7 @@ Handler_read_key	4
 Handler_read_next	0
 Handler_read_prev	0
 Handler_read_rnd	7
+Handler_read_rnd_deleted	0
 Handler_read_rnd_next	23
 Handler_rollback	0
 Handler_savepoint	0
@@ -296,7 +297,34 @@ Created_tmp_files	0
 Created_tmp_tables	2
 Handler_tmp_update	2
 Handler_tmp_write	7
-Rows_tmp_read	34
+Rows_tmp_read	35
+drop table t1;
+CREATE TABLE t1 (i int(11) DEFAULT NULL, KEY i (i) ) ENGINE=MyISAM;
+insert into t1 values (1),(2),(3),(4),(5);
+flush status;
+select * from t1 where i=5 union select * from t1 where i=5;
+i
+5
+show status like "handler%";
+Variable_name	Value
+Handler_commit	0
+Handler_delete	0
+Handler_discover	0
+Handler_prepare	0
+Handler_read_first	0
+Handler_read_key	2
+Handler_read_next	2
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_deleted	1
+Handler_read_rnd_next	2
+Handler_rollback	0
+Handler_savepoint	0
+Handler_savepoint_rollback	0
+Handler_tmp_update	0
+Handler_tmp_write	2
+Handler_update	0
+Handler_write	0
 drop table t1;
 set @@global.concurrent_insert= @old_concurrent_insert;
 SET GLOBAL log_output = @old_log_output;
diff --git a/mysql-test/r/status_user.result b/mysql-test/r/status_user.result
index bc5f0aff7e4..17c44df1d3c 100644
--- a/mysql-test/r/status_user.result
+++ b/mysql-test/r/status_user.result
@@ -106,6 +106,7 @@ Handler_read_key	3
 Handler_read_next	0
 Handler_read_prev	0
 Handler_read_rnd	0
+Handler_read_rnd_deleted	0
 Handler_read_rnd_next	5
 Handler_rollback	2
 Handler_savepoint	0
diff --git a/mysql-test/suite/pbxt/r/null_key.result b/mysql-test/suite/pbxt/r/null_key.result
index bef8c92419a..cc2b417b263 100644
--- a/mysql-test/suite/pbxt/r/null_key.result
+++ b/mysql-test/suite/pbxt/r/null_key.result
@@ -427,5 +427,6 @@ Handler_read_key	6
 Handler_read_next	2
 Handler_read_prev	0
 Handler_read_rnd	0
+Handler_read_rnd_deleted	0
 Handler_read_rnd_next	5
 DROP TABLE t1,t2,t3,t4;
diff --git a/mysql-test/suite/pbxt/r/update.result b/mysql-test/suite/pbxt/r/update.result
index 9154583fa81..46321d6f3bf 100644
--- a/mysql-test/suite/pbxt/r/update.result
+++ b/mysql-test/suite/pbxt/r/update.result
@@ -276,6 +276,7 @@ Handler_read_key	0
 Handler_read_next	0
 Handler_read_prev	0
 Handler_read_rnd	0
+Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
 flush status;
 update t1 set a=9999 order by a limit 1;
@@ -290,6 +291,7 @@ Handler_read_key	0
 Handler_read_next	0
 Handler_read_prev	0
 Handler_read_rnd	2
+Handler_read_rnd_deleted	0
 Handler_read_rnd_next	18
 flush status;
 delete from t1 order by a limit 1;
@@ -300,6 +302,7 @@ Handler_read_key	0
 Handler_read_next	0
 Handler_read_prev	0
 Handler_read_rnd	1
+Handler_read_rnd_deleted	0
 Handler_read_rnd_next	9
 flush status;
 delete from t1 order by a desc limit 1;
@@ -310,6 +313,7 @@ Handler_read_key	0
 Handler_read_next	0
 Handler_read_prev	0
 Handler_read_rnd	1
+Handler_read_rnd_deleted	0
 Handler_read_rnd_next	8
 alter table t1 disable keys;
 Warnings:
@@ -323,6 +327,7 @@ Handler_read_key	0
 Handler_read_next	0
 Handler_read_prev	0
 Handler_read_rnd	1
+Handler_read_rnd_deleted	0
 Handler_read_rnd_next	7
 select * from t1;
 a	b
@@ -376,6 +381,7 @@ Handler_read_key	1
 Handler_read_next	1
 Handler_read_prev	0
 Handler_read_rnd	1
+Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
 drop table t1, t2;
 create table t1(f1 int, `*f2` int);
diff --git a/mysql-test/t/partition_pruning.test b/mysql-test/t/partition_pruning.test
index f1ec8964769..f12593d746f 100644
--- a/mysql-test/t/partition_pruning.test
+++ b/mysql-test/t/partition_pruning.test
@@ -1086,12 +1086,14 @@ show status like 'Handler_read_key';
 flush status;
 delete from t2 where b > 5;
 show status like 'Handler_read_rnd_next';
+show status like 'Handler_read_rnd_deleted';
 show status like 'Handler_read_key';
 show status like 'Handler_read_prev';
 show status like 'Handler_read_next';
 flush status;
 delete from t2 where b < 5 or b > 3;
 show status like 'Handler_read_rnd_next';
+show status like 'Handler_read_rnd_deleted';
 show status like 'Handler_read_key';
 show status like 'Handler_read_prev';
 show status like 'Handler_read_next';
diff --git a/mysql-test/t/status.test b/mysql-test/t/status.test
index 46e454363cc..8cc938c923e 100644
--- a/mysql-test/t/status.test
+++ b/mysql-test/t/status.test
@@ -368,6 +368,16 @@ show status like 'Handler%';
 show status like '%tmp%';
 drop table t1;
 
+#
+# Test of handler status counts
+#
+CREATE TABLE t1 (i int(11) DEFAULT NULL, KEY i (i) ) ENGINE=MyISAM;
+insert into t1 values (1),(2),(3),(4),(5);
+flush status;
+select * from t1 where i=5 union select * from t1 where i=5;
+show status like "handler%";
+drop table t1;
+
 # End of 5.3 tests
 
 # Restore global concurrent_insert value. Keep in the end of the test file.
diff --git a/sql/ha_partition.cc b/sql/ha_partition.cc
index 290224850c7..03a879815e8 100644
--- a/sql/ha_partition.cc
+++ b/sql/ha_partition.cc
@@ -3938,6 +3938,8 @@ int ha_partition::rnd_next(uchar *buf)
   int result= HA_ERR_END_OF_FILE;
   uint part_id= m_part_spec.start_part;
   DBUG_ENTER("ha_partition::rnd_next");
+
+  /* upper level will increment this once again at end of call */
   decrement_statistics(&SSV::ha_read_rnd_next_count);
 
   if (NO_CURRENT_PART_ID == part_id)
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index 1a60d996fed..1312675f7b4 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -8294,6 +8294,7 @@ SHOW_VAR status_vars[]= {
   {"Handler_read_next",        (char*) offsetof(STATUS_VAR, ha_read_next_count), SHOW_LONG_STATUS},
   {"Handler_read_prev",        (char*) offsetof(STATUS_VAR, ha_read_prev_count), SHOW_LONG_STATUS},
   {"Handler_read_rnd",         (char*) offsetof(STATUS_VAR, ha_read_rnd_count), SHOW_LONG_STATUS},
+  {"Handler_read_rnd_deleted", (char*) offsetof(STATUS_VAR, ha_read_rnd_deleted_count), SHOW_LONG_STATUS},
   {"Handler_read_rnd_next",    (char*) offsetof(STATUS_VAR, ha_read_rnd_next_count), SHOW_LONG_STATUS},
   {"Handler_rollback",         (char*) offsetof(STATUS_VAR, ha_rollback_count), SHOW_LONG_STATUS},
   {"Handler_savepoint",        (char*) offsetof(STATUS_VAR, ha_savepoint_count), SHOW_LONG_STATUS},
diff --git a/sql/sql_class.h b/sql/sql_class.h
index da82ed5beed..f7d44eeec52 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -571,6 +571,7 @@ typedef struct system_status_var
   ulong ha_read_prev_count;
   ulong ha_read_rnd_count;
   ulong ha_read_rnd_next_count;
+  ulong ha_read_rnd_deleted_count;
   /*
     This number doesn't include calls to the default implementation and
     calls made by range access. The intent is to count only calls made by
@@ -3747,10 +3748,17 @@ inline int handler::ha_ft_read(uchar *buf)
 
 inline int handler::ha_rnd_next(uchar *buf)
 {
-  increment_statistics(&SSV::ha_read_rnd_next_count);
   int error= rnd_next(buf);
   if (!error)
+  {
     update_rows_read();
+    increment_statistics(&SSV::ha_read_rnd_next_count);
+  }
+  else if (error == HA_ERR_RECORD_DELETED)
+    increment_statistics(&SSV::ha_read_rnd_deleted_count);
+  else
+    increment_statistics(&SSV::ha_read_rnd_next_count);
+
   table->status=error ? STATUS_NOT_FOUND: 0;
   return error;
 }