Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-18188

Maintain persistent COUNT(*) in InnoDB

    Details

      Description

      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.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                marko Marko Mäkelä
                Reporter:
                marko Marko Mäkelä
              • Votes:
                1 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated: