counter per year, MySQL

Language: Layout:

drop table if exists bills;
CREATE TABLE bills (
    id_interess     INT UNSIGNED    NOT NULL,
    id_bill         VARCHAR(30)     NULL,
    PRIMARY KEY (id_interess)
) ENGINE=InnoDB;

set @sql = "
    insert into bills(id_interess, id_bill)
        select ?, concat(date_format(?, '%Y%m%d-'), coalesce(max(substr(id_bill, 10) + 1), 1))
        from bills 
        where id_bill like concat(year(?), '%')
";
prepare stmt from @sql;
set @i = 0;

-- insert first entry for this year with current date:
set @i = @i+1, @date = curdate(); execute stmt using @i, @date, @date;
-- dates can be inserted out of order
set @i = @i+1, @date = '2017-06-01'; execute stmt using @i, @date, @date;
set @i = @i+1, @date = '2017-05-01'; execute stmt using @i, @date, @date;
-- you can insert dates from 2016 even when entries for 2017 already exist
set @i = @i+1, @date = '2016-06-01'; execute stmt using @i, @date, @date;
set @i = @i+1, @date = '2016-05-01'; execute stmt using @i, @date, @date;
-- next year
set @i = @i+1, @date = '2018-01-02'; execute stmt using @i, @date, @date;
-- the counter is casted to int, so there is no issue with MAX
set @i = @i+1, @date = curdate(); execute stmt using @i, @date, @date;
set @i = @i+1, @date = curdate(); execute stmt using @i, @date, @date;
set @i = @i+1, @date = curdate(); execute stmt using @i, @date, @date;
set @i = @i+1, @date = curdate(); execute stmt using @i, @date, @date;
set @i = @i+1, @date = curdate(); execute stmt using @i, @date, @date;
set @i = @i+1, @date = curdate(); execute stmt using @i, @date, @date;
set @i = @i+1, @date = curdate(); execute stmt using @i, @date, @date;
set @i = @i+1, @date = curdate(); execute stmt using @i, @date, @date;
set @i = @i+1, @date = curdate(); execute stmt using @i, @date, @date;

select * from bills;


absolute service time: 0,4 sec