Maintain persistent COUNT(*) in InnoDB

Query planning needs to know the number of records in a table. Currently, InnoDB only provides an estimate of this.

If InnoDB kept accurate track of the number of records in a table, then it would not only benefit statistics, but also limited cases like the following:

SELECT COUNT(*) FROM t LOCK IN SHARE MODE;

SELECT COUNT(*) FROM t FOR UPDATE;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT COUNT(*) FROM t;

At the moment, InnoDB would scan the entire table in order to count the records. If we maintained a durable count of committed records, we could instantly return the results for the above cases. The locking variants would simply lock the table. For the default REPEATABLE READ we would still have to count the records.

However, if we additionally maintained a count of uncommitted rows, then we could have instant COUNT in most cases:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT COUNT(*) FROM t;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- instantaneous if committed count = uncommitted count

SELECT COUNT(*) FROM t;

These counts could possibly be maintained in the clustered index root page. If this hurts concurrency too much (in MDEV-6076, storing the persistent AUTO_INCREMENT there did not seem to hurt), then we could partition the two counters into multiple pages.