Get inserted values / UNIQUE key over multiple tables / MySQL

Question

Caused by some bad design decisions, I have to make sure that a random-character string (no sequence!) must not appear twice in three different MySQL tables (TableA, TableB, TableC). So if the value "ABCD1234" is already present in TableB oder TableC, it must not be inserted into TableC. The task now is to insert a few (1-100.000) new keys in one of these tables.

Side note: The tables habe a project field that separates different logical partitions. The key must "only" be unique within each partition/project. If I write about record numbers below, that is always within a single partition/project.

First trial with delayed error

I used to retrieve a union-list of keys on the client (PHP) to check for duplicates before inserting. This works quite performant, using a hash table, but as we reached >2 mio. records, we ran into memory issues. As we have several thread running at the same time, the PHP process is much more limited in its RAM usage (256 MB) than the MySQL database (4+ GB).

Second trial and error

I did a lot of trial an error with subqueries (completely off performance) and JOINs.

INSERT INTO TableA (`code`, `project`)
SELECT TemporaryTable t
LEFT JOIN TableA r1 ON ((t.`code` = r1.`code`) AND (r1.project = ...))
LEFT JOIN TableB r2 ON ((t.`code` = r2.`code`) AND (r2.project = ...))
LEFT JOIN TableB r3 ON ((t.`code` = r3.`code`) AND (r2.project = ...))
WHERE (r1.`code` IS NULL) AND (r2.`code` IS NULL) AND (r3.`code` IS NULL)

All that trials ended in a setting where 100k new keys must be compared to 2m existing keys ... and that is very, very slow. We're talking about several minutes for such a query when using a LEFT JOIN to filter existing values.

Third trial and error

So the next trial was to work with the temporary table that I needed anyway, because I need to support MySQL 5.7 and this does not support the VALUES ROW() syntax.

So I created the temporary table (with an index on code), inserted the news values (candidates to be inserted), then performed three DELETE FROM TABLE queries to remove duplicates:

DELETE FROM TemporaryTable 
WHERE `code` IN (
    SELECT r.`code` FROM TableA r WHERE (r.`project` = ...)
)

What shall I say ... it's still painfully slow. The DELETE command with a subquery seems inefficient.

Next trial

The only thing that works really fast is a UNIQUE index (code, project) together with INSERT IGNORE. MySQL seems to have some magic optimization there that is al least 10k times faster than any explicit JOIN.

The point, of course, is thay you can create such a UNIQUE index within a single table, only. So the locial consequence is to create a fourth table TableX that takes care of uniqueness.

So, new values would be inserted (INSERT IGNORE) into TableX, and only those that make it, are then inserted in either TableA, TableB oder TableC.

This, as far as I though through, is also the only solution that won't fail if there are multiple such inserts running at the same time (thread secure).

This solution still gives me some headache:

1. How do I know which values were accepted (inserted) and can now be used for TableA? A sequence number for the insert query could be added, but that would add a lot of otherwise useless data to TableX.
2. Is there any elegant solution, using MySQL foreigns keys or such to avoid artifacts in TableX when data is deleted from TableA, TableB, TableC?

And, of course, I welcome any alternative suggestion on how to solve the unique-over-multiple-tables issues might be solved. We also consider changing the bad design decisions that originally caused the issue ... but that would be related to lots of other changes, of course. And I just cannot beliefe that you cannot use the super-performant UNIQUE + INSERT IGNORE mechanism of MySQL otherwise. It's the same task in the end.

Answer 1

Uniqueness over a lot of tables.

This can be solved by additional table + triggers. Like

CREATE TABLE tableABC (id VARCHAR(255) UNIQUE);
CREATE TRIGGER tr_bi_a BEFORE INSERT ON tableA 
FOR EACH ROW INSERT INTO tableABC VALUES (NEW.id);

DEMO

If this column value can be altered then you must create according BEFORE UPDATE triggers (which removes OLD.id and inserts NEW.id). If the rows can be removed from the tables then you must create according BEFORE DELETE triggers (which removes OLD.id).

---

Alternatively you may create BEFORE INSERT trigger which will check that NEW.id is not present in the tables - but this can be not concurrent-safe.