How to INSERT all entries to a map table

Question

I have a map table as

CREATE TABLE keymap
(
col1 int(11) unsigned NOT NULL,
col2 int(11) unsigned NOT NULL,
col3 int(11) unsigned NOT NULL,
FOREIGN KEY(col1) REFERENCES table1(col1) ON DELETE CASCADE,
FOREIGN KEY(col2) REFERENCES table2(col2) ON DELETE CASCADE,
FOREIGN KEY(col3) REFERENCES table3(col3) ON DELETE CASCADE,
PRIMARY KEY (col1,col2,col3)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci

What is the most efficient way to INSERT all possible combination of col1, col2, and col3?

The reason I am asking this simple question is that the table is big (tens of millions of rows) and I need to do INSERT regularly to fill the missing combinations (table1, table2, and table3 are regularly being enlarged).

Answer 1

APPROACH #1

If you never delete from table1, table2, or table3

STEP #1

Using a Cartesian Join, generate a script that makes individual inserts

SQL="SELECT CONCAT('INSERT IGNORE INTO keymap VALUES"
SQL="${SQL} (',col1,',',col2,',',col3,');') FROM"
SQL="${SQL} (SELECT col1 FROM table1) A,"
SQL="${SQL} (SELECT col2 FROM table2) B,"
SQL="${SQL} (SELECT col3 FROM table3) C;"

read -s pw ; echo ${pw} | less
DB=mydb
echo "SET foreign_key_checks = 0;"         > All_Key_Combinations.sql
mysql -uroot -p${pw} -D${DB} -ANe"${SQL}" >> All_Key_Combinations.sql

STEP #2

Review the script and make sure it's OK

head All_Key_Combinations.sql
echo
tail All_Key_Combinations.sql
echo
sleep 15
less All_Key_Combinations.sql

STEP 3

Execute All_Key_Combinations.sql

DB=mydb
mysql -uroot -p${pw} -D${DB} < All_Key_Combinations.sql

APPROACH #2

If you ever delete from table1, table2, or table3

STEP #1

Using a Cartesian Join, generate a script that makes individual inserts

SQL="SELECT CONCAT('INSERT INTO keymap VALUES"
SQL="${SQL} (',col1,',',col2,',',col3,');') FROM"
SQL="${SQL} (SELECT col1 FROM table1) A,"
SQL="${SQL} (SELECT col2 FROM table2) B,"
SQL="${SQL} (SELECT col3 FROM table3) C;"

read -s pw ; echo ${pw} | less
DB=mydb
echo "SET foreign_key_checks = 0;"         > All_Key_Combinations.sql
echo "TRUNCATE TABLE keymap;"             >> All_Key_Combinations.sql
mysql -uroot -p${pw} -D${DB} -ANe"${SQL}" >> All_Key_Combinations.sql

STEP #2

Review the script and make sure it's OK

head All_Key_Combinations.sql
echo
tail All_Key_Combinations.sql
echo
sleep 15
less All_Key_Combinations.sql

STEP 3

Execute All_Key_Combinations.sql

DB=mydb
mysql -uroot -p${pw} -D${DB} < All_Key_Combinations.sql

GIVE IT A TRY !!!