[mySQL] Masowa edycja/wstawianie wartości - optymalizacja

Mam bazę produktową. Dany produkt opisywany jest przez bardzo dużą ilość cech (właściwości).
Zastanawiam się jak najbardziej optymalnie można zrealizować sposób edycji tych cech. Chodzi mi tutaj przede wszystkim o optymalny stosunek ilości zapytań do czasu ich wykonania.

Doszedłem do 2 rozwiązań. W moim odczuciu (i bazując na posiadanej wiedzy) opcja a) wydaje się być lepsza.
Być może jest inne rozwiązanie, które jest powszechnie używane w przypadku takich baz produktowych, a o istnieniu którego ja nie mam pojęcia - stąd też post tutaj.

Wszelkie sugestie/pomysły będą wartościowe ;)

a) Usunięcie wszystkich cech opisujących dany produkt i zrobienie INSERT'ów od początku

• samo usuwanie zrobię 1 zapytaniem, później wstawię te cechy od nowa
• niestety indeksy będą "skakały" - w konsekwencji nawet przy niewielu produktach ale licznej edycji będą bardzo wysokie

b) sprawdzanie czy cecha już istnej - jeśli tak zrób update, jeśli nie to wstaw

• tutaj na każdą cechę zawsze 2 zapytania (SELECT i INSERT lub SELECT i UPDATE)

c) inne rozwiązanie - jakie?

A dlaczego nie zwykłe

Update ... where

skoro możesz do deleta znaleźć wszystko jednym zapytaniem

Już spieszę z wyjaśnieniem. Delete zadziała po id_produkt. DELETE FROM..WHERE id_produkt=N. Natomiast jeśli mam 20 atrybutów przy czym podczas dodawania produktu uzupełnione zostało tylko 5, to tak na prawdę w tabeli mam 5 rekordów opisujących ten produkt. Podczas edycji moge zmienić 2 z tych 5 uzupelnionych podczas dodawania oraz ponadto wypełnić kilka innych cech. Jakoś muszę sprawdzać które już są, a które trzeba dodać. Dodanie nowej cechy też komplikuje sprawę, bo ona musi być widoczna dla kazdego produktu. Dlatego też UPDATE WHERE nie zadziała na wartości, które wcześniej nie były uzupełnione, albo Pola które zostały dodane po wprowadzeniu produktu.

Mam nadzieję, że jasno to wszystko opisałem.

W MySQL jest klauzula REPLACE. Wtedy baza sama robi delete i insert. Oprócz tego możesz spróbować insert z on conflict.

Masz też coś takiego jak operacje batchowe i zamiast robić N insert/update, to można jednym strzałem korzystając z tego o czym pisał @Marcin.Miga, tj. ON CONFLICT:

CREATE TABLE PRODUKT (
  id_produktu int,
  cecha varchar(50),
  wartosc varchar(30),
  CONSTRAINT PK_Produkt PRIMARY KEY (id_produktu,cecha)
);
 
INSERT into `PRODUKT` (id_produktu, cecha, wartosc)
  VALUES (1, 'MARKA', 'Samsung'), (1, 'MODEL','S4'), (1, 'Kolor','biały')
  ON DUPLICATE KEY UPDATE cecha = VALUES(wartosc);

INSERT into `PRODUKT` (id_produktu, cecha, wartosc)
  VALUES (1, 'MARKA', 'HUAWEI'), (1, 'MODEL','P20'), (1, 'Kolor','żółty'),(1,'Waga','300'),(1,'FOO','BAR')
  ON DUPLICATE KEY UPDATE wartosc = VALUES(wartosc);

@yarel:
@Marcin.Miga

U mnie tabele wygląda tak:

Tak więc bazując na powyższym przykładzie muszę dodać
a) CONSTRAINT PK_Produkt PRIMARY KEY (id_product, id_property, id_lang)

I teraz może dość głupie pytanie.
Zapytanie, które robi inserta, a w razie wykrycia duplikatów update wyglądało by tak:

INSERT INTO product SET id_product=$id_product, id_property=$id_property, description="$description", id_lang = $id_lang 
ON DUPLICATE KEY UPDATE description= VALUES("$description");  
// nie musze czasem dodać warunku w stylu:
// WHERE id_product=$id_product AND id_property=$id_property AND id_lang = $id_lang    ??

?

https://www.db-fiddle.com/f/iKbbW5tnLLu9FkZjvfxJBh/0