Losowanie [MySQl]

[M@k]

Witam

Mam takie pytanie. Bo kombinuje jak koń pod górkę i nie wiem jak to rozwiązać.

Chodzi mi o optymalizacje Selecta z rand (losowanie rekordów z bazy ale z warunkiem).

Jeśli mamy do czynienia z losowaniem np. 10 elementów z całej bazy to można zrobić tak.

SELECT * FROM `tabela`ORDER BY RAND() LIMIT 10

ale jak wiadomo to nie jest optymalne i lepiej zrobić np. Tak;

$count = mysql_num_rows(mysql_query('SELECT COUNT(`'.$primary.'`) FROM `'.$table.'`));

$num = rand(0, $count - 1);

mysql_query('SELECT `'.$fields.'` FROM `'.$table.'` LIMIT '.$num.', 1');

I chciałbym to samo zrobić ale z warunkiem, że nie ma losować z całej bazy tylko jeśli zajdzie spełnienie warunku.

I nie wiem jak to;

$zapytanie = "SELECT * FROM ".$table ." WHERE dane LIKE '%".$znajdz."%' ORDER BY rand() LIMIT 15";

przerobić na ten drugi sposób pokazany wyżej. Bo to jest masakra dla serwera przy dużej bazie.

Bardzo był bym wdzięczny o jakieś wskazówki.

[Mion]

Do losowania w dużej bazie w ogóle nie należy stosować rand, bo jest to funkcja bardzo wolna. Należy to rozwiązać w inny sposób, a można na kilka sposobów zależnych od dalszych operacji na zbiorze wyników, wielkości bazy itd.

---

Podobny temat już był jakiś czas temu poruszany w tym dziale...

[M@k]

No a nie mógł byś napisać jak to można być rozwiązać. Bo własnie wiem że randa nie powinno się używać. Baza ma 100 tys - 200 tys i rośnie.

Czego ja nie rozumie. Mianowicie.

Mogę zrobić np tak

$zapytanie = "SELECT * FROM ".$table ." WHERE dane LIKE '%".$znajdz."%' ";

$max= mysql_num_rows($sql);

Wtedy mam np 1000 elementów spełniających warunek. Ale teraz jak to losowo np. w pętli for wyświetlić nie wiem. Bo nie znam id jakie są. Bo mogę sobie losować jakieś id ale one nie konieczne musza być w tym selekcie.

[Mion]

@No a nie mógł byś napisać jak to można być rozwiązać.

Za dużo pisania, a na to nie mam obecnie czasu...

[slawek22]

Wynik like do cache:

$zapytanie = "SELECT id FROM ".$table ." WHERE dane LIKE '%".$znajdz."%' ";

użyj pack i zapisz wszystkie ID jako integer potem umieść w bazie jako BLOB.

Później.

select / unpack. Wielkość tablicy to strlen (x) / 4 ( int ma 4 bajty).

Robisz 10 razy rand i wybierasz 10 losowych elementów

drugi select.

SELECT * from ... WHERE id IN (el1,...el10);

Przy 200k elementach to będzie jakieś 800 kb na cache (założenie pesymistyczne).

Edit: jeśli w bazie ID są ciągłe wystarczy zrobić select id = 0 - ilość elementów w tablicy.

Jeśli nie są ciągłe wystarczy id <= ilość elementów w tablicy ale nie będziesz miał like a wyniki będą pseudo-losowe (niektóre wpisy będą się powtarzać częściej zależnie od tego jak bardzo ID jest nieciągły).

[websign]

Ja bym tego zdecydowanie przez MySQLa nie robił.

1) Jedno zapytanie do MySQLa sprawdzające ile jest rekordów spełniających dany warunek

2) W PHP wybranie x losowych numerów od 1 do ilosc_rekordow

3) x zapytań do MYSQLa z danym warunkiem i odpowiednim LIMIT albo jedno zapytanie z UNION

Oczywiście pozostaje zapytanie jak wolne jest dane LIKE '%".$znajdz."%' bo jak bardzo wolne, to też różnie może być

[slawek22]

Oczywiście pozostaje zapytanie jak wolne jest dane LIKE

Włąśnie po to jest cache do LIKE, teoretycznie fakt plik byłby do tego lepszy, jednak w mysql też prawdopodobnie będzie się dało użyć operacji na łańcuchach i wyciągnąć dane z cache bez odczytu całego BLOB (poza tym możesz taką tabelę łatwo przenieść do pamięci).

1) Jedno zapytanie do MySQLa sprawdzające ile jest rekordów spełniających dany warunek

2) W PHP wybranie x losowych numerów od 1 do ilosc_rekordow

3) x zapytań do MYSQLa z danym warunkiem i odpowiednim LIMIT albo jedno zapytanie z UNION

Edit: W ten sposób powtarzasz niepotrzebnie wolne LIKE 9 razy i to like nie będzie cacheowane przez mysql (bo za każdym razem kwerenda inna)

Mógłbyś w pierwszej kwerendzie pobrać wszystkie ID do tablicy i pominąć like w punkcie 3, jednak jeśli warunek będzie luźny to będziesz miał dużo niepotrzebnego transferu między PHP a serwerem bazy.

[M@k]

Dzięki wszystkim za odpowiedzi.

Na razie testowałem to:

1) Jedno zapytanie do MySQLa sprawdzające ile jest rekordów spełniających dany warunek

2) W PHP wybranie x losowych numerów od 1 do ilosc_rekordow

3) x zapytań do MYSQLa z danym warunkiem i odpowiednim LIMIT albo jedno zapytanie z UNION

i na małej bazie ok 2 tys rekordów to z randem jest szybsze o rząd wielości.

Z randem mniej więcej 0.005 s

a to wyżej 0,05

Prawdopodobnie ten sposób będzie się lepiej spisywał na większej bazie, ale jeszcze nie testowałem.

Jeszce slawek22 muszę sie przymierzyć to twojej propozycji.

[Mion]

Sprawdzisz ile jest rekordów odpowiadających <OK

W PHP wybranie x losowych numerów od 1 do ilosc_rekordow < OK jedynie pod warunkiem, że masz w bazie ciągłość w ID. Jednak w bazie produkcyjnej przeważnie ciągłości nie ma bo userzy kasują dane więc jeśli będziesz wybierał po ID będziesz trafiał na puste miejsca. Chyba, że będziesz wybierał z dużym zapasem licząc, że prawdopodobieństwo wybrania potrzebnej ilości rekordów będzie wystarczając duże.

ewentualnie możesz zastosować mechanizm rekurencyjny który sprawdzi czy jest określona ilość rekordów jeśli tak zwróci wyniki jeśli nie dolosuje itd i tak do skupu

[sorrow]

$query = mysql_query("SELECT pole1,pole2,pole3 FROM tabela");

$all = array();

while ( $row = mysql_fetch_array($query, MYSQL_ASSOC) ) :

$all[] = $row;

endwhile;

arsort($all);

// np. https://www.php.net/manual/en/array.sorting.php

$iled = count($all);

for ($i=0;$i<10; $i++) {

//coś tam

}

// Albo lepiej foreach

[Mion]

sorrow coś się rozpędziłeś z odpowiedzią

----

PS odnośnie jakiego postu podajesz swój kod, bo chyba nie bieżącego z propozycją pobierania wszystkich rekordów i umieszczania ich w tablicy do dalszego przetasowania

[slawek22]

Wiesz to z randem na małej bazie faktycznie będzie szybsze (można przyjąć, że na bazie do pewnego momentu każda kwerenda zajmuje tyle samo czasu). Bo wszystko jest w pamięci a SCAN też jest szybszy od INDEXów poniżej X rekordów. Więc jeśli X < 50k to ja bym zostawił to z RAND, bo to tak jakby "jedna" kwerenda a drugi sposób to 10 kwerend (i tak 10x like powinien być szybszy na dużej bazie bo z RAND to się skończy na przetwarzaniu każdego rekordu + sortowanie na dysku, kiedy baza urośnie).