QnNsrhKA

1tld: 738 rows
2============================
3| id (int) | tld (varchar) |
4============================
5
6sld: 116,000,000 rows
7============================
8| id (int) | sld (varchar) |
9============================
10
11dns: 3,000,000 rows
12============================
13| id (int) | dns (varchar) |
14============================
15
16cache: 300,000,000 rows  --temp data
17===============================================================
18| sld (varchar) | tld (varchar) | dns (varchar) | date (date) |
19===============================================================
20
21history: 128,000,000 rows
22===================================================
23| sld (int) | tld (int) | dns (int) | date (date) |
24===================================================
25	
26INSERT INTO `sld` (`sld`)
27SELECT DISTINCT `sld` FROM `cache`
28WHERE `sld` NOT IN (SELECT `sld` FROM `sld`);
29	
30INSERT INTO `history` (`sld`, `tld`, `dns`, `date`)
31SELECT DISTINCT * FROM (
32   SELECT `sld`.`id` AS `sld`, `tld`.`id` AS `tld`, `dns`.`id` AS `dns`, `cache`.`date` AS `date` FROM `cache`, `sld`, `tld`, `dns`
33   WHERE `cache`.`tld` = `tld`.`tld` AND `cache`.`sld` = `sld`.`sld` AND `cache`.`dns` = `dns`.`dns`
34) AS `t`;
35	
36REPEAT
37
38DROP TABLE IF EXISTS `tmp_cache`;
39CREATE TEMPORARY TABLE `tmp_cache`
40SELECT `sld`.`id` AS `sld`, `tld`.`id` AS `tld`, `dns`.`id` AS `dns`, `cache`.`date` AS `date` FROM `cache`, `sld`, `tld`, `dns`
41WHERE `cache`.`tld` = `tld`.`tld` AND `cache`.`sld` = `sld`.`sld` AND `cache`.`dns` = `dns`.`dns`
42LIMIT 1000000;
43
44INSERT INTO `history` (`sld`, `tld`, `dns`, `date`) SELECT DISTINCT * FROM `tmp_cache`
45ON DUPLICATE KEY UPDATE `history`.`date` = `history`.`date`;
46
47UNTIL (SELECT count(*) FROM `tmp_cache`) = 0
48END REPEAT;