Eu tenho um banco de dados de itens, pois cada item tem um conjunto de pares chave-valor como
place => london
field => engineering
já que frequentemente preciso encontrar itens com os mesmos dois conjuntos de valores-chave. Criei tabelas de suporte armazenando id
s designados para cada key
e 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
(Eu removi índices e FKs por simplicidade)
Por exemplo, uma linha da tabela Pairs
é
PairID Key1 Value1 Key2 Value2
1 3 (for place) 88 (for London) 10 (for field) 9 (for engineering)
Eu preciso preencher as duas últimas tabelas.
- Como posso
INSERT
todas as combinações daKey1,Value1,Key2,Value2
tabelaItemKeyValues (KeyID,ValueID)
? - Como posso
UPDATE
mesaItemPairs
da mesaItemPairs
?
O desempenho é de grande importância, pois a tabela ItemKeyValues
tem mais de 100 milhões de linhas (embora esta seja uma operação única).
violino
PS. Simplifiquei suas estruturas removendo índices e opções.
PPS. Seu pacote de scripts DDL contém pelo menos 1 erro (erro de cópia e colagem?).