mysql: Compare large tables by hash

compare_large_table_by_hash.sql

/* create tmp table with entries that exist */
DROP TEMPORARY TABLE IF EXISTS source;
CREATE TEMPORARY TABLE source (compare_hash char(32) PRIMARY KEY) ENGINE = MEMORY
SELECT
        compare_hash
FROM table_source AS s;

/* create tmp table with entries that SHOULD exist */
DROP TEMPORARY TABLE IF EXISTS target;
CREATE TEMPORARY TABLE target   (compare_hash char(32) PRIMARY KEY) ENGINE=MEMORY
SELECT
        compare_hash
FROM table_target AS maz;

/* find all entries we have to add */
DROP TEMPORARY TABLE IF EXISTS to_add;
CREATE TEMPORARY TABLE to_add (
        compare_hash char(32) PRIMARY KEY
) ENGINE = MEMORY
SELECT
        s.compare_hash
FROM source AS s
        LEFT OUTER JOIN target AS t
        USING (compare_hash)
WHERE t.compare_hash IS NULL;

/* find all entries we have to delete */
DROP TEMPORARY TABLE IF EXISTS to_delete;
CREATE TEMPORARY TABLE to_delete (
        compare_hash char(32) PRIMARY KEY
) ENGINE = MEMORY
SELECT
        t.compare_hash
FROM target AS t
        LEFT OUTER JOIN source AS s
        USING (compare_hash)
WHERE s.compare_hash IS NULL;

/* add missing entries */
INSERT INTO table_target
        SELECT
  s.*
FROM table_source AS s
        INNER JOIN to_add AS ta
        USING (compare_hash);

/* delete old entries */
DELETE a.*
        FROM table_target a
                INNER JOIN to_delete AS td USING (compare_hash);