[PHP][MYSQL] LOSOWY REKORD, szukam sposobu bardziej wydajnego niż RAND() Opcje

[lysy2005]

Witam,
mam bazę 4 GB i chce pobrać losowy rekord spełniające moje kryteria.

Teraz mam zastosowanie:
Select pole FROM tabela WHERE pole='1' ORDER BY RAND(); LIMIT 1

Ale wykorzystując RAND() zapytanie trwa bardzo długo,
przy dużych bazach nie spełnia roli.
W jaki sposób wy wyciągacie losowe rekordy w takich dużych bazach?
Z góry dziękuje za pomoc

[matino]

Tutaj masz bardzo przydatny link: http://net.tutsplus.com/tutorials/other/to...best-practices/
Patrz punkt 6 odnośnie swojego pytania.

[lysy2005]

Tutaj masz bardzo przydatny link: http://net.tutsplus.com/tutorials/other/to...best-practices/
Patrz punkt 6 odnośnie swojego pytania.

Hm... no jest to jakiś pomysł,
ale jak dodam do pierwszego zapytanie Where, to całe zapytanie trwa 10 sekund troszkę dużo.. Wiadomo lepiej niż RAND, ale i ka długo...
Może jeszcze jakieś pomysły>

10 sekund napisałem... Zapytanie z tego przykładu dostosowane do moich potrzeb trwa 100seknd

[Hpsi]

Aż mi sie nie chce wierzyć że:

[SQL] pobierz, plaintext 
 SELECT count(`id`) AS `count` FROM tabela 
[SQL] pobierz, plaintext 

wynik counta jak dasz w mt_rand() i pobierasz potem 1 rekord, daje ci 100 sekund geenrowania

pracowałem na podobnej bazie wielkosciowo nie jeden raz i nigdy nie osiagnelem takiej "wartosci" zapytan

Ten post edytował Hpsi+ 18.06.2011, 13:18:03

[lysy2005]

Aż mi sie nie chce wierzyć że:

[SQL] pobierz, plaintext 
 SELECT count(`id`) AS `count` FROM tabela 
[SQL] pobierz, plaintext 

wynik counta jak dasz w mt_rand() i pobierasz potem 1 rekord, daje ci 100 sekund geenrowania

pracowałem na podobnej bazie wielkosciowo nie jeden raz i nigdy nie osiagnelem takiej "wartosci" zapytan

Tak, ale zobacz ze nie dodajesz przedrostka WHERE, a ja jeszcze dodatkowo daje regule do wyszukiwania.
Baza ma 4 GB, i cały czas rośnie bo są tam dodawane rekordy ok 100MB na godzinę także wieczorem będzie 5 GB

[webdice]

Aż mi sie nie chce wierzyć że:

[SQL] pobierz, plaintext 
 SELECT count(`id`) AS `count` FROM tabela 
[SQL] pobierz, plaintext 

wynik counta jak dasz w mt_rand() i pobierasz potem 1 rekord, daje ci 100 sekund geenrowania

pracowałem na podobnej bazie wielkosciowo nie jeden raz i nigdy nie osiagnelem takiej "wartosci" zapytan

Count zwróci Ci ilość rekordów. Wszystko będzie działać dobrze, o ile rekordy będą ponumerowane w odpowiedniej kolejności. Dla przykładu masz w bazie pięć rekordów (1, 2, 3, 5, 6). W takiej sytuacji mt_rand może wylosować Ci liczbę 4, co z wiadomej przyczyny nie zadziała.

[Speedy]

Możesz zrobić tak:

[SQL] pobierz, plaintext 
SELECT id
FROM tabela
WHERE (tabela.id = (SELECT FLOOR(1 + (RAND() * ((SELECT COUNT(id) FROM tabela) - 1) ))))
LIMIT 1;
[SQL] pobierz, plaintext 

To zapytanie wylosuje jedną liczbę od 1 do N, gdzie N, to liczba rekordów w Twojej tabeli a następnie zwróci rekord o podanym ID.
To rozwiązanie ma tą wadę, że jeśli rekordy są niespójne (nie ma ciągłości identyfikatorów ID) i część z nich jest usunięta, to wtedy zapytanie czasem zwróci pusty wynik (NULL).
Można to spróbować jakoś rozwiązać w SQL-u, albo pójść na łatwiznę i w aplikacji webowej możesz zrobić pętlę do ... while, w której wykonasz to zapytanie raz jeszcze, gdy zwróci wartość NULL.
Jeśli nieciągłość w identyfikatorach ID jest mała, rozwiązanie powinno się sprawdzić. Jeśli natomiast rozbieżność jest duża, może to działać bardzo wolno.
Tak czy inaczej, powyższe zapytanie działa szybciej, niż ... ORDER BY RAND() choć jest mniej stabilne.

Ten post edytował Speedy 18.06.2011, 14:55:28

[#luq]

Co Wy z tym WHERE id = xxx?

Przecież można:

[SQL] pobierz, plaintext 
SELECT row FROM TABLE LIMIT $rand, 1
[SQL] pobierz, plaintext 

@lysy2005 a masz na polu z którego robisz warunek dla COUNT zrobiony index?

[lysy2005]

@lysy2005 a masz na polu z którego robisz warunek dla COUNT zrobiony index?

Oczywiście, ale zapytanie miele i miele bo są cały czas dodawane nowe rekordy,
zrobiłem tymczasowo że pobieram 1000 rekordów z warunkiem i z nim w phpie losuje randem.

Sposób wskazany wyżej "count" nie zadziała bo dane nie są usegregowane po kolei, Id wygląda rak: 1,100,145,1111,456 itp,
ponieważ czasami właśnie rekordy nie spełniające warunków są usuwane.

Rozbudowałem troszkę skrypt i już dodaje 1GB na godzinę do bazy, baza ma 20GB wszystko zoptymalizowałem został ten rand nieszczęsny:(

Ale 1GB danych na godzinę to trochę mało dla mnie, bo całość ma mieć 500GB...
W takim tępię 20 dni by się robiło ;/

PS. może ktoś podpowie z konfiguracją my.cnf
Serwer 6GB ramu ~9 GHz
aktualnie mam zajęte 95% ramu.

Wycinek najważniejszych ustawień:
key_buffer = 80M
max_allowed_packet = 80M
thread_stack = 40M
thread_cache_size = 6
query_cache_limit = 40M
query_cache_size = 400M

System Ubuntu 10, 64bit
Baza jest w InnoDB
Mysql Wersja serwera: 5.1.49-1ubuntu8.1

Z góry dzięki za pomoc!

[aachi]

Może rozbij tablice na kilkanaście mniejszych. Nie wiem jakie dane przechowujesz, ale np tablica1 mogłaby zawierać tylko wiersze z wartością pole 1 (albo 1 do 100,000, jeśli pole może przyjmować wartości od 1 do miliona).

[Speedy]

Co Wy z tym WHERE id = xxx?

Przecież można:

[SQL] pobierz, plaintext 
SELECT row FROM TABLE LIMIT $rand, 1
[SQL] pobierz, plaintext 

@lysy2005 a masz na polu z którego robisz warunek dla COUNT zrobiony index?

Można tak, ale tego nie zrobisz w samym MySQL-u, ponieważ w klauzuli LIMIT nie można stosować zmiennych.

Wymyśliłem inny sposób na rozwiązanie tego problemu, który jest pewną wariacją mojego poprzedniego pomysłu:

[SQL] pobierz, plaintext 
SELECT id
FROM tabela
WHERE (tabela.id >= (SELECT FLOOR(1 + (RAND() * ((SELECT COUNT(id) FROM tabela) - 1) ))))
LIMIT 1;
[SQL] pobierz, plaintext 

Zamiast dokładnego porównania ID do losowej wartości, wyszukujemy rekordy, które są większe lub równe, niż losowa wartość od 1 do N, gdzie N, to liczba wszystkich rekordów.
Można też zmienić operator na znak mniejszości, ale wtedy zapytanie zawsze zwróci pierwszy wynik z tabeli, więc lepiej jest zrobić tak, jak napisałem wyżej.
Wtedy nie musimy znajdować rekordu, który ma identyczny ID, jak losowy, ale znajdujemy więcej rekordów z danego zakresu i wybieramy pierwszy z nich. W takiej sytuacji zawsze wylosujemy jakiś rekord i zapytanie nigdy nie powinno zwrócić pustego wyniku.

[aachi]

Panowie, ale pamiętacie, że autor nie potrzebuje losowego wiersza z tabeli, tylko losowego wiersza spełniającego jakiśtam warunek?

[Fifi209]

Fajnie jakbyś pokazał strukturę tej tabeli lub nawet tabel o ile są powiązane. Może da się to jakoś rozbić?

Swoją drogą co Ty przechowujesz w tej bazie, że w ciągu godziny o takie rozmiary się powiększa?

[Speedy]

Panowie, ale pamiętacie, że autor nie potrzebuje losowego wiersza z tabeli, tylko losowego wiersza spełniającego jakiśtam warunek?

Tylko nie wiadomo, co to za warunek, więc teraz mógłbym jedynie zgadywać (musiałbym mieć więcej szczegółów), ale rzeczywiście przy odpowiednim warunku moje ostatnie rozwiązanie może nie zadziałać zawsze.
Jeżeli pominiemy ten warunek, rozwiązanie działa.

[vokiel]

Wybaczcie link do bloga, ale kiedyś o tym pisałem, przepisywanie z bloga nie ma sensu: MySQL RAND() – jak pobrać losowe wiersze

[aachi]

Wybaczcie link do bloga, ale kiedyś o tym pisałem, przepisywanie z bloga nie ma sensu: MySQL RAND() ? jak pobrać losowe wiersze

Z tego co rozumiem proponujesz autorowi tworzenie tymczasowej tabeli zawierającej wszystkie id wierszy spełniających określony warunek WHERE? A następnie losowanie z tej tymczasowej tabeli id wiersza? I to wiedząc, że w ciągu godziny baza przybiera o 1GB?
Czy może masz na myśli jakiś inny sposób podany na blogu?

Ten post edytował aachi 19.06.2011, 21:16:08

[by_ikar]

Dobrym rozwiązaniem byłoby zastosowanie cron'a, który powiedzmy co 10min mieliłby nawet bardzo skomplikowane zapytanie, z losowymi rekordami, ale większą ilością, powiedzmy 10k czy tam nawet 100k i zapisywał ich nie wiem, id, czy nawet całe wiersze, do pliku i potem odczytywać te 10k losowych rekordów i z tych 10k losowych rekordów wyciągać powiedzmy 20 losowych przy każdym requeście, które nam są potrzebne. Jakaś tam losowość pozostaje. Mysql akurat w przypadku tak dużej ilości danych nie jest dobrym rozwiązaniem. Do przechowywania dużej ilości danych dobre są bazy typu nosql, które lepiej sobie radzą z tak dużą ilością danych. Nie wiem jak mysql zachowa się powiedzmy przy 500gb danych. Radzę się zastanowić nad zmianą bazy.

[uupah5]

coraz ciekawsze pomysły:
rozbijanie tabeli na wiele z powodu jednego zapytania? a reszta logiki aplikacji sama się dostosuje?
20GB to za dużo dla mysql?

Autorze, moim skromnym zdaniem problem jest rozwiązywalny bez zmiany bazy
Rzuć więcej info o samej bazie/tabeli/zapytaniach. opicy tego co robisz za pomocą php możesz sobie darować

@all: no offence;)

[thek]

Ja osobiście kombinowałbym także pod kątem:
Zlicz ilość rekordów spełniających warunek,
Wybierz liczbę od 0 do ilość-1
Zapytanie z WHERE oraz LIMIT wylosowana_liczba, 1.

Omija się problem nieciągłości. Problemem jest jedynie szybka modyfikacja danych bazy, ale można to ominąć choćby funkcjami.

[by_ikar]

coraz ciekawsze pomysły:
rozbijanie tabeli na wiele z powodu jednego zapytania? a reszta logiki aplikacji sama się dostosuje?
20GB to za dużo dla mysql?

Autorze, moim skromnym zdaniem problem jest rozwiązywalny bez zmiany bazy
Rzuć więcej info o samej bazie/tabeli/zapytaniach. opicy tego co robisz za pomocą php możesz sobie darować

@all: no offence;)

20gb na mysql to nie jest za dużo, ale do składowania dużej ilości danych, używa się wyspecjalizowanych w tym kierunku baz danych, które radzą sobie o niebo lepiej, od średniego mysqla.