Bug #29162 show table status row counts for innodb include deleted but unpurged rows

• Submitted: 17 Jun 2007 17:27
• Modified: 22 Jun 2007 21:28
• Reporter: Shane Bester (Platinum Quality Contributor)
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S4 (Feature request)
• Version: 5.0.44, 5.1.20
• OS: Any
• Assigned to: Assigned Account
• CPU Architecture: Any
• Tags: innodb, purge, row count

[17 Jun 2007 17:27] Shane Bester

Description:
maybe not a real bug, but possibly something that could adversely affect approximate row counts.

if you have a large table, t1, and then do 'delete from t1',
show table status will reflect delete-marked rows until the purge thread has purged them all. 

this can take a long time on a large batch of deletes

How to repeat:
will upload testcase later

Suggested fix:
not let show table status consider records to be purged?  can it skip them?

[22 Jun 2007 17:29] MySQL Verification Team

Small testcase to show what I mean.  Notice how the show table status shows decreasing rowcount each second (as innodb purges the rows).  select count(*) and checksum table report 0 because the table is really empty. 

drop table if exists bug29162;
create table bug29162(id int not null auto_increment primary key,a char(255))engine=innodb;
start transaction;
insert into bug29162(a) values ('a');
insert into bug29162(a) select a from bug29162;
insert into bug29162(a) select a from bug29162;
insert into bug29162(a) select a from bug29162;
insert into bug29162(a) select a from bug29162;
insert into bug29162(a) select a from bug29162;
insert into bug29162(a) select a from bug29162;
insert into bug29162(a) select a from bug29162;
insert into bug29162(a) select a from bug29162;
insert into bug29162(a) select a from bug29162;
insert into bug29162(a) select a from bug29162;
insert into bug29162(a) select a from bug29162;
insert into bug29162(a) select a from bug29162;
insert into bug29162(a) select a from bug29162;
insert into bug29162(a) select a from bug29162;
insert into bug29162(a) select a from bug29162;
insert into bug29162(a) select a from bug29162;
insert into bug29162(a) select a from bug29162;
insert into bug29162(a) select a from bug29162;
insert into bug29162(a) select a from bug29162;
commit;
show table status like 'bug29162';
delete from bug29162 limit 100000000;
commit;
show table status like 'bug29162';
select sleep(1);
show table status like 'bug29162';
select sleep(1);
show table status like 'bug29162';
select sleep(1);
show table status like 'bug29162';
select sleep(1);
show table status like 'bug29162';
select sleep(1);
show table status like 'bug29162';
select sleep(1);
show table status like 'bug29162';
select sleep(1);
show table status like 'bug29162';
select sleep(1);

[25 Jun 2007 13:54] Heikki Tuuri

For the SQL optimizer, it may be better to count also the delete-marked, unpurged rows.

For SHOW TABLE STATUS, it would be nice to estimate only the existing row count.

Marking this as a feature request.