Notes

BEGIN
START TRANSACTION;
CREATE TEMPORARY TABLE categorical_relations
SELECT * FROM cached_publisher_ads c
GROUP BY publisher_id, ad_id;

CREATE TEMPORARY TABLE `exclusion` (
`id` varbinary(11),
PRIMARY KEY (id)
) ENGINE=MyISAM;
INSERT INTO `exclusion`
SELECT CONCAT(publisher_id, '|', advertiser_ad_id)
FROM advertiser_ads_excluded_sites
WHERE is_deleted = 0 AND is_active = 1
;

CREATE TEMPORARY TABLE `banneds` (
`id` varbinary(11),
PRIMARY KEY (id)
) ENGINE=MyISAM;
INSERT INTO `banneds`
SELECT CONCAT(publisher_id, '|', advertiser_ad_id)
FROM publisher_banlist
WHERE is_deleted = 0 AND is_active = 1
UNION
SELECT CONCAT(publisher_id, '|', ad.id)
FROM publisher_banned_advertisers pba
LEFT JOIN advertiser_ads ad ON ad.advertiser_id = pba.advertiser_id
WHERE ad.id IS NOT NULL
;

TRUNCATE cached_table_publisher_ads;
INSERT INTO cached_table_publisher_ads
SELECT * FROM categorical_relations cr
WHERE CONCAT(cr.publisher_id, '|', cr.ad_id) NOT IN (
select * FROM exclusion)
AND CONCAT(cr.publisher_id, '|', cr.ad_id) NOT IN (
select * FROM banneds)
;
COMMIT;
END