Dyskusja: optymalizacja baz danych, Zliczanie rekordów z drugiej tabeli Opcje

[Athlan]

Witam,

Dziś chciałbym podyskutować troszkę na temat optylamizacji zapytań do baz danych.

Ostatnio mam problem z pobraniem ilości komentarzy przypisanych do listy notek na blogu, a że będzie to dość wielki system blogowy oferujący dobudowanie swojej cegiełki w Internecie, zależy mi na maksymalnej optymalizacji, dlatego poświece temu ten oto topick.

Wracając do zliczania komentarzy. Mamy 3 sposoby:

1. Zapisywać ilość komentarzy (notes_comments = notes_comments + 1) przy każdym dodawaniu komentarza do notki, a przy usuwaniu odejmować tą wartość.

2. Zliczać ilość komentarzy zapytaniem, które przelatuje przez tabelą komentarzy licząc je i grupując wg ich ID, ale tylko te, które przynależą do notek wyświetlonych na liście, przykładowo:

[PHP] pobierz, plaintext 
<?php
  public function getUsed(array $aNotes)
  {
	$this->_connect();
	$oDb = $this->getSource();
 
	$sSql = '
	SELECT `comment_blog_note` AS `note`, COUNT(`comment_id`) AS `comments`
	FROM `comments`
	WHERE `comment_blog_note` = ' . implode(' OR `comment_blog_note` = ', $aNotes) . '
	GROUP BY `comment_blog_note`';
	$rResult = $oDb->Execute($sSql);
 
	$aComments = array();
 
	while($aRow = mysql_fetch_array($rResult))
	{
	  if(!is_array($aUsedTags[$aRow['note']]))
				   $aUsedTags[$aRow['note']] = array();
 
	  $aComments[$aRow['note']][] = $aRow['comments'];
	}
 
	return $aComments;
  }
?>
[PHP] pobierz, plaintext 

Otrzymujemy tablicę: NOTKA => ILOŚĆ_KOMENTARZY, później możemy połączyć ją w PHP.

3. Dołączmy JOINA i countujemy komentarze przpisane do danej notki:

[SQL] pobierz, plaintext 
SELECT n.*, COUNT(c.comment_id) AS count_comments FROM notes AS n
LEFT JOIN comments AS c ON (c.comment_blog_note = n.note_id)
GROUP BY note_id
ORDER BY note_time DESC
[SQL] pobierz, plaintext 

-----------------------------------------------------------------------------------------

Wykonując sposób pierwszy, nie mamy doczynienia z żadnym liczeniem. Chciałbym trochę podyskutować na temat sposobu 2 i 3. Doszły mnie słuchy od @cichy'ego i od @hwao, że przy wielkiej ilości rekordów baza może trochę zamulać. Zadaje więc 2 pytania:

Całe sedno sprawy

1. Baza danych zacznie zamulać w przypadku dużej ilości rekordów przypisanych do notki?
2. Baza danych zacznie zamulać w przypadku dużej ilości rekordów w tabeli komentarzy?

Jak narazie jestem skłonny jak najbardziej do sposobu nr 1, ale chciałbym omówić dwa pozostałe.

Zapraszam do dyskusji Athlan

[Athlan]

Przeprowadziłem testy, miałem do dyspozycji:

• 10 000 notek
• do każdej notki po 10 komentarzy, czyli 100 000
• sumarycznie operowałem na 110 000 rekordach

Zapytanie ze sposobu nr 3, operacja z limitem na 1000 rekordów:

[SQL] pobierz, plaintext 
SELECT n.*, COUNT(c.comment_id) AS count_comments FROM megatest_notes AS n
LEFT JOIN megatest_comments AS c ON (c.comment_note = n.note_id)
GROUP BY note_id
LIMIT 0, 1000
[SQL] pobierz, plaintext 

Pokaż rekordy 0 - 999 (1 000 wszystkich, Wykonanie zapytania trwało 0.0111 sekund(y))

Sposób 3 na 2000 rekordów:

Pokaż rekordy 0 - 1999 (2 000 wszystkich, Wykonanie zapytania trwało 0.0216 sekund(y))

Sposób 3 na 5000 i 10000 rekordów: phpmyadmin zwraca pustą stronę.

Teraz przetestujemy sposób 1, wybierając id rekordów z notek (załóżmy, że jest to pole ilości komentarzy)

Sposób 1, 100 rekordów:

[SQL] pobierz, plaintext 
SELECT note_id FROM `megatest_notes` LIMIT 0, 1000
[SQL] pobierz, plaintext 

Pokaż rekordy 0 - 99 (100 wszystkich, Wykonanie zapytania trwało 0.0015 sekund(y))

Sposób 1, 500 rekordów:
phpmyadmin zwraca pustą stronę.

Wniosek? Chyba będę używał sposobu nr 3, bo nie przewiduję aż tak wielkiej bazy na jakiej testowałem moje przemyślenia.

Jak się do tego ustosunkujecie? Jakieś rady?

Dzięki za odpowiedzi, Athlan