Bug #102335 Add support for INSTANT algorithm for changing the auto-increment value
Submitted: 21 Jan 16:57 Modified: 22 Jan 12:33
Reporter: Yann Odeyer Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[21 Jan 16:57] Yann Odeyer
Description:
Hello, 

For testing, between each test, you may need to delete all rows and set the auto_increment counter to 0 for a lot of tables. Truncating a table or changing the auto-increment value rebuilds the table, which can be slow on very small tables.
A solution is to add support for INSTANT algorithm for changing the auto-increment value.

Thank you,

How to repeat:
mysql> CREATE TABLE foo (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.03 sec)

* Using "TRUNCATE"
mysql> TRUNCATE TABLE foo;
Query OK, 0 rows affected (0.05 sec)

* Using "DELETE" + "ALTER TABLE"
mysql> DELETE FROM foo; ALTER TABLE foo AUTO_INCREMENT=0;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Suggested fix:
Add support for INSTANT algorithm for changing the auto-increment value (like MariaDB) : 
https://mariadb.com/kb/en/innodb-online-ddl-operations-with-the-instant-alter-algorithm/#a....
[22 Jan 12:33] MySQL Verification Team
Hello Yann,

Thank you for the feature request!

regards,
Umesh