0

I have a database of items, as each item has a set of key-value pairs such

place => london
field => engineering

since I frequently need to find items having the same two key-value sets. I created supporting tables by storing designated ids for each key and value.

CREATE TABLE ItemKeyValues
(
ItemID int(11) unsigned,
KeyID mediumint(7) unsigned,
ValueID mediumint(7) unsigned,
PRIMARY KEY (ItemID,KeyID,ValueID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci KEY_BLOCK_SIZE=1

CREATE TABLE Pairs
(
PairID int(11) unsigned NOT NULL AUTO_INCREMENT,
Key1 mediumint(7) unsigned,
Value1 mediumint(7) unsigned,
Key2 mediumint(7) unsigned,
Value2 mediumint(7) unsigned,
UNIQUE INDEX(Key1,Value1,Key2,Value2),
PRIMARY KEY (ItemID,KeyID,ValueID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci KEY_BLOCK_SIZE=1

CREATE TABLE ItemPairs
(
PairID int(11) unsigned NOT NULL,
ItemID int(11) unsigned,
PRIMARY KEY (PairID,ItemID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci KEY_BLOCK_SIZE=1

(I removed indices and FKs for simplicity)

For example, one row of the table Pairs is

PairID    Key1             Value1            Key2              Value2
1         3 (for place)    88 (for London)   10 (for field)    9 (for engineering)

I need to fill the last two tables.

  1. How can I INSERT all combinations of Key1,Value1,Key2,Value2 from the table ItemKeyValues (KeyID,ValueID)?
  2. How can I UPDATE table ItemPairs from the table ItemPairs?

The performance is of great importance since table ItemKeyValues is over 100M rows (though, this is one-time operation).

9
  • 1) Join 2 copies of ItemKeyValues. 2) Update self??
    – Akina
    May 21 '20 at 9:47
  • Specify precisely DBMS and its version. PS. ItemPairs table is excess - Pairs may refer to ItemKeyValues directly. PPS. table ItemKeyValues is over 100M rows How many distinct ItemID it stores?
    – Akina
    May 21 '20 at 9:54
  • @Akina I didn't get how Pairs can refer to ItemKeyValues. Pairs is independent of ItemID. Sorry, it's MariaDB 10.5. I will create the tag. There are about 5M ItemID.
    – Googlebot
    May 21 '20 at 11:53
  • I didn't get how Pairs can refer to ItemKeyValues. Add ItemID into Pairs, and you may remove ItemPairs at all. One-to-many does not need in intermediate table until it is an entity.
    – Akina
    May 21 '20 at 11:55
  • @Akina The purpose is to use PairID. For example, I want to retrieve all items with place=london and field=engineering. I quickly do it with SELECT ItemID FROM ItemPairs WHERE PairID=1.
    – Googlebot
    May 21 '20 at 11:58
1
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;
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;

fiddle

PS. I have simplified your structures by removing indices and options.

PPS. Your DDL scripts pack contains at least 1 error (copypaste misprint?).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.