[MSSQL][PHP] Wybor losowego elementu z bazy, spelniajacego dane kryteria. Opcje

[bercow]

Pisze wlasnie prosty system banerowy, i zastanawiam sie jak wylosowac dany rekord, spelniajacy warunki, mimo, iz raczej RAND w bazie przy max 100 rekordach, nie jest jakims duzym opciazeniem, to czytalem, ze nikt go z wzgledow wydajnosci nie poleca. Chce pobrac * rekordy gdzie jezlei limit !=0 to musi spelnic limit_value<=limit_stop oraz jezeli date !=0 to musi zostac spelniona wartosci date_start <= date_stop i na koniec value='1', Dodatkowo, jezeli limit = 1 to limit_value++.

Staralem sie skonstuowac cos w stylu array_rand($n = mysql_fetch_assoc($query)), ale nie chce pobrac wszytskich mozliwych ID do tablicy, a potem znowu zapytanie, i nowe, i ew. trzecie zapytanie UPDATE do limit_value.

[SQL] pobierz, plaintext 
CREATE TABLE `banner` (
  `id_banner` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` text,
  `user` int(10) UNSIGNED DEFAULT NULL,
  `img` text,
  `limit` tinyint(1) DEFAULT '0',
  `limit_value` int(10) UNSIGNED DEFAULT '0',
  `limit_stop` int(10) UNSIGNED DEFAULT NULL,
  `date` tinyint(1) DEFAULT '0',
  `date_start` date NOT NULL DEFAULT '0000-00-00',
  `date_stop` date NOT NULL DEFAULT '0000-00-00',
  `visible` tinyint(1) DEFAULT NULL,
  PRIMARY KEY  (`id_banner`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin2 AUTO_INCREMENT=6 ;
[SQL] pobierz, plaintext 

Obecnie mam dzialajace takie cos, ale nie jestem z tego zadowolony.

[PHP] pobierz, plaintext 
<?php
		require "connection.php"; 
 
		$query = mysql_query("SELECT id_banner FROM banner WHERE limit_value<=limit_stop AND date_start<=date_stop AND visible='1'") or die('Błąd zapytania');
		if(mysql_num_rows($query) > 0) { 
			$id = range(1,mysql_num_rows($query));	
			while($b = mysql_fetch_assoc($query)) { 
				$id[] = $b['id_banner'];
			}
		}
		$tab = array_rand($id);
		$query2 = mysql_query("SELECT * FROM banner WHERE id_banner='$id[$tab]' AND limit_value<=limit_stop AND date_start<=date_stop AND visible='1'") or die('Błąd zapytania');
		if(mysql_num_rows($query2) > 0) { 
			while($bi = mysql_fetch_assoc($query2)) { 
				echo "<img src=\"../img/banner/".$bi['img']."\" alt=\"reklama\" />";
			}
		}
?>
[PHP] pobierz, plaintext 

Ten post edytował bercow 28.04.2011, 22:38:20

[pmir13]

Przeanalizujmy typowe rozwiązanie nr 1:

[SQL] pobierz, plaintext 
SELECT * FROM tabela WHERE warunki ORDER BY rand() LIMIT 1
[SQL] pobierz, plaintext 

Dlaczego jest to mało wydajne? Ponieważ dla każdego rekordu spełniającego warunki odpalana jest funkcja rand, wiersze sortowane są wg wyników tej funkcji a następnie zwracany jest pierwszy z tych posortowanych wierszy.
2. To co proponujesz w zamian to pobranie wszystkich rekordów spełniających warunki i wylosowanie jednego z nich przez php. Unikamy sortowania i wielokrotnego wywoływania rand(), ale wciąż przesyłamy niepotrzebnie wszystkie pasujące rekordy.
Proponuję rozwiązanie 3:
a. Obliczyć n - liczbę rekordów spełniających warunki przez zapytanie do mysql. To będzie szybkie zapytanie, prawie takie samo jakie musiałoby być wykonane w rozwiązaniu 2, tyle że select count(*) zamiast select *, dzięki temu nie przesyłamy wszystkich rekordów, ale jedną liczbę - ilość tych rekordów.
b. Wylosować w php x - numer rekordu do pobrania.
c. Wykonać zapytanie z tymi samymi warunkami ponownie, tylko że tym razem już bez count ale na końcu odpowiedni limit by wybrać konkretny rekord.
W ten sposób przesyłamy tylko jeden losowy rekord, uruchamiamy funkcję losującą tylko raz, a zapytania do bazy danych są szybkie.

Tyle teorii, w praktyce to będą kosmetyczne sprawy, odpowiednie dobranie indeksów jest o wiele ważniejsze.

Ten post edytował pmir13 28.04.2011, 23:35:09

[bercow]

Obecnie calosc mam taka, ale nie rozumiem co mi daje count, faktycznie wiem, ile rekordow spelnia moje zapytanie, ale nie wiem ktore. Mamy 3a, a wiec wiemy ile rekordow, jak zrobic 3b, niech sie wylosuje z 1..4 liczba 3, a 3 rekord jest nie prawidlowy, bo prawidlowe sa np. 1, 2, 4, 5.

[PHP] pobierz, plaintext 
<?php
 
		require "connection.php"; 
		//liczba rekordow spelniajacych kryterium
		$query4 = mysql_query("SELECT count(*) FROM banner WHERE limit_value<=limit_stop AND date_start<=date_stop AND visible='1'") or die('Błąd zapytania');
		$count = mysql_result($query4 , 0);
		echo $count; 
 
		////jw. ale tutaj juz mam konkretny rekord, pobieram id_banner
		$query = mysql_query("SELECT id_banner FROM banner WHERE limit_value<=limit_stop AND date_start<=date_stop AND visible='1'") or die('Błąd zapytania');
		if(mysql_num_rows($query) > 0) { 
			$id = range(1,mysql_num_rows($query));	
			while($b = mysql_fetch_assoc($query)) { 
				$id[] = $b['id_banner'];
			}
		}
		//pobieram all dane, z wylosowanego id
		$tab = array_rand($id);
		$query2 = mysql_query("SELECT * FROM banner WHERE id_banner='$id[$tab]' AND limit_value<limit_stop AND date_start<=date_stop AND visible='1'") or die('Błąd zapytania');
		if(mysql_num_rows($query2) > 0) { 
			while($bi = mysql_fetch_assoc($query2)) { 
				echo "<img src=\"../img/banner/".$bi['img']."\" alt=\"reklama\" />";
				if ($bi['limit'] ==1) {
					//dodaje +1 do liczby wyswietlen i aktualizuje je
					$new_value = $bi['limit_value']+1;
					$query3 = mysql_query("UPDATE banner SET limit_value ='$new_value' WHERE id_banner ='$id[$tab]'");
					if ($new_value >= $bi['limit_stop']) $query4 = mysql_query("UPDATE banner SET visible ='0' WHERE id_banner ='$id[$tab]'");
					}			
			}
		}		
?>
[PHP] pobierz, plaintext 

[pmir13]

Przecież rekordy ograniczasz tym samym warunkiem co przy zliczaniu, wybierasz n-ty rekord spełniający te warunki.
Jeśli masz np prawidłowe 1,2,4,5, czyli

[SQL] pobierz, plaintext 
SELECT * FROM tabela WHERE warunki
[SQL] pobierz, plaintext 

zwraca ci cztery rekordy o id 1,2,4,5 a wylosowałeś 3 z zestawu 0-3 (bo liczymy od zera i potrzebujemy w sumie 4)

[SQL] pobierz, plaintext 
SELECT * FROM tabela WHERE warunki LIMIT 3,1
[SQL] pobierz, plaintext 

zwróci ci rekord o indeksie 5, bo LIMIT oznacza tutaj zacznij od rekordu o kolejnym numerze w wynikach równym 3 (licząc od zera) i w sumie zwróć ich co najwyżej 1.

[Crozin]

Rozwiązanie @pmir13 można nieco ulepszyć. Zamiast pobierać liczbę rekordów spełniających warunek pobierz identyfikatory tych rekordów. Dzięki temu w drugim zapytaniu będziesz mógł wyeliminować te wszystkie warunki i pobrać wygodnie po idenryfikatorze, czyli coś w stylu:

[PHP] pobierz, plaintext 
$rs = /* SELECT GROUP_CONCAT(id) AS list_of_ids FROM tbl_name WHERE ... */;
$ids = (array) explode(',', $rs['list_of_ids']);
$id = $ids[array_rand($ids)];
 
$banner = /* SELECT ... FROM tbl_name WHERE id = $id */;
[PHP] pobierz, plaintext 

[thek]

Pomysł Crozina jest bezpieczniejszy, ponieważ składnia LIMIT offset, ilość nie jest standardem SQL i nie we wszystkich dialektach SQL występuje. W MSSQL niestety nie. tam trzeba robić kombinacje z TOP, ale od tego masz już Google, bo da się zasymulować to co w MySQL robisz prostym LIMIT. Dziś będę miły i pokażę jak to jest w obu wersjach:
MySQL:

[SQL] pobierz, plaintext 
SELECT * FROM tabela ORDER BY kolumna LIMIT a,b
[SQL] pobierz, plaintext 

MSSQL:

[SQL] pobierz, plaintext 
SELECT TOP a * FROM tabela WHERE kolumna NOT IN (SELECT TOP b kolumna FROM tabela ORDER BY kolumna) ORDER BY kolumna
[SQL] pobierz, plaintext 

Ale MySQL też cacy nie jest Spróbuj stworzyć w nim odpowiednik dostępnego w MSSQL pivot

EDIT: Byłbym zapomniał... Jeśli robisz warunki dodatkowe to WHERE w obu SELECTACH musi wystąpić by była zgodność.

[darko]

Z drugiej strony GROUP_CONCAT też nie jest standardem w SQLu, np. w MSSQL tego nie ma i trzeba sobie samemu zasymulować to zachowanie.
http://stackoverflow.com/questions/451415/...sql-server-2005

[Crozin]

@darko: Zawsze możesz sobie zrobić klasyczne SELECT id FROM i w PHP w pętli utworzyć tablicę.

[darko]

@Crozin: Zgadza się.

[bercow]

Ja losuje tak.

1. Pobieram id rekordow spelniajacych warunek id='$id'
--losuje
2. Pobieram * wylosowanego rekordu
3/4. Jak trzeba aktualizuje

@Pmir13
1. Pobiera wszytskie opcje,
--losuje
2. Pobiera * wyslosowanego za pomoca LIMIT
3/4. Jak trzeba aktualizuje

Czy LIMIT jest bardziej wydajny niz dodanie jednego warunku ? Piszac SELECT * FROM tabela WHERE warunki LIMIT 3,1 rozumiem, ze z jakiegos powodu sprawdzasz jeszcze raz warunki. I jak dobrze rozumiem, to LIMIT 3,1 zwroci jeden rokord od rekordu trzeciego. Wiem, ze bez warunkow, LIMIT 3,1 wylosowal by pozycje 4, a nie 5 bo to nowe zapytanie, ale nie widze tutaj sensu jezeli chodzi o wydajnosc.

Nie mowie, ze twoj sposob jest bez sensu, ale moze poprostu nie rozumiem Twojej idei.

@Crozin
A czy ja tego nie robie, pobieram id rekordow spelniajacych warunek, SELECT id_banner FROM banner WHERE.... Rozumiem, ze GROUP_CONCAT(id_banner) bedzie bardziej wydajniejszy, niz pobranie wszystkich, i zrobienie petli tworzacej tablice ? Dostane tablice Array[0] = 1;Array[1] = 2;Array[2] = 4;Array[3] = 5;

Druga sprawa, ze

[SQL] pobierz, plaintext 
SELECT GROUP_CONCAT(id_banner) FROM banner WHERE limit_value<=limit_stop AND date_start<=date_stop AND visible='1'
[SQL] pobierz, plaintext 

zwraca mi Pokaż rekordy 0 - 0 (1 wszystkich, Wykonanie zapytania trwało 0.0004 sekund(y)), a nizej tylko GROUP_CONCAT(id_banner) [BLOB - 9 bajtów]. Oczywiscie * zwraca wszystkie pasujace rekordy



@thek

Raczej M$SQL nie bede uzywal, malo ktory hosting daje to w rozsadnej cenie

Ten post edytował bercow 29.04.2011, 14:05:44