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

[SongoQ]

To moze ja sie wypowiem troche w tej kwestii. Podales 3 przyklady wykorzystania, jesli chodzi o optymalne rozwiazanie i ilosc komentarzy do posta to nie wiem jak bys kombinowal to 1 przykad jest najbardziej optymalny - dlaczego? Wyciagasz 1 rekord jako PK z 1 tabeli gdzie masz juz 1 wartosc, opleracje zewnetrznych zlaczen jak i agregacji sa calkowicie pomijane.

Co do przykladu nr 3 warto podkreslic ze na optymalizacje wchodzi kilka czynnikow. 1 to index na pola zlaczeniowe, 2 index na pole uzywane w sortowaniu.

Co do stwierdzenia ze przy duzych danych baza bedzie zamulac to pomysl ma szybciej dzialac? Przeciez LEFT JOIN to odczytanie krotek z jednej tabeli a nastepnie w petli dolanczanie do 2 tabeli a nastepnie agregacja sortowania i inne cuda, wiec co szybciej jest wykonac 10x petle czy powiedzmy 10 mil? Wazne w bazie sa indeksy - to tak jak w ksiazce otwierasz spis tresci i wiesz jaki temat Cie interesuje i jaka strone chesz otworzyc bez czytania kazdej strony.

Co do Twoich testow to wsumie wykonywales na malej ilosci danych.

Jesli chcesz dobrze operowac cache w bazie danych to takie narzedzie jak bind jest Ci pomocne. Mowie tutaj o cache stringa zapytan w bazie. Kiedys o tym juz pisalem, po co sie stosuje prepare i bind.

Co do cache juz w samym php to tez ma sens jesli nawet cache mial by sie oswierzac 1 na 10 zadan.