butcher.art.pl

Bardzo często zdarza się potrzeba pobrania z bazy danych kilku losowych wierszy – np. losowe zdjęcia na głównej stronie. MySQL dostarcza bardzo przydatną funkcję matematyczną RAND(). Zwraca ona wartość z przedziału <0, 1>. Dzięki temu możemy sobie wykonać takie zapytanie:

SELECT id, title, file FROM photo ORDER BY RAND() LIMIT 5

W odpowiedzi otrzymamy 5 zdjęć wybranych losowo. Jak to działa w praktyce? Silnik MySQL dla każdego wiersza z tabeli photo pobierze wartości id, title, file oraz wygeneruje losową wartość z przedziału <0, 1>. Następnie posortuje je w kolejności od najmniejszej do największej i zwróci 5 pierwszych wartości.

Jak działa RAND()? Troszkę inaczej niż u człowieka. Wymyśl sobie liczbę z przedziału 0 do 1000. Łatwe? Komputer tego nie potrafi! Nie jest spontaniczny, potrafi tylko liczyć. Dlatego podanie liczby losowej wbrew pozorom nie jest takie proste. Są różne algorytmy do liczenia takich liczb. Często opierają się na aktualnym czasie w mikrosekundach do tego wykonując jakieś skomplikowane obliczenia, aby liczba była za każdym razem inna. Fachowo nawet nie nazywa się to liczba losowa, tylko pseudolosowa :)

Po co o tym piszę? Trzeba sobie uświadomić, że jest to stosunkowo pracochłonny proces. W przypadku tabeli która ma kilka tysięcy rekordów nie stanowi to problemu. Jednak kiedy mamy kilkadziesiąt tysięcy rekordów, obliczenie dla każdego liczby losowej już trochę trwa.

Ale to jeszcze nic!

Najbardziej czasochłonne jest sortowanie! W przypadku sortowania po dowolnej kolumnie MySQL tworzy sobie indeksy. Tzn. nie sprawdza wszystkich rekordów, tylko od razu wie gdzie znajdzie rekordy o odpowiednich wartościach. Taki jakby katalog w bibliotece. Jak chcesz wypożyczyć książkę w bibliotece (co zdarza się coraz rzadziej) to pani sprawdza sobie w indeksie gdzie książka się znajduje i idzie do odpowiedniej półki, a nie sprawdza po kolei każdą książkę, czy przypadkiem to nie ta, której szuka :)

Skoro już wiadomo z jakiego powodu to zapytanie jest wolne dla dużej ilości danych, można przejść do usprawnienia tego procesu. Najpierw pobierzemy minimalną i maksymalną wartość kolumny id.

<?php
$v = mysql_fetch_object('SELECT MIN(id) min, MAX(id) max FROM photo');
?>

Wybierzmy teraz losowy wiersz z tabeli photo:

<?php
$randId = rand($v->min, $v->max);
$randomRow = mysql_fetch_object("SELECT id, title, file FROM photo WHERE id >= $randId  LIMIT 1");
?>

Losujemy liczbę pomiędzy minimalną wartością id, a maksymalną (w PHP funkcja rand() działa trochę inaczej), a następnie szukamy wiersza, który ma wartość większą lub równą tej liczbie. Dlaczego większą? Bo może się zdarzyć, że nie ma wiersza o takim id, wtedy będziemy szukać następnego.

No dobra, ale jak wybrać 5 rekordów? Wykonać 5 zapytań? Nie ma innego wyjścia, ale można to zrobić trochę sprytniej. Oto całość funkcji:

<?php
function getRandomPhotos($count = 5) {
	$v = mysql_fetch_object('SELECT MIN(id) min, MAX(id) max FROM photo');
	$results = array();
	for ($i=0; $i>$count; $i++) {
		$randId = rand($v->min, $v->max);
		$results[] = "(SELECT id, title, file FROM photo WHERE id >= $randId LIMIT 1)";
	}
	return mysql_fetch_object( implode(' UNION ', $results) );
}
?>

Tym sposobem, za pomocą 2 zapytań i trochę kodu PHP mamy bardzo szybki algorytm do wybierania losowych rekordów. Nie jest on oczywiście wolny od wad. Po pierwsze trzeba użyć kodu PHP do zapytania, czego się powinno unikać jeśli to możliwe. Można by napisać skrypt w MySQL (jeśli jest ktoś chętny to proszę :) ). Po drugie może się zdarzyć, że wybierzemy 2 razy ten sam wiersz. Żeby tego uniknąć trzeba by dopisać dodatkowe warunki. Ale tak jak już wspomniałem, jest sens bawić się w to rozwiązanie kiedy jest ogromna ilość rekordów. A wtedy prawdopodobieństwo wylosowania takiego rekordu jest bliskie 0.0000000001 :)

Kategoria Porady |