Including deleted_at in unique index for upsert

Question

I have a table with a deleted_at column, see comment.

The deleted_at column behaves like a one-way boolean event flag, i.e. once a row is deleted it can't be restored.

There is also an asynchronous process that permanently deletes rows older than a certain timestamp. This process works in batches, for example: "pt-archiver nibbles records from a MySQL table"

For efficiently looking up rows to delete (and paging in general) the table has an id column as primary key. This column is generated in code, it's a GUID, and it's sortable by creation date.

primary key (`id`)

Deleting rows is a three step process

-- De-activate
update x set deleted_at = :timestamp where id = :id
-- Select IDs to delete
select id from x where deleted_at is not null
-- Hard delete
delete from x where id in (...)

Also, the table is updated in batches with an upsert query. The "on duplicate key update" clause makes use of a unique index consisting of two columns (code, deleted_at). The code must be unique over all active rows, deleted_at is null for active rows, and the upsert query looks like this

insert into x (id, code, foo, deleted_at)
values (...)
on duplicate key update foo=values(foo)

Is it a bad idea to make deleted_at part of a unique index, and if so why?

unique key `uniq_code_deleted_at`(`code`, `deleted_at`)

Answer 1

• By "GUID", are you referring to "KSUID"?
• A UNIQUE index confers a uniqueness constraint on the set of columns in it. Tacking on extra columns invalidates the constraint on simply code.
• When you talk about "deleting", are you referring to the "soft delete" using deleted_at? Or the "hard delete" using DELETE FROM t WHERE ...?
• Do you reference (say JOIN) this table from other tables ON the id? If not, why have the id column, why not?
• For (code, deleted_at) where code is unique, why add the extra column. Upsert will be happy to do nothing if you change deleted_at to the value it is currently set to. (Except it might "burn" and auto_increment id.)
• If you plan to "delete" most or all of a range of GUIDs at a time, don't bother pulling the ids out, that is an unnecessary extra step. But do bound the area to search.
• More suggestions on big deletes: http://mysql.rjweb.org/doc.php/deletebig

Please provide SHOW CREATE TABLE and the actual queries involved.

Possible DELETE

To preserve most recently de-activated rows it makes sense to select the IDs first

DELETE FROM t
       WHERE deleted_at IS NOT NULL
       ORDER BY deleted_at ASC
       LIMIT 100;

But that does not control how many of the "most recently deactivated rows" will be kept. So, let's flip that around, and use an OFFSET. Suppose you want to keep the most recent 50 and delete no more than 100 at a time:

DELETE FROM t
       WHERE deleted_at IS NOT NULL
       ORDER BY deleted_at DESC
       LIMIT 100  OFFSET 50;

INDEX(deleted_at) would help with either DELETE, but have overhead for whenever deleted_at is changed.

These DELETEs will delete the indicated rows without explicitly mentioning id.

If none need to be deleted, they will do nothing (other than taking some time).