CODEWITHSUNDEEP
mysqlindexing
Arth
Arth

Reputation: 13110

MySQL compound index with a unique subset

I have the following one to one linking table

CREATE TABLE `foo_bar` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `foo_id` int(10) unsigned NOT NULL,
  `bar_id` int(10) unsigned NOT NULL,
  …
  PRIMARY KEY (`id`),
  …
) ENGINE=InnoDB

Where foo_id and bar_id are foreign keys to tables foo and bar respectively

There is also

Note that this setup does allow non-unique foo_ids, as long as they are accompanied by a unique bar_id

I would now like to enforce uniqueness on foo_id alone while retaining the performance benefits of the existing compound index on (foo_id, bar_id)

Is there any way of creating a unique compound index where uniqueness is enforced on a subset of the columns referenced, but allows further columns to be added to the index?

In this example, this would look like ([foo_id], bar_id) where the square brackets indicate the unique part of the compound index

I would like to avoid the overhead of a separate index on (foo_id, bar_id)

Off the top of my head, I can't think of any algorithmic reason why this wouldn't be possible - as long as the unique subset of columns is at the start of the compound index

Note that I'm aware that I could use either foo_id or bar_id as the primary key for the table, but the application requires a separate ID by which the linking record can be deleted separately without revealing the links

Basically foo_bar.id is stored in a client cookie (after being reversibly encrypted), and any personal data is accessible from this ID. The idea is that if a client requests deletion, we delete the foo_bar record and remove their data without destroying the anonymised or functional data in foo and bar

This has the added bonus that, even if the cookie persists anywhere, it will no longer be resolvable to any data

Upvotes: 3

Views: 130

Answers (1)

Rick James
Rick James

Reputation: 142453

This is a many:many mapping table?

Then get rid if id; it is clutter and slows things down.

PRIMARY KEY(foo_id, bar_id),
INDEX(bar_id, foo_id)

Those and more tips: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

This has the added bonus that, even if the cookie persists anywhere, it will no longer be resolvable to any data

This implies doing one of these:

  • Hang onto all hashes created, but mark some as 'deleted'.
  • Otherwise make sure that you never generate the same hash twice.

In either case, you can avoid "resolving" an old cookie to data that is dead or gone.

That somewhat implies the existence of a UNIQUE (or PRIMARY) key somewhere for foo_id and, separately, bar_id.

If those two uniqueness constraints are in the same table and you need a separate id for other reasons, then the minimum indexing is:

PRIMARY KEY(id),
UNIQUE(foo_id),
UNIQUE(bar_id)

or

PRIMARY KEY(foo_id),
UNIQUE(bar_id),
INDEX(id)   -- This is sufficient to keep `AUTO_INCREMENT` happy.

There is no need to have any index (plain or UNIQUE) on (foo_id, bar_id) since the uniqueness constraint on the first is sufficient for both uniqueness of the pair and for efficient lookups.

foo_bar.id is encrypted and stored in a client cookie ... client requests deletion, we delete the foo_bar record

What kind of encryption? One-way (md5, shar256, etc)? Or reversable (aes...)? If one-way, then you need an index somewhere that maps CONCAT(foo_id, bar_id) (or whatever you are doing) to foobar_id.

Upvotes: 1

Related Questions