Best practise to reset a database index once a year?

Question

Using php and mysql (and Drupal), I want to generate an index based on the number of items in the database by year - 20090001, 20090002, 20090003, etc.

Is there a way to do this without testing if the year has changed for each and every item - a cron job perhaps?

Answer 1

What you can do is have a multi-column primary key, one column for the year and one for the id. You still set auto-increment on the id column and mysql sorts out the increments for each year you add in the year column. The only caveat is you need to add the year to the list of values when you do an insert. The details are here.

EDIT: This only works on MyISAM tables