Poprawna konstrukcja zapytania, pobierającego z relacją wiele do wielu Opcje

[Adi32]

Witajcie.

Jestem w trakcie pewnego projektu, którego kod będzie oglądał klient, dlatego chce aby wszystko było wysokiej jakości.
Dodatkowo projekt ma się rozwinąć więc jest narzut na optymalizację.

Także do rzeczy - podam przykładową strukturę:

produkt (
id_produkt INT PK
wartosc FLOAT
typ (produkt | usluga)
....
)

opiekun (
id_opiekun INT PK
imie VARCHAR
....
)

produkt_opiekun (
id_produkt INT FK
id_produkt INT FK
)

Chyba najprostsza możliwa struktura tabel w relacji wiele do wielu.

Przywykłem do tego, że gdy muszę pobierać coś z takich tabel rozkładam to na mnogą ilość zapytań i coś mi się zdaje, że nie jest to najoptymalniejsze rozwiązanie.

Dajmy przykładowe zadanie:

Pobierz wszystkie produkty powyżej wartości 1000 które nie mają opiekuna a ich typ to usługa.

Jak zbudować takie zapytanie aby było 'poprawne' z każdej strony? Gdzie dać indeksy? Czy rozbicie tego na kilka zapytań to jest błąd? W jednym zapytaniu i tak musielibyśmy robić podzapytania także czy jest to taka duża różnica?

Chciałbym to raz a dobrze zrozumieć. Jeżeli znacie jakieś miejsce które wyjaśnia lepiej prace z relacjami to proszę o podanie.

Pozdrawiam.

Ten post edytował Adi32 20.07.2012, 11:24:55

[nospor]

W jednym zapytaniu i tak musielibyśmy robić podzapytania

Bzdura wierutna

[SQL] pobierz, plaintext 
SELECT * FROM produkt p
LEFT JOIN produkt_opiekun po ON po.id_produkt=p.id_produkt
WHERE wartosc > 1000 AND typ='usluga' AND po.id_produkt IS NULL
[SQL] pobierz, plaintext 

I już.

[Adi32]

Bzdura wierutna

Miałem na myśli JOINy i podzapytania, czy JOINy są szybsze?

[SQL] pobierz, plaintext 
SELECT * FROM produkt p
LEFT JOIN produkt_opiekun po ON po.id_produkt=p.id_produkt
WHERE wartosc > 1000 AND typ='usluga' AND po.id_produkt IS NULL
[SQL] pobierz, plaintext 

I już.

No i tak bym to zrobił

a zapytanie gdzie dodatkowo opiekunów jest więcej niż dwóch i żaden nie ma na imię Andrzej?

Ten post edytował Adi32 20.07.2012, 11:39:34

[alegorn]

[SQL] pobierz, plaintext 
    SELECT p.*, count(0) AS ile  FROM produkt p
    LEFT JOIN produkt_opiekun po ON po.id_produkt=p.id_produkt
    LEFT JOIN opiekun o ON o.id_opiekun = po.id_opiekun 
    WHERE wartosc > 1000 AND typ='usluga' AND po.id_produkt IS NULL
    AND o.imie <> 'Andrzej' 
   GROUP BY  po.id_produkt HAVING ile>2
 
[SQL] pobierz, plaintext 

z pamieci, bez sprawdzania, powinno dzialac.

jesli chcesz prawidlowo zaprojektowac baze danych to twoim idealem bedzie 3nf (da sie i wyzej, ale to juz niekoniecznie dla prostego projektu)
baza danych bedzie sobie dobrze radzic z relacjami. nie ma sie co bac kolejnych joinow w zapytaniu.

prawidlowo zakladane indexy - to juz osobna bajka.
sa ogolne reguly, np. tutaj z marszu ci bym powiedzial ze w tabeli produkt_opiekun musisz zalozyc klucz (przynajmniej unika) na obie kolumny jednoczesnie.
klucze trzeba dostosowywac do zapytan jakie sa wykonywane na bazie danych, lub tez, wynikajace z koniecznosci (klucz glowny, zachowanie spójności)
to naprawde dosc szeroki temat...

[Adi32]

[SQL] pobierz, plaintext 
    SELECT p.*, count(0) AS ile  FROM produkt p
    LEFT JOIN produkt_opiekun po ON po.id_produkt=p.id_produkt
    LEFT JOIN opiekun o ON o.id_opiekun = po.id_opiekun 
    WHERE wartosc > 1000 AND typ='usluga' AND po.id_produkt IS NULL
    AND o.imie <> 'Andrzej' 
   GROUP BY  po.id_produkt HAVING ile>2
 
[SQL] pobierz, plaintext 

z pamieci, bez sprawdzania, powinno dzialac.

jesli chcesz prawidlowo zaprojektowac baze danych to twoim idealem bedzie 3nf (da sie i wyzej, ale to juz niekoniecznie dla prostego projektu)
baza danych bedzie sobie dobrze radzic z relacjami. nie ma sie co bac kolejnych joinow w zapytaniu.

prawidlowo zakladane indexy - to juz osobna bajka.
sa ogolne reguly, np. tutaj z marszu ci bym powiedzial ze w tabeli produkt_opiekun musisz zalozyc klucz (przynajmniej unika) na obie kolumny jednoczesnie.
klucze trzeba dostosowywac do zapytan jakie sa wykonywane na bazie danych, lub tez, wynikajace z koniecznosci (klucz glowny, zachowanie spójności)
to naprawde dosc szeroki temat...

Dzięki.
Byłbyś w stanie skazać miejsce gdzie lepiej mógłbym poznać ten szeroki temat? Ewentualnie może mógłbyś sam rozwinąć trochę temat?

Dlaczego dałeś tam 'count(0)'? Co on ma liczyć?
Pierwszy raz spotykam się z HAVING po GROUP BY...
Nie rozumiem tego...

[alegorn]

hmmm...wszystkie te polecenia o ktore pytasz znajdziesz z dokladnym opisem w manualu

na szybko - count zliczanie wierszy (tutaj - wierszy ktore zostana zgrupowane)
group by - grupuje wynik wg wybranej kolumny (tutaj jest to imie opiekuna)
having - wyswietla tylko te wiersze ktore spelniaja warunek - tutaj ile >2

indexy

uuf, to juz raczej bardziej o teorii sql'a i relacyjnych bazach danych, niz sama ksiazka do nauki sql'a


dosc duzo ciekawostek i swietnych rad znalazlem na roznych blogach np. http://blog.ksiazek.info << akurat tego moge z czystym sumieniem polecic, tam takze znajdziesz o indexach..

j.