ALTER TABLE slots
  ADD COLUMN event_slot_id INT NULL AFTER event_id;

SET @prev_event_id := 0;
SET @seq := 0;

UPDATE slots s
INNER JOIN (
  SELECT
    t.id,
    t.event_id,
    (@seq := IF(@prev_event_id = t.event_id, @seq + 1, 1)) AS seq_value,
    (@prev_event_id := t.event_id) AS _event_marker
  FROM (
    SELECT id, event_id
    FROM slots
    ORDER BY event_id ASC, id ASC
  ) t
) seq_map ON seq_map.id = s.id
SET s.event_slot_id = seq_map.seq_value;

ALTER TABLE slots
  MODIFY COLUMN event_slot_id INT NOT NULL,
  ADD UNIQUE KEY uq_slots_event_local_id (event_id, event_slot_id);