MySQL autogenerated id vs custom autogenerated id

Question

I have to say that this is my first question here. Currently I have a table ´journals´ in a MySQL server with an ID autogenerate with autoincrement:

´id´ int(10) NOT NULL AUTO_INCREMENT,
-- more columns
PRIMARY KEY (´id´)

I'm thinking about modify this ID for a custom one because the client would like to generate the id in the following way:

ABC0000001, where ´ABC´ is the acronym of a department and ´0000001´ would be autoincrement. We already have different acronyms for different deparments.

I can get this ID with a trigger (a similar example: How to make MySQL table primary key auto increment with some prefix) but I have doubts about perfomance and efficiency because I could get the same "result" with the initial solution and another column to store the department´s acronym. In the backend I would create a method to unified both columns before returning the result to the frontend and another method to split the search into the table.

Does anybody have faced a similar situation that could guide me on this issue in terms of perfomance or efficiency? Thanks in advance.

is it necessary that the numbers for one department in sequence ? or can it 'ABC0000001', 'DEF0000002','ABC0000003' ? — Bernd Buffen, Mar 31 '17 at 11:25
It´s not necessary that the numbers for one department in sequence. Your example is right. — J. Morales, Apr 03 '17 at 06:43

score -1 · Accepted Answer · answered Apr 01 '17 at 20:28

Creating custom-generated ids is fun, but masochistic. What is your subconscious goal?

AUTO_INCREMENT is well optimized; anything else is somewhere between slightly and seriously less efficient. For example, a Trigger, might double the cost of an INSERT, but if INSERTs are not a performance problem, then "so what".

Semi-related: UUIDs are terribly inefficient for huge tables -- due to their randomness.

MySQL autogenerated id vs custom autogenerated id

1 Answers1