[MySQL] Struktura bazy danych dla bazy utworów Opcje

[@rtur]

Chcę zrobić bazę utworów które są grane w radio internetowym, z której później będę mógł pobrać informację o tym co było grane z wyszczególnieniem data_godzina|wykonawca|utwór, np. z podziałem na dni, pobrać utwory według wykonawcy, pobrać informację o tym ile razy dany utwór był zagrany. Na obecnym etapie chcę skupić się nad optymalną strukturą bazy danych, na razie wymyśliłem coś takiego:

[SQL] pobierz, plaintext 
CREATE TABLE `Tracklista` (
  `TracklistaID` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `KiedyGrane` timestamp NULL DEFAULT NULL,
  `UtworID` int(11) NOT NULL,
  PRIMARY KEY (`TracklistaID`),
  UNIQUE KEY `KiedyGrane` (`KiedyGrane`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=1 ;
 
CREATE TABLE `Utwor` (
  `UtworID` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `WykonawcaID` int(11) NOT NULL,
  `UtworTytul` varchar(255) COLLATE utf8_polish_ci NOT NULL,
  PRIMARY KEY (`UtworID`),
  UNIQUE KEY `UtworTytul` (`UtworTytul`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=1 ;
 
CREATE TABLE `Wykonawca` (
  `WykonawcaID` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `WykonawcaNazwa` varchar(255) COLLATE utf8_polish_ci NOT NULL,
  PRIMARY KEY (`WykonawcaID`),
  UNIQUE KEY `WykonawcaNazwa` (`WykonawcaNazwa`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=1 ;
[SQL] pobierz, plaintext 

Czy tak wyglądająca struktura bazy jest według Was w porządku, czy można by jeszcze ją bardziej zoptymalizować, coś odjąć, dodać?

W jaki sposób mając dane w postaci zmiennych $utwor, $wykonawca, $data najbardziej optymalnie wykonać takie zadanie:

Dodać zawartość zmiennej $wykonawca do tabeli 'Wykonawca' (tylko jeśli jej nie ma w tabeli), następnie z tabeli 'wykonawca' pobrać pole WykonawcaID dla rekordu zgodnego ze zmienną $wykonawca, i dodać pobraną wartość WykonawcaID i zmiennej $wykonawca do tabeli 'utwory' (tylko jeśli tego zestawu nie ma jeszcze). Na końcu pobrać z tabeli 'utwory' UtworID dla zestawu "WykonawcaID i $utwor", i wrzucić go do tabeli 'Tracklista', razem ze zmienną $data

Do powyższego zadania widzę konieczność wykonania conajmniej 5 zapytań na jeden utwór:

[SQL] pobierz, plaintext 
$sqlQuery1 = INSERT IGNORE INTO 'Wykonawcy' (WykonawcaNazwa) VALUES ($wykonawca);
$sqlQuery2 = SELECT 'WykonawcaID' FROM 'Wykonawcy' WHERE 'WykonawcaNazwa' LIKE '$wykonawca'; // wynik tego zapytania przypisujemy do zmiennej $WykonawcaID 
$sqlQuery3 = INSERT IGNORE INTO 'Utwory' (WykonawcaID, UtworTytul) VALUES (WykonawcaID, $utwor); // no właśnie, jak tu dodać tylko unikalne zestawy WykonawcaID, UtworTytul?
$sqlQuery4 = SELECT 'utworID' FROM 'utwory' WHERE 'WykonawcaID'='$WykonawcaID' AND UtworTytul LIKE '$utwor' // wynik tego zapytania przypisujemy do zmiennej $UtworID
$sqlQuery5 = INSERT IGNORE INTO 'Tracklista' (KiedyGrane, UtworID) VALUES ($data, $UtworID);
[SQL] pobierz, plaintext 

Biorąc pod uwagę że każde wywołanie skryptu który będzie uruchamiany cronem, to próba dodania w pętli 20 rekordów, robi się już 100 zapytań na jedno wywołanie skryptu, więc wydaje mi się to trochę nieoptymalne, nie wiem czy dobrze myślę, ale wydaje mi się że mogę wyeliminować oddzielne zapytania select wrzucając je od razu do zapytań insert, ale nie wiem czy to już najbardziej optymalne rozwiązanie?

[Pyton_000]

100 zapytań nieoptymalne? Ja w podobny sposób musiałem zaktualizować 700000 rekordów codziennie co nam dawało 700000*8zapytań/rekord. Trwało to 45 min, więc twoje 100 to jest pierdnięcie

Ten post edytował Pyton_000 3.07.2014, 07:10:41

[mmmmmmm]

SELECT możesz połączyć z INSERT IGNORE w jedno zapytanie.
Unikalne pary zrobisz albo poprzez DISTINCT, albo poprzez GROUP BY. Dla ciebie to pierwsze.
W MySQL używa się ` zamiast ' - te drugioe służą do czegoś innego.

[@rtur]

Wydawało mi się to mało optymalne, ale faktycznie Pyton_000, przy Twoim moje 100 to pierdnięcie ,

Nie wiem czy dobrze kombinuję, ale może zapytanie $sqlQuery1 zmienić w pętli na

[SQL] pobierz, plaintext 
INSERT IGNORE INTO 'Wykonawcy' (WykonawcaNazwa) VALUES ($wykonawca[1]),($wykonawca[2]), .... , ($wykonawca[20]);
[SQL] pobierz, plaintext 

i 20 zapytań zmieni się w jedno?

Jak mogę dodać tylko unikalne zestawy z zapytania $sqlQuery3, czy tylko poprzez dodanie wcześniej zapytania

[SQL] pobierz, plaintext 
$sqlQuery4 = SELECT 'UtworID' FROM 'utwory' WHERE 'WykonawcaID'='$WykonawcaID' AND UtworTytul LIKE '$utwor'
[SQL] pobierz, plaintext 

potem jeśli 0 rekordów to wykonać zapytanie $sqlQuery3 ale już niekoniecznie z ignore?

Unikalne pary zrobisz albo poprzez DISTINCT, albo poprzez GROUP BY. Dla ciebie to pierwsze.

Z tym sobie już chyba poradziłem dodając

[SQL] pobierz, plaintext 
UNIQUE KEY `WykonawcaID_UtworTytul` (`WykonawcaID`,`UtworTytul`)
[SQL] pobierz, plaintext 

do tabeli Utwory

Ten post edytował @rtur 3.07.2014, 08:39:14

[mmmmmmm]

$sqlQuery1 = "insert ignore into `Wykonawcy` (WykonawcaNazwa) values ($wykonawca)";
Robisz query. Pobierasz mysql_insert_id() albo coś podobnego - zależy z czego korzystasz. Do zmiennej $wykonawca
Wtedy:
$sqlQuery2 = select 'WykonawcaID' from 'Wykonawcy' where 'WykonawcaNazwa' like '$wykonawca'; // wynik tego zapytania przypisujemy do zmiennej $WykonawcaID
już ci nie jest potrzebne

$sqlQuery3 = "insert ignore into `Utwory` (WykonawcaID, UtworTytul) values (WykonawcaID, '$utwor')"; // no właśnie, jak tu dodać tylko unikalne zestawy WykonawcaID, UtworTytul?
Już sobie prawie poradziłeś z unikalnością. Ale to jest bez sensu. Bo nie patrzysz, co coś już jest, tylko na chama usiłujesz dodać... Tak się nie robi.
I podobnie jak wyżej: query, insert_id. DO zmiennej $utworID
I to poniżej juz niepotrzebne.
$sqlQuery4 = select 'utworID' from 'utwory' where 'WykonawcaID'='$WykonawcaID' and UtworTytul like '$utwor' // wynik tego zapytania przypisujemy do zmiennej $UtworID

$sqlQuery5 = "insert ignore into `Tracklista` (KiedyGrane, UtworID) values ('$data', $UtworID)";

koniec

[@rtur]

$sqlQuery1 = "insert ignore into `Wykonawcy` (WykonawcaNazwa) values ($wykonawca)";
Robisz query. Pobierasz mysql_insert_id() albo coś podobnego - zależy z czego korzystasz. Do zmiennej $wykonawca

dodałem 1 rekord przeszło ok przypisało do zmiennej $UtworID prawidłowe ID, dodałem ten sam rekord raz jeszcze, (nie dodał się bo już jest) i zmienna $UtworID dostała wartość 0 więc chyba coś nie tak robię, tylko co?

Ten post edytował @rtur 3.07.2014, 11:52:38

[Pyton_000]

Dobrze robisz, po prostu rekord nie został dodany (INSERT IGNORE) dlatego zwróciło 0.

Zapisuj sobie w tablicy ID już dodanych rekordów i dane tak aby kolejne zapytania mogły być sprawdzane w tablicy. Jak nie ma to Insert, a jak jest to weź ID z tablicy. To zredukuje ilość zapytań o ten sam rekord

[@rtur]

Musiałem temat porzucić na chwile, ale już do niego wracam, @Pyton_000 mówisz o pobieraniu do tablicy id i danych już dodanych do tablicy podczas aktualnego odpalenia skryptu, czy ogólnie id i dane wszystkich które już są w bazie? W tej chwili w tabelach mam średnio 10 000 rekordów, a może pobierać dane poprzez where utwor like tytul1 or utwor like tytul2 itd... wynik wrzucić w tablice porównać obie tablice wrzucić w tabele to czego nie ma w pierwszej....

Ten post edytował @rtur 1.08.2014, 21:41:01