MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE ItemKeyValues
(
ItemID int(11) unsigned NOT NULL,
KeyID mediumint(7) unsigned,
ValueID mediumint(7) unsigned
);
INSERT INTO ItemKeyValues (ItemID, KeyID, ValueID) VALUES
(1,1,1),
(1,2,2),
(1,3,3),
(1,4,4),
(2,1,1),
(2,2,2),
(2,3,4),
(2,5,5);
SELECT * FROM ItemKeyValues;
CREATE TABLE Pairs
(
PairID int(11) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
Key1 mediumint(7) unsigned,
Value1 mediumint(7) unsigned,
Key2 mediumint(7) unsigned,
Value2 mediumint(7) unsigned
);

CREATE TABLE ItemPairs
(
PairID int(11) unsigned NOT NULL,
ItemID int(11) unsigned NOT NULL
);


ItemID
KeyID
ValueID


1
1
1

1
2
2

1
3
3

1
4
4

2
1
1

2
2
2

2
3
4

2
5
5


INSERT INTO Pairs
SELECT DISTINCT NULL, t1.KeyID, t1.ValueID, t2.KeyID, t2.ValueID
FROM ItemKeyValues t1
JOIN ItemKeyValues t2 USING (ItemID)
WHERE t1.KeyID < t2.KeyID;
SELECT * FROM Pairs;


PairID
Key1
Value1
Key2
Value2


1
1
1
2
2

2
2
2
3
3

3
1
1
3
3

4
3
3
4
4

5
2
2
4
4

6
1
1
4
4

7
2
2
3
4

8
1
1
3
4

9
3
4
5
5

10
2
2
5
5

11
1
1
5
5


INSERT INTO ItemPairs
SELECT t1.PairID, t2.ItemID
FROM Pairs t1
JOIN ItemKeyValues t2 ON t1.Key1 = t2.KeyID 
                     AND t1.Value1 = t2.ValueID
JOIN ItemKeyValues t3 ON t1.Key2 = t3.KeyID 
                     AND t1.Value2 = t3.ValueID 
                     AND t2.ItemID = t3.ItemID;
SELECT * FROM ItemPairs;


PairID
ItemID


1
2

1
1

2
1

3
1

4
1

5
1

6
1

7
2

8
2

9
2

10
2

11
2