Zna ktoś jakiś szybki sposób na wybranie 12 losowych rekordów z tabeli, która może mieć nawet kilka milionów wierszy? Do tego biorąc pod uwagę, że będzie bardzo często odczytywana.

Najpierw próbowałem tego:
http://blog.desmart.com/2008/03/12/sposob-...-tablicy-mysql/
ale losuje mi 12 rekordów, tyle, że następujących po sobie jeden po drugim, czyli tak naprawdę losuje miejsce startowe, skąd ma pobrać kolejne rekordy. Nie umiem tego przerobić tak by wybrało 12 zupełnie przypadkowych rekordów.

Pomyślałem więc, że wylosuję w PHP ok. 50 liczb, i używając ich zadam zapytanie:

[SQL] pobierz, plaintext 
SELECT * FROM tabela WHERE pole_id IN (liczba1,liczba2...)
[SQL] pobierz, plaintext 

Ale to mi nie daje gwarancji, że wybierze dokładnie 12 rekordów, bo przecież niektóre o podanych ID mogą nie istnieć. W sumie mogę tak wybierać rekordy aż uzyskam dokładnie 12 (bo to mała liczba) ale może zna ktoś jakiś lepszy sposób?

@luki100011 zanim udzielisz jakze pomocnej odpowiedzi, przeczytaj najpierw co autor napisał...

W linku co podal metoda ta też jest podana. Jest też podane tam rownież dlaczego nalezy jej nie uzywac. Przeczytaj, wiedza ta moze ci sie przyda w przyszlosci

Może więc w PHP losuj 12 + X liczb, gdzie X to odpowiedni "zapas" jeśli danego artykuły by nie było. Następnie wybierasz rekordy z warunkiem IN (wylosowane_liczby) LIMIT 12. Jeśli dobierzesz odpowiednią wartość X (odpowiednio duża jeśli jest duże prawdopodobieństwo trafienia na "dziurę") to będziesz miał naprawdę pecha jeśli zapytanie nie zwróci Ci faktycznie 12 rekordów.

http://www.slideshare.net/billkarwin/sql-a...rns-strike-back

Od slajdu 141. Solution #4 całkiem sprytne.

@Mchl Twoje rozwiązanie również pobiera 1 rekord.

@Mchl:
Zgadza się, jest to dość sprytne rozwiązanie, ale w tym przypadku chyba nie do końca przypadnie autorowi do gustu. Jego minusem będzie to, że wybierze rekordy po kolei od zadanego (wylosowanego) punktu startowego.

@nospor:
Można przecież nadać ograniczenie, że liczba rekordów = rzeczywista liczba rekordów - limit i wtedy można pobrać X "losowych" rekordów.

Jak zostosuje je dosłownie w takiej postaci jak przedstawiona to na pewno.
Ale kto mu broni wylosować 12 offsetów i złożyć przez UNION zapytanie 12 SELECTów?

No tak, ale dochodzi tutaj kwestia liczby wykonanych zapytań oraz możliwość powstawania powtórzeń w wylosowanych rekordach.

Ba.... nikt mu nie zabroni. Ale to mogl zrobic juz na samym początku przy rozwiązaniu ktore sam podał. A weź pobierz 12 losowych rekordów bez union i bez 12 zapytan....

> 2000000



2.1342s


0.3164s

No i czemu te zestawienie ma sluzyc? Przeciez to oczywiste. Ba... nawet jest podane w pierwszym linku w pierwszym poscie. Jak to ma sie do 12 losowych rekordów bez UNION i bez 12 zapytan?

Ale dlaczego bez UNION? Skąd taki wymóg?

oj... zmieniasz te fronty.. tu mi jakies wyniki, tu jakies zapytania i za kazdym razem unikasz odpowiedzi na pytanie

zaden wymog. Wyjasniam ci jedynie ze UNION to on juz mogl uzyc na samym poczatku przy zapytaniu co podal. Zapytal sie jednak jak mozna to zrobic inaczej, a ty w odpowiedzi podales swoje zapytanie, inne, które tez pobiera jeden rekord.

Swoją drogą: czy przypadkiem rozwiązanie z IN (1, 2, 3) nie będzie przypadkiem szybsze? Przyznam, że wynik dla drugiego zapytania (0.3164s) jakoś szczególnie mnie nie powalił swą szybkością. Czy mógłbyś wykonać zapytanie z kilkoma identyfikatorami w IN?

@phpion ale przeciez dla IN musisz podac np. konkretne ID. A o dziurach nie slyszal?

ps: tak, czytalem twoje poprzednie posty o prawdopodobienstwu - nie podoba mi sie ta teoria

No mi generalnie również nie do końca bo zawsze może się zdarzyć przypadek, gdy zapytanie zwróci 0 rekordów (wylosujemy same "dziury"). Jednak wybieranie rekordów po PK powinno być zdecydowanie szybsze niż wycinanie danego fragmentu ze zbioru - stąd moja prośba do Mchl o wykonanie zapytania i podanie wyniku.

Wszystkie metody opierające się na losowaniu wartości klucza głównego zakłądają, że klucz główny jest ciągły.
Jeśli nie jest, to na przykład zapytanie podane w linku w pierwszym poście, raz na jakiś czas zwróci 0 wierszy. Czy to dopuszczalne? Pewnie jak się ma tego świadomość i dobrze obsłuży to tak.

Jeżeli wywołanie pierwszego zapytania tyle razy żeby uzyskać dokładnie 12 wierszy będzie w 99,99% szybsze niż wywołanie 12 razy rozwiązania podanego przeze mnie, to chyba wiadomo jaki jest wybór.

@phpion: niestety akurat ta tabela nie ma klucza głównego auto_increment, więc trudno byłoby zrobić porównanie. Niewątpliwie jednak, wybieranie po PK będzie szybsze. Pytanie tylko, jak często losując wartość PK trafiamy na wartość rzeczywiście istniejącą.

Może po prostu wykorzystując metodę phpion'a dodać do warunku OR 1=1 i przy LIMIT 12 to by wypełniło dziury, ale faktem jest że rekordy wypełniające dziury będą w większości przypadkach takie same.

wydaje mi się, ze mysql wowczas potraktuje cale wyrazenie jako prawda i poleci pokolei bez patrzenia na IN. Ale mogę sie mylic - mozna sprawdzic.

edit: sprawdzilem - jest tak jak powiedzialem

Nie ma co sprawdzać. Dokładnie tak będzie. Jakikolwiek dodatkowy warunek w alternatywie z wyrażeniem, któe zawsze ma wartość TRUE będzie wyrzucony jeszcze na poziomie optymalizera.

mozna połaczyc dwie metody:
IN
UNION
limit 12 z losowym ofsetem

I jesli IN zawiedzie (nie zwroci 12 losowych rekordów) to dopelni się je kolejnymi rekordami z losowego offsetu

Jeżeli taka 'losowość' wystarcza autorowi pytania, to można. Mnie się to nie bardzo podoba

W dzisiejszym losowaniu multilotka wylosowano 4,7,2,6,43,21,42,10,11,12,13,14...

Sprawdziłem u siebie na bazie.

Rekordów w tabeli: blisko 15mln
Zapytanie z UNIONami: 34.662 ms
Zapytanie z IN: 1.371 ms

Każde zapytanie uruchamiałem 5 razy i dopiero przy piątym uruchomieniu spisywałem wyniki (czasy się ustabilizowały). Co prawda baza to akurat PostgreSQL no ale jakieś odniesienie można mieć. Pozostają jednak problemy z "dziurami".

Ja tylko podalem usprawnienie do metody phpiona. nie pisalem ze jest idealne

Dlatego wybór metody trzeba uzależnić od rzeczywistych danych. Może w tej tabeli dane są tylko dodawane i nigdy nie usuwane, albo usuniętych wartość PK jest nie więcej niż 10%.Wtedy można śmiało wybierać wartości klucza głównego, bo średnio powinno wystarczyć ok 13 losowań, żeby trafić na 12 istniejących.
Z drugiej strony, jeżeli z tabeli dane są usuwane "często i gęsto", wtedy może być lepiej polecieć z offsetem.

czyli zgadzasz się z nim że to zapytanie wylosuje po kolie 12 rekordów a nie losowe

mowisz o order by rand() ?
Nie, to zapytanie zwroci 12 losowych rekordów. Ale on o tym wie, ma to napisane w linku co sam podal. Nie nalezy tej metody jednak uzywac bo jest cholernie nie optymalna i przy duzej ilosci rekordów, jaka tu ma miejsce, szlag wszystko trafi. To mialem na mysli piszac: ". Przeczytaj, wiedza ta moze ci sie przyda w przyszlosci"

On napisał że zwraca mu po kolei 12 rekordów

no tak. zapytanie ktore podal, te ktore mialo LIMIT 1, po wstawieniu tam LIMIT 12, zwraca 12 rekordów nastepujacych po sobie.

@luki100011:
Słowo-klucz zawarte jest w temacie; jest nim szybko, a Twoje zapytanie nie spełnia tego warunku.

Trudno będzie znaleźć coś innego przecież nie pobierzesz całej tabeli (np kolumny id) do tablic aby w php wylosować 12 jej elementów.
Jak nie musi cały czas być to inne 12 elementów np 12 innych co godzinę no to cache i będzie szybko

zrob sobie kilku milionową tabele i zrob na niej order by rand()
To juz lepiej bawic sie w 12 union czy w 12 zapytan niż w to, co ty zaproponowales a co bylo wiadome juz od samego początku bo bylo w linku co podal autor, co podkreslam ci jeszcze raz

Jak przejrzysz ten wątek, to znajdziesz conajmniej trzy szybsze sposoby.

Ja bym proponował:
- użycie IN z odpowiednim zapasem losowych ID, jeśli się zdarzy, że będzie mniej niż 12 rekordów to trudno, niech się wykona jeszcze raz (lepsze pewnie nawet 1,5 zapytania niż 12 unionów)
- zawężenie zakresu wybierania rekordów poprzez between, gdzie parametry dla between są losowane - ale nie wiem jak z wydajnością tego, czy to coś przyśpieszy

A osobiście by zrobił:
- dodatkową tabelę, która przechowuje określony % identyfikatorów głównej tabeli a rekordy w niej są odświeżane co jakiś czas, czas ten oraz % rekordów dobieramy wg potrzeb i uznania

Wątpliwe. Trzebaby sprawdzić OC.



W obu przypadkach ogranicza się 'losowość' wyboru.

a takie cos? http://jan.kneschke.de/projects/mysql/order-by-rand/ nie sprawdzalem ale na oko jakies inne podejscie niz tu proponujecie;)

Dodatkowa tabela w której trzyma się same wartości klucza głównego? Oczywiście można... Nie podobają mi się tylko te triggery, zwłaszcza AFTER DELETE: kasujesz jeden wiersz z początku tabeli, a trigger uaktualnia miliony wierszy w tabeli skojarzonej...

Kto jest chętny sprawdzić jak wydajność ma się do tego co już omówiliśmy? Podejrzewam że w okolicach tego co dla WHERE ID IN (...). Natomiast przy kasowaniu wierszy może być nieciekawie.