Liczba tematów i postów w forach Opcje

[peku33]

Witajcie. Tworzę forum internetowe i mam problem ze stworzeniem odpowieniego zapytania do bazy.
Mam takie tabele

Forums z kluczem ForumId
Threads z kluczem ThreadId oraz indexem na ThreadId
Posts z kluczem PostId i indexem ThreadId

I teraz moje pytanie - jak do SELECT (..) FROM Forums dodać liczbę postów i tematów w tym forum

Próbowałem LEFT JOIN 2 i 3 tabelka, ale liczba nie była poprawna. Dodam, że każda z tabel ma pole Show, i powinna być zliczana tylko gdy jest ono równe 1

[thek]

Coś więcej o strukturze, bo jak dla mnie to powinno być:
Posts z kluczem na Id i ThreadId (chyba warto wiedzieć w jakim temacie jest post)
Threads z kluczem na Id i ForumId (chyba warto wiedzieć w jakim forum jest temat)
Forums z kluczem na Id
Teraz JOIN między tabelami wychodząc od Posts jako głównej i grupowanie po ForumId z tabeli Threads oraz count na odpowiednich Id. Ale to jest MAŁO optymalne bo ilość wywołań tego zapytania będzie OGROMNA. Zrób sobie lepiej jakieś dodatkowe pola związane ze statystyką w tabelach Forums i Threads. W przypadku dodawania lub odejmowania tematów i postów, zwiększaj lub zmniejszaj tu owe dane statystyczne. Inaczej zarżniesz bazę liczeniem w kółko. W takim przypadku rezygnacja z nadmiarowości w bazie ma jak najbardziej uzasadnienie. Jeśli ktoś Ci zacznie pisać bajki o normalizacji bazy to go zapytaj wtedy o wydajność, a się zamknie (IMG:style_emoticons/default/wink.gif)

[bar_bara]

Może ten mój szkic Ci coś pomoże:

[SQL] pobierz, plaintext 
SELECT t.ThreadId, count(p.PostId) FROM Forums f, Threads t, Posts p WHERE t.ThreadId=p.ThreadId AND t.ForumId=f.id AND f.ForumId = IDszukanego AND t.SHOW=1 AND p.SHOW=1 GROUP BY t.ThreadId
[SQL] pobierz, plaintext 

I teraz w skrypcie php odczytać ilość wierszy - to liczba tematów, a następnie zsumować drugą kolumnę - to liczba postów w tym forum.

A jak to będzie wyglądało wydajnościowo to zawsze możesz podpatrzeć przez DESC SELECT...
To Show tu trochę energii pochłonie

[peku33]

Nie mogę sobie pozwolić na pobieranie wszystkich wierszy.

Ostatecznie skonstruowałem takie zapytanie:

[SQL] pobierz, plaintext 
				SELECT F.ForumId, F.Name, COUNT(T.ThreadId) AS ThreadNum, SUM(T.PostNum) AS PostNum, T.ThreadId AS LastThreadId, T.Name AS LastThreadName, MAX(T.PostLastDate) AS PostLastDate, COUNT(T.ThreadId)-SUM(T.ThreadRead) AS UnreadPosts
				FROM ForumsForums AS F
				LEFT JOIN(
					SELECT T.ThreadId, T.ForumId, T.Name, SUM(P.PostNum) AS PostNum, MAX(P.PostLastDate) AS PostLastDate, COUNT(V.ThreadId) AS ThreadRead
					FROM ForumsThreads AS T
					LEFT JOIN (
						SELECT COUNT(PostId) AS PostNum, ThreadId, MAX(Date) AS PostLastDate
						FROM ForumsPosts
						WHERE Public=1
						GROUP BY ThreadId
					) AS P USING ( ThreadId )
					LEFT JOIN ForumsThreadViews AS V ON (T.ThreadId=V.ThreadId AND V.UserId='%d' AND V.UnseenPostId = 0)
					WHERE Public=1
					GROUP BY ThreadId
					ORDER BY PostLastDate DESC
				) AS T ON(F.ForumId=T.ForumId)
				WHERE F.Public=1 AND F.BoardId='%d'
				GROUP BY F.ForumId
				ORDER BY Sort ASC
[SQL] pobierz, plaintext 

Każda tabela ma PRIMARY na swoje Id (ForumsForums - ForumId) + klucz rodzica i te według których grupuję / sortuje / whereuje

EXPLAIN zapytania wywala:

Kod

1     PRIMARY     F     ref     Public,BoardId     BoardId     2     const     1     Using where; Using temporary; Using filesort
1     PRIMARY     <derived2>     ALL     NULL    NULL    NULL    NULL    4     
2     DERIVED     T     ref     Public     Public     1         3     Using temporary; Using filesort
2     DERIVED     <derived3>     ALL     NULL    NULL    NULL    NULL    4     
2     DERIVED     V     eq_ref     PRIMARY,ThreadId,UserId     PRIMARY     12     xxx.T.ThreadId     1     
3     DERIVED     ForumsPosts     ALL     Public     NULL    NULL    NULL    46     Using where; Using temporary; Using filesort

Jestem rootem na serwerze, mam 16GB ramu do dyspozycji więc mogę zwiększyć jakoś wartości pamięci podręcznej itp.

Jeszcze trochę o typach danych
ForumId, ThreadId - INT
PostId, UserId - BIGINT
Public - BOOL
Date - TIMESTAMP

Teraz pytanie, jak to się zachowa przy 100k postów i 20k tematów. Czy nie zamuli serwera?
Może lepiej ilość postów przechowywać jako pole w tabeli nadrzędnej?