3

I have an InooDB table:

  • id - primary key, auto increment
  • short_code - varchar(6), unique
  • some other columns

The short_code is always generated in php based on id. The problem is that I can't generate the short_code before inserting a record, because I don't know the id.

I tried the following:

  1. insert a record with empty short_code
  2. get the id of the inserted row with mysqli_insert_id, or pdo, whatever
  3. generate the short code based on the id, and update the record with the new short_code

This works, but this is really slow. I have to do an insert and also an update, which is time consuming.

A better idea is to "guess" the id the record I want to insert will have, with select max(id)+1 from mytable; (or something similar), generate the short_code, and insert the record. Here I have a SELECT and one INSERT which is actually much better.

My question is:

  1. Is there a smarter solution for this problem?
  2. If no, and I go with solution 2 (guessing the next id), how can I make sure that in the time I do the select max(id)+1 from mytable;, generate short code, and insert a record, nobody will insert another record, so max(id)=1 doesn't change in meantime?

NOTE!

  • I can not generate the short code in a mysql procedure.
  • Short code must be generated based on the id.
Tamás Pap
  • 17,777
  • 15
  • 70
  • 102
  • you are updating based on the PK; it should not be so much faster then a select+insert. Check if there is no another problem with the queries. Can you post your benchmarks? – Maxim Krizhanovsky Dec 25 '12 at 10:28

3 Answers3

3

Use a DB trigger that can generate the code while inserting.

Example (not knowing on what rules you generate your short_code):

delimiter $$
create trigger code_gen after insert on your_table
for each row
begin
   SET NEW.short_code = NEW.id + 1;
end
$$
delimiter ;
juergen d
  • 201,996
  • 37
  • 293
  • 362
  • It can be a good idea, but I would love to make the short code generation on php side. This short code is an easy to read 6 char long string (ex: 'awexo', 'opeda', 'ikami', etc.), generated based on the id. I don't want to implement this algorithm in mysql if I have another choise. Btw, this is the generator I implemented: http://pastebin.com/gTSHUZch – Tamás Pap Dec 25 '12 at 10:18
2

Auto-inc value is generated when insert occurs, with a locking technique (to avoid generation on two same values) that depends on configuration and you cannot guess it. In a transactional environment many transaction can require a new id, but they may or may not commit, so at the time of the query, the value is reserved, but still not used.

If you want to "guess" the correct value, you have to lock the entire table for write, to avoid another thread to insert a record and to break your guess. This will work if you don't have a lot of writes on the table, e.g. for few dozen inserts per second.

If there are a lot of updates on this table and write lock is not an option, you can use another table to generate the values (it will have only the id field), and instead of insert/update you will have insert/insert, assuming that the 2 operations will be as fast as they can.

Maxim Krizhanovsky
  • 26,265
  • 5
  • 59
  • 89
1

Checkout below mentioned URL(thinkswan's answer)
Most efficient way to get table row count
This way you can get auto-increment value. In your case, you can get the id as id is auto-increment. This way, your first problem will be solved.
Then all you have to do is to use id to generate short_code and insert a record.

Community
  • 1
  • 1
Bhavik Shah
  • 2,300
  • 1
  • 17
  • 32