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 id
s 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.
- How can I
INSERT
all combinations ofKey1,Value1,Key2,Value2
from the tableItemKeyValues (KeyID,ValueID)
? - How can I
UPDATE
tableItemPairs
from the tableItemPairs
?
The performance is of great importance since table ItemKeyValues
is over 100M rows (though, this is one-time operation).
Best Answer
fiddle
PS. I have simplified your structures by removing indices and options.
PPS. Your DDL scripts pack contains at least 1 error (copypaste misprint?).