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

Remove the requirement to have an INDEX on AUTO_INCREMENT column

    XMLWordPrintable

    Details

      Description

      After MDEV-6076 introduces persistent AUTO_INCREMENT for InnoDB, InnoDB does not really require an index to exist on AUTO_INCREMENT columns.
      The reason for requiring an index to exist was that before MDEV-6076, InnoDB used to execute the equivalent of SELECT MAX(autoinc_column) when loading a table definition.

      After MDEV-6076, there only are two cases where we need MAX(autoinc_column). The first one is upgrading an old dataset where the persistent AUTO_INCREMENT is not present (but the AUTO_INCREMENT column is necessarily indexed, because up to now, InnoDB always required the index to exist).

      The second reason is the following use case:

      CREATE TABLE t (autoinc_column SERIAL);
      INSERT INTO t SET autoinc_column=100;
      ALTER TABLE t AUTO_INCREMENT = 1;
      INSERT INTO t SET autoinc_column=NULL;
      SELECT * FROM t;
      

      By default, ALGORITHM=INPLACE would reset the sequence to 1, while after ALGORITHM=COPY, the last INSERT would use a value larger than 100.

      For compatibility with ALGORITHM=COPY and MyISAM, the user-requested AUTO_INCREMENT=1 should be adjusted to be at least MAX(autoinc_column)+1.

      If we want to maintain the ALTER TABLE compatibility, we could execute MAX(autoinc_column) on the SQL layer, using an index if one is available. The InnoDB internal code that requires the index to exist can be removed. Even if an index is not available, a table scan would be a lot faster than executing ALGORITHM=COPY.

      Most of this work should be outside InnoDB. Maybe there should be a handler::table_flags() bit for specifying whether an index on AUTO_INCREMENT column is required.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              marko Marko Mäkelä
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated: