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.

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.

[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

@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.

@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ą

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.

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.

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...

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...

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.

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.

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.

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.