[MySQL] Zapytanie na bazie 3 tabel z dwukrotnym COUNT Opcje

[qrzysztof]

Mam następujące tabele:

1. t_obiekty (id, ..., ...)
2. t_oceny (id, id_obiektu, ocena, ...)
3. t_komentarze (id, id_obiektu, ....)

Pola t_oceny.id_obiektu i t_komentarze.id_obiektu odnoszą się bezpośrednio do pola t_obiekty.id.

Chcę w jednym zapytaniu, nie zmieniając przy tym struktury bazy wyciągnąć następujące dane:

t_obiekty.id
COUNT(t_oceny.*) AS liczba_ocen
AVG(t_oceny.ocena) AS srednia_ocena
COUNT(t_komentarze.*) AS liczba_komentarzy

Oczywiście COUNT i AVG nie dla całej tabeli tylko dla poszczególnych obiektów. Czyli table trzeba złączyć.

Innymi słowy chcę wyciągnąć liczbę ocen, średnią ocen i liczbę komentarzy dla poszczególnych obiektów.

Ten post edytował qrzysztof 1.04.2012, 20:21:43

[thek]

Da się, ale nie będzie to ekstra super wydajne. Mianowicie łączysz tabelę obiektów i ocen po id_obiektu i grupujesz też po tym. W select dajesz zarówno count dla liczby ocen jak i ich średnią. Liczba komentarzy to podzapytanie jako jedno z pól wynikowych w select. Ma to jednak jeden minus jeśli obiekt nie ma choć jednej oceny, to nie pojawi się w wynikach. Jeśli mają wszystkie obiekty być ujęte, to niestety jedyne pewne rozwiązanie to walnięcie wszystkich count czy avg jako podzapytania w select, a to już raczej mało wydajne.

[singollo]

[SQL] pobierz, plaintext 
SELECT
t_obiekty.id,
COUNT(t_oceny.*) AS liczba_ocen,
AVG(t_oceny.ocena) AS srednia_ocena.
COUNT(t_komentarze.*) AS liczba_komentarzy
FROM t_obiekty
LEFT JOIN t_oceny ON t_oceny.obiekt_id = t_obiekty.id
LEFT JOIN t_komentarze ON t_komentarze.obiekt_id = t_obiekty.id
GROUP BY t_obiekty.id
[SQL] pobierz, plaintext 

Powinno zadziałać i dać oczekiwane efekty. I powinno być też całkiem wydajne

[qrzysztof]

@singollo - Twoje rozwiązanie nie daje spodziewanych rezultatów.

Średnią wylicza prawidłowo, ale liczbę ocen i komentarzy już nie. Jeżeli, dajmy na to, oczekiwany wynik to 3 komentarze i 5 ocen to obie kolumny (liczba_komentarzy i liczba_ocen) zawierają iloczyn czyli 15. Tylko jeśli któryś obiekt nie ma komentarzy lub nie ma ocen (liczba_ocen = 0 lub liczba_komentarzy = 0) to wiersz jest zwracany prawidłowo.

edit: zmodyfikowałem w następujący sposób i działa:

[SQL] pobierz, plaintext 
SELECT
t_obiekty.id,
COUNT(DISTINCT t_oceny.id) AS liczba_ocen,
AVG(t_oceny.ocena) AS srednia_ocena.
COUNT(DISTINCT t_komentarze.id) AS liczba_komentarzy
FROM t_obiekty
LEFT JOIN t_oceny ON t_oceny.obiekt_id = t_obiekty.id
LEFT JOIN t_komentarze ON t_komentarze.obiekt_id = t_obiekty.id
GROUP BY t_obiekty.id
[SQL] pobierz, plaintext 

Efektywność raczej średnia. Zapytanie wykonuje się w około 3 sekundy. Co można zrobić żeby ją poprawić? Tabele mają następującą liczbę rekordów:

t_obiekty: 261
t_oceny: 98
t_komentarze: 12 523

Domyślam się, że jeśli liczba rekordów w pierwszych dwóch tabelach wzrośnie nawet tylko kilkukrotnie to może już zabić zapytanie i sprawić, że czas wykonania będzie liczony już w minutach.

Ten post edytował qrzysztof 2.04.2012, 09:35:03

[thek]

@singollo: sprawdziłeś ile wierszy i co zawierających dostaniesz po samym JOINowaniu? Zapewniam Cię, że możesz się zdziwić ich ilością i zawartością

[qrzysztof]

Chcę wyświetlać od 20 do maksymalnie 100 obiektów na stronie. Sprawdzę jeszcze wydajność rozwiązania zaproponowanego przez @theka, bo widzę, że to rzeczywiście głównie rodzaj złączenia decyduje o drastycznym spadku wydajności. A jeśli to mnie nie usatysfakcjonuje to chyba podzielę sobie całość na 2 lub nawet 3 zapytania. Gdzieś po prostu wyczytałem jakąś mądrość, że "jeśli da się coś zrobić jednym zapytaniem to tak jest najlepiej" i stąd moje pierwotne pytanie.

[SQL] pobierz, plaintext 
SELECT
t_obiekty.id,
COUNT(t_komentarze.id) AS liczba_komentarzy,
(SELECT COUNT(t_oceny.id) FROM t_oceny WHERE t_oceny.id_obiektu=t_obiekty.id) AS liczba_ocen,
(SELECT AVG(t_oceny.ocena) FROM t_oceny WHERE t_oceny.id_obiektu=t_obiekty.id) AS srednia_ocena
FROM t_komentarze
INNER JOIN t_obiekty ON t_komentarze.id_obiektu = t_obiekty.id
GROUP BY t_obiekty.id
[SQL] pobierz, plaintext 

wykonuje się w 0,05 sekundy (60-krotny wzrost wydajności w porównaniu ze zmodyfikowanym rozwiązaniem @singollo). Muszę się tylko zastanowić co zrobić z faktem, że obiekty bez ani jednego komentarza nie są wyświetlane. Jest ich, co prawda mało (około 1%) ale chciałbym je też wyświetlać.


Kurcze, tylko jak wybrać obiekty bez komentarzy?

Nic lepszego niż

[SQL] pobierz, plaintext 
SELECT t_obiekty.id FROM t_obiekty WHERE t_obiekty.id NOT IN (SELECT t_komentarze.id_obiektu FROM t_komentarze)
[SQL] pobierz, plaintext 

nie udało mi się wymyślić. A wykonuje się to przez 1,75 sekundy. To zdecydowanie za długo!

Da się jakoś inaczej? Może jakiś sprytny UNION albo coś? Właściwie potrzebne mi są tylko id obiektów bez komentarzy. W ich przypadku nie muszę mieć żadnych innych danych.

[thek]

Jeśli są Ci potrzebne TYLKO id obiektów liczba ocen i ewentualnie średnia, a liczba komentarzy nie jest potrzebna, to zrób obiekty LEFT JOIN oceny i GROUP BY id_obiektu. To co idzie wtedy z lewej jest bazą dla zapytania i nie "znika", a więc będziesz miał wszystkie obiekty. Zrobiłem dla Ciebie mały przykład, przyjmując Twoją strukturę:

[SQL] pobierz, plaintext 
SELECT ob.id, IF(o.ocena IS NULL , 0, count(ob.id)) AS ilosc_ocen, IF(o.ocena IS NULL, 0, avg(o.ocena)) AS srednia_ocen FROM t_obiekty AS ob LEFT JOIN t_oceny AS o ON ob.id = o.id_obiektu GROUP BY ob.id
[SQL] pobierz, plaintext 

Zwróć uwagę na IF w select... Gdy zrobię JOINa i pogrupuję, te obiekty, które nie mają ocen będa miały w polach teoretycznie z tabeli t_oceny NULLe Wykorzystując tę właściwość, wykrywamy, które nie mają ocen i tam na pałę wstawiamy 0 W ten sposób też wykryjesz po stronie php, które nie mają ocen bo bedą tam 0 zawsze.

Powód edycji: [thek]: Wyjaśnienie

[qrzysztof]

Trochę nieprecyzjnie się wyraziłem:

-przy obiektach bez komentarzy wystarczy mi sam id obiektu (w pozostałych kolumnach mogą być zera, cokolwiek)
-przy obiektach z komentarzami potrzebuję pełnego kompletu (srednia_ocena, liczba_ocen, liczba_komentarzy).

Twóje ostanie zapytanie @thek nie posuwa nas zbytnio do przodu, bo wiadomo, że najbardziej newralgiczna tabela to t_komentarze. Jesliby tę samą metodę (opartą o lewostronne złączenie) zastosować zamiast do tabeli t_oceny do tabeli t_komentarze to czas wykonania rzędu 2,7 sekundy raczej nie powala.

W tej chwili skłaniam się ku dodaniu w tabeli t_komentarze fałszywego komentarza dla każdego obiektu. Baza stanie się "brudna" ale wydajność zostanie zachowana. No chyba, że jednak się da...

Ten post edytował qrzysztof 2.04.2012, 16:23:50

[thek]

Ja bym się zastanowił nad jednym... Skoro masz takie kiepskie czasy przy teoretycznie tak niskich wartościach (bo co to jest 13k rekordów dla bazy?) to może coś skopałeś z indeksami? Jakie masz założone indeksy w tabelach, bo coś dziwne są dla mnie tak wysokie czasy...

Powód edycji: [thek]: usuwam sobie nadmierne "Pomógł". Jeden dla osoby w temacie wystarczy :D

[qrzysztof]

W każdej tabeli mam jeden klucz podstawowy założony na id.

Samo 13k w jednej tabeli to może nic, ale przy lewostronnym złączeniu jak powstanie 13k x 270 to już 3,5 mln będzie (o ile dobrze liczę, może mniej, ale chyba i tak sporo). A coś na razie nie udaje się uniknąć tego złączenia żeby wyciągnąć te dane.

Ten post edytował qrzysztof 3.04.2012, 05:05:39

[thek]

Klucze powinny być z reguły założone na te pola, po których następuje łączenie, grupowanie i są często używane przy where. Dzięki indeksom operacje na tych kolumnach są szybsze. Zresztą dla testów weź sobie załóż indeks na kolumnę id_obiektu w t_oceny i t_komentarze. Powinno przyspieszyć. Zresztą zrób sobie EXPLAIN zapytania i sam zobaczysz co Ci sama baza podpowiada.

Czasy wydają mi się dziwne, bo wielokroć używam znacznie wiekszych tabel, łącząc nawet po 4-5 ze sobą i w naprawdę ogromnych rzadko kiedy przekraczam sekundę... Na PW podrzucę Ci zapytanie, które faktycznie mogłoby ubić ładnie serwer ze źle założonymi indeksami a wykonuje się przy kilku tysiącach rekordów w czasie ułamków sekundy.

[qrzysztof]

Dzięki! Wystarczyło dodać indeks na polach id_obiektu w t_oceny i t_komentarze i czasy zleciały z 2,5 - 3 sekund do akceptowalnych poziomów 0,05 - 0,2 sekundy.

[thek]

W takim razie dorzuć sobie jeszcze brakujący count() jako podzapytanie w SELECT i zobacz jak teraz Ci całość złapie. Baaardzo możliwe, że po założeniu tych dodatkowych indeksów uzyskasz akceptowalny poziom z wszystkimi trzema interesującymi Cię danymi jednocześnie.