0

I have a table in magento where many records will be deleted (about 50k rows). I need to make id as little as possible as it's used as reference to records, so I want to reuse deleted ids. Could you tell me how to reuse id reliably in multiuser enviroment?' I tried to make trigger that set id if there any ids to reuse:

DELIMITER $$ CREATE TRIGGER product_BINS BEFORE INSERT ON product FOR EACH ROW BEGIN DECLARE reused_id integer;
DECLARE semaphore_id integer;

SET @semaphore_id = (SELECT id FROM semaphore WHERE id = 1 FOR UPDATE);
SET @reused_id = (SELECT MIN(id) FROM product_all_ids LEFT JOIN product ON id = product_id WHERE product_id IS NULL);

IF (NOT ISNULL(@reused_id)) THEN
    SET NEW.product_id = @reused_id;
END IF;

END

I use table semaphore like semaphore to avoid issues in multiuser system. I tried to test it and haven't found any issues so far. Does it make sense? Is it a viable solution?

Oleg
  • 2,733
  • 7
  • 39
  • 62
  • 2
    Why do you care how big the ids are? If the ids are being used to reference to other tables, then it is a really bad idea to try to change them. – Gordon Linoff Sep 17 '14 at 11:57
  • What you want to do is not a good practice. Please check this [same question](http://stackoverflow.com/questions/2214141/mysql-auto-increment-after-delete) – Tushar Bhaware Sep 17 '14 at 12:38
  • The problem is ids are use to refer item when people communicate. The short this code - the better. – Oleg Sep 17 '14 at 13:30

0 Answers0