MySQL optymalizacja RAND()

[yavaho]

Mam baze, ktora ma około 10k rekordów a może mieć za niedługo 100k i wiecej.

W zapytaniu muszą być spełnione pewne warunki, nie można wybrać losowo rekordów z całej tabeli jak leci (w rzeczywistości jest jeszcze więcej warunków)

Do tej pory jest tego typu zapytanie:

SELECT * FROM `tabela` WHERE `id_typ` NOT IN(1,2,3) GROUP BY `id_grupa` ORDER BY RAND() LIMIT 10

Jak to można zoptymalizować? Chodzi mi o pozbycie się RAND z zapytania.

Mógłbym pobrać wszystkie interesujące mnie rekordy tylko z samymi id

SELECT `id` FROM `tabela` WHERE `id_typ` NOT IN(1,2,3) GROUP BY `id_grupa`

potem w php za pomocą rand wybrać te 10 rekordów i w pętli zrobić 10 zapytań już do konkretnego id (bez żadnych warunków)

Ale czy to będzie wydajniejsze? Pobranie prawie całej tabeli a potem jeszcze 10 zapytań?

Było już na forum o tym, ale to było dla prostego zapytania, ktore nie ma warunków, kiedy można sobie pobrać maksymalną ilość rekordów.

[KuRT]

nie musisz robić potem 10 zapytan, po prostu wylosuj w php te 10 rekordów i wrzuć na koniec zapytania warunek

WHERE id IN ($wylosowany_id1,$wylosowany_id2.....)

[smut]

Może nie zrozumiałem do końca, ale czy nie możesz zrobić tak.

SELECT `id` FROM `tabela` WHERE `id_typ` NOT IN(1,2,3) GROUP BY `id_grupa` order by id ASC

co da Ci maxymalne id, oraz

SELECT `id` FROM `tabela` WHERE `id_typ` NOT IN(1,2,3) GROUP BY `id_grupa` order by id DESC

co da Ci minimalne id

i teraz,

for(ile chcesz rekordow)

{

id = rand(min,max);

}

i teraz, where in, albo 10 zapytan

[pc3t]

smut, to juz lepiej zrobic tak:

SELECT MAX( id ) , MIN( id ) FROM tabela WHERE `id_typ` NOT IN(1,2,3)

1 zapytanie i duzo szybsze. Ale tutaj sie nie sprawdzi, bo przeciez on pobiera z bazy tylko interesujace go id, anie z zakresu od - do.

BTW: order BY rand() jest naprawde tak malo efektywny, ze lepiej pobierac wszystkie id a pozniej rand z tablicy?

[yavaho]

Nie moge wybrać zakresu od-do. Bo wtedy i tak znajdą się tam dane ktore powinny być wykluczone.

BTW: order BY rand() jest naprawde tak malo efektywny, ze lepiej pobierac wszystkie id a pozniej rand z tablicy?

No właśnie gdybym tak wiedział przy ilu rekordach ktore rozwiązanie jest lepsze?

To drugie zapytanie też nie wygląda zbyt ciekawie przy dużej ilości rekordów. Do php bedzie ładowana wtedy bardzo duza tablica tylko po to aby wybrać z niej kilka id.

[pc3t]

Zaintrygowalo mnie to rand() i rzeczywiscie malo oplacalne jest.

Moze to rozwiazanie sie przyda:

https://forum.php.pl/index.php?showtopic=85...st&p=431447

[slawek22]

To ta sama klasa złożoności obliczeniowej (tzn. ten sam algorytm). Nie ma znaczenia czy to samo zrobi PHP albo mySql (właściwie ma bo jakoś te IDy trzeba przesłać, jednak to i tak najmniejszy problem).

Ja robię coś takiego

SET @counter := 0;
SET @prev := 0;
SET @group := 0;
CREATE TABLE __fast_rand (r_group int, r_id bigint, `id` int, INDEX (r_id) ) 
(
	SELECT @group:=IF(@prev<>members_userid, @group+1, @group) as `r_group` ,
		@counter:=IF(@prev<>members_userid, @group*100000, @counter+1)   as `r_id`,
		id as `id`, @prev:=members_userid FROM saved_searches ORDER BY members_userid DESC 
)

Zamiast 100000 powinien być maksymalny numer ID.

wejście:

members_userid - identyfikator `kategorii`

id - primary index

wyjście:

r_group - identyfikator `kategorii`

r_id - unikalny identyfikator wiersza zależny od grupy

id - primary index tabeli wejściowej

Musimy wygenerować pomocniczą tablicę (Array) w php (najlepiej zserializować i wczytywać według potrzeb).

SELECT count(*), r_group from __fast_rand GROUP BY r_group

Struktura tablicy:

ARR[id_grupy_wyjścia] = liczba elementów w grupie;

Teraz tak:

-- PHP --

+ Na wyjściu nie ma kategorii do której należy wiersz tabeli wejściowej (więc nie można rand-omować w zależności od kategorii ale to można łatwo zmienić kopiując pole z tabeli wejściowej).

Losowanie (TT wierszy):

+ losujemy TT grup, grupy w przedziale 0-(liczba_grup-1) - obszar ciągły

+ dla każdej wylosowanej grupy X losujemy C=0-(ARR[X]-1); Dodajemy C+=(X*100000) czyli ustalamy losowy wiersz wylosowanej grupy a potem liczymy z tego r_id

Mamy teraz r_id'y losowych elementów losowych grup (zerowy element każdej grupy zaczyna się od r_id'u r_group*100000)

-- SQL --

Wybieramy wylosowane wiersze z tabeli pomocniczej (żeby otrzymać id tabeli wejściowej)

SELECT id FROM __fast_rand WHERE r_id IN(X[0] .. X[9])

Końcowa kwerenda:

SELECT * FROM saved_searches WHERE id IN (SELECT id FROM __fast_rand WHERE r_id IN(.........))

Niestety nie używa (nie wiedzieć czemu) indeksu, zamiast tego join.

SELECT * FROM saved_searches

INNER JOIN __fast_rand

ON saved_searches.id = __fast_rand.id

WHERE r_id IN(X[0] .. X[9])

Na razie tabela ma 20000 wierszy.

RAND() - bez grupowania ~0.8 sek.

Tworzenie tabeli pomocniczej ~1.2 sek

Subquery zamiast rand ~0.9 sek

Inner join zamiast rand ~0.1 sek.

@Smut:

Niestety złe rozwiązanie:

1. mniejszy problem - zakładasz, że id są ciągłe (jeśli nie są to po wykonaniu takiej kwerendy możesz nie dostać niczego). Można by znak równości zmienić na >=, wtedy może się jednak okazać że te losowe wyniki będą dość kiepsko losowe (szczególnie, jeśli skasowano wcześniej duży ciągły obszar tabeli)

2. Grupy będą się powtarzać. Mógłbyś to rozwiązać losując liczbę elementów będącą sumą ilości elementów w najliczniejszych grupach - dopiero wtedy byłaby gwarancja, że a każdym razem otrzymasz tyle wartości losowych o ile pytałeś.

[yavaho]

@slawek22

musze wgryźć się bardziej w Twoja metodę i sprawdzić jaka bedzie wydajnosc przy tworzeniu pomocniczej tabeli.

Robiłem testy i minimalnie, ale wydajniejsze jest jednak to zapytanie z RAND z mojego pierwszego postu niż ta druga metoda nawet z jednym zapytaniem (z wykorzystaniem WHERE id IN jak @KuRT podpowiedział)

Robiłem pomiary z bazą z 10k rekordów i otrzymuje czasy od 0,02 do 0,04

Doszedłem do wniosku że mysql najpierw wykonuje warunek, ktory jest w tym zapytaniu, dzięki czemu otrzymuje tablice z liczbą rekordów mniejszą o 3 lub 4 zera i wtedy dopiero z tej tablicy wybiera losowo rekordy (co już nie stanowi problemu przy takich małych ilosciach).

Nie znam algorytmow mysq'a, nie wiem w jakiej kolejności to sobie tam robi, ale tak mi się przynajmniej wydaje po czasach ktore zmierzyłem.

[slawek22]

Tak, jeśli masz dobry warunek to większość wierszy sql odrzuci na starcie. Rand się wykonuje potem, dla każdego wiersza tabeli który pozostał. Jeśli masz czas 0,04s to nie ma co myśleć o tworzeniu tabeli pomocniczej. Tylko przez to dojdzie problem z blokowaniem (READ LOCK) no i algorytm się skomplikuje.

Chyba, że są takie przypadki, że trzeba wybierać z całej tabeli wtedy będzie lipa bo nawet przy małej tabeli i paru zapytaniach serwer tym ubijesz.

Co do wybierania ID i losowania w PHP. Nie ma cudów, szybciej nie będzie - mysql robi dokładnie to samo.

[bumfank]

teoretyczny skrypt optymalizacji zamiast order by rand()

<?
query = select id from texty
i=1
while(query)
{

array(i) = query['id']
i = i + 1;
}


tak sie zapelnily wszystkie id w array 
$query2 = "select * from teksty where"
for($i = 1; $i <= 2000; $i++)
{
if($i = 2000)
{
$query2 =."id = array(mt_rand(1,3000000)) "
}
else{
$query2 =."id = array(mt_rand(1,3000000)) OR"
}
}

?>

[Panisher freelancer]

Mam dwa rozwiązania:

$in='';

$query = mysql_query("SELECT id FROM slownik WHERE active='1' ORDER by id DESC LIMIT 20");

//pobieramy ostatnie 20 wpisów ze słownika

while($row = mysql_fetch_assoc($query))

{

$in .= $row2['id'].',';

}

$in = substr($in, 0,-1);

$query2=mysql_query("SELECT id, slowo FROM slownik WHERE id IN ($in) ORDER by RAND() LIMIT 4") ;

//z 20losujemu 4 rekordy.

$wynik = array();

while($row=mysql_fetch_assoc($query))

{

$wynik[]=$row['slowo'];

}

2 Rozwiązanie:

		 $wynik = '';
	 $query = mysql_query("SELECT slowo FROM slownik WHERE active='1'  ORDER by id DESC LIMIT 20");
	 while($row = mysql_fetch_assoc($query))
	 {
	 $wynik[] = $row['slowo'];
	  }
$losuj = array_rand($wynik, 4);

$wynik2 = array();
foreach($losuj as $key=>$value)
{
	$wynik2[$key] = $wynik[$value];
}

[shad]

Ja stosuję coś takiego. Przy jonie na 3 tabelkach po 600-700 tys rekordów każda przyśpieszenie rzędu 20-30 razy w zależności od sytuacji.

$q='SELECT 
count(*) as ilosc
FROM 
katalogi as a 
JOIN katalogi_kategorie as b ON a.id=b.katalog
JOIN katalogi_podkategorie as c ON a.id=c.katalog	AND b.id=c.kategoria	
WHERE 
b.kategoria IN ('.implode(',',$i_kat).') AND c.podkategoria IN ('.implode(',',$i_pkat).') AND 
a.status IN (1,3) AND a.typ IN ("qlweb", "omini", "bizzdir")
AND (SELECT COUNT(*) FROM linki_to_katalog_status AS k WHERE k.katalog=a.id AND k.link='.$katalog['site_id'].')<'.$katalog['max_wyslanych'].' '.$q_user.$q_kat.' ';
$w=sql_query($q);
$d=sql_fetch_array($w);
if ($d['ilosc']>0){

$offset=rand(0,$d['ilosc']-1);	
$q='SELECT 
a.id as katalog_id, 
a.url as katalog_url, a.typ as katalog_typ, a.mod as katalog_mod, a.capacha as katalog_capacha, 
d.nazwa as kategoria_nazwa,
b.id as kategoria_id, 
b.url as kategoria_url,
e.nazwa as podkategoria_nazwa,
c.id as podkategoria_id, c.url as podkategoria_url
FROM 
katalogi as a 
JOIN katalogi_kategorie as b ON a.id=b.katalog
JOIN katalogi_podkategorie as c ON a.id=c.katalog	AND b.id=c.kategoria	
JOIN katalogi_kategorie_nazwy as d ON b.kategoria=d.id		
JOIN katalogi_podkategorie_nazwy as e ON c.podkategoria=e.id
WHERE 
b.kategoria IN ('.implode(',',$i_kat).') AND c.podkategoria IN ('.implode(',',$i_pkat).') AND 
a.status IN (1,3) AND a.typ IN ("qlweb", "omini", "bizzdir")
AND (SELECT COUNT(*) FROM linki_to_katalog_status AS k WHERE k.katalog=a.id AND k.link='.$katalog['site_id'].')<'.$katalog['max_wyslanych'].'						
'.$q_user.$q_kat.' LIMIT '.$offset.', 1
$w=sql_query($q);

[βασιλιάς]

Ja tworzę kolumnę rand_index, przy dodawaniu rekordu wstawiam tam wynik RAND().

Zamiast losowego rekordu wybieram później rekord (czy kilka rekordów) o najmniejszym rand_index i zmieniam mu rand_index na nowy RAND().

W ten sposób nie uruchamia się nigdy powolny filesort jak przy SELECT ... ORDER BY RAND().

[papieros]

βασιλιάς (toś se nick wybrał ma rację, można to robić tak jak on proponuje lub jesli RAND wystarczy wykonac tylko co jakis czas to mozna

wywolywac UPDATE'a i sprawa zalatwiona.

@yavaho, jesli robisz testy wydajnosci roznych sposobow losowan, to podziel się pozniej wynikami

[garyoldman]

@βασιλιάς

Nie wiem czy dobrze zrozumiałem, ale w Twoim rozwiązaniu jeżeli na "dzień dobry" dany rekord otrzyma przy insercie wysoki wynik RAND() powiedzmy 0.9... to nigdy potem nie zostanie "wylosowane"?