Optimal SQL solution

Question

I have notification table (very large table). I need your help with the below scenario:

1. select all notifications for user id;
2. insert these notifications in notification_log table;
3. delete all these notifications from the notification table.

My thoughts :

1. create flag column in notification table and create on update trigger on it to do steps 2 and 3 in the above scenario. Draw Backs: trigger complicity o(n) and I am always select bulk of notification so it will be not nice for performance;
2. create simple SQL procedure to do the above scenario. Draw Backs: what if step of the scenario failed to commit the whole procedure will roll-back.

Can you help me to optimize this?

• jean: Triggers aren't a good practice when implementing business rules. If you need to implement this in the DB side put it in a separate SP and call it from the first SP

Answer 1

How about only SQL statements! no triggers, and no SPs; within a transaction for integrity:

START TRANSACTION;
INSERT INTO notification_log SELECT field1, field2, ... FROM notification_table WHERE user_id=123;
DELETE FROM notification_table WHERE user_id=123;
COMMIT;

Make sure you have an index on user_id.

HTH

Answer 2

Divide and conquer: write a simple stored procedure to perform step 2 (step2proc), then a simple stored procedure to perform step 3 (step3proc).

I would then write a master stored procedure to loop over all different users on the notification table and invoke step2proc and step3proc one user at a time. Be careful and use error handling. This should be a one-time really long execution (in terms of performance issue. You can later run the master stored procedure periodically with a lesser impact on performance.

Answer 3

A delete trigger for notification:

CREATE TRIGGER ... AFTER DELETE ON notification
FOR EACH ROW
BEGIN
 INSERT INTO notification_log (user_id, ...)
 VALUES (OLD.user_id, ...);
END;

Then delete from that table:

DELETE FROM notification WHERE user_id = ...;

No need to select any rows