The auto increment field should reset to 1 on any change in column 1 or 2. The Primary Key looks like this. account_id (INT) order_date (DATE) order_id (INT) order_id should reset to 1 on a new account_id or new order_date. Now I know this can work natively using MyISAM (all the other tables in this schema are InnoDB), however DevOps said no ...
When a row is deleted from a table, its auto incremented id is not re-used. MySQL continues generating new numbers sequentially. By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record. To let AUTO_INCREMENT sequence start with another value , use AUTO_INCREMENT = 10.