Mysql – losowe rekordy bez użycia order by rand()

W dzisiejszym wpisie postaram się przedstawić kilka porad na temat losowania rekordów z mysql. Jak już się prawdopodobnie zorientowaliście order by rand() działa całkiem nieźle, pod warunkiem że danych jest niewiele. Gdy pojawi się więcej rekordów, to strona zaczyna przymulać, aż w końcu nadchodzi dzień w którym od administratora serwera dostajemy emaila z informacją, że mamy sobie znaleźć inną maszynę.

Przykładowe zapytania dotyczyć będą tabeli tb_search_hits należącej do wordpressowej wtyczki Search Metter. Tabela zawiera 25974 rekordy.

Order by rand()

W chwili obecnej metoda ta pociąga za sobą szereg operacji, które wymagają sporego nakładu pracy ze strony serwera. Mysql tworzy tymczasową tabelę, do której dokłada kolumnę z losowymi liczbami. Następnie rekordy te są sortowane i odsyłany jest ograniczony zestaw danych (o ile wpisaliśmy limit x, w przeciwnym wypadku mysql odeśle wszystkie rekordy). Przy małej tabeli dzieje się to niemal natychmiastowo.

Przykładowe zapytanie:

select term, type from  tb_search_hits ORDER BY RAND() LIMIT 50

# Query_time: 0.043001  Lock_time: 0.000040 Rows_sent: 50  Rows_examined: 51998

Where id >= losowa_liczba

Jednym z rozwiązań jest wprowadzenie ograniczenia na id rekordów. Losowość wcale nie musi oznaczać, że wszystkie rekordy mają być wymieszane. Wystarczy, że pobierzemy kilka kolejnych rekordów z losowego miejsca w tabeli. Najwyżej przy pomocy php wymieszamy je potem ze sobą.

Przykładowe zapytanie:

select term,type from  tb_search_hits where id >=
FLOOR( 1 + RAND( ) * ( SELECT MAX( id ) FROM tb_search_hits ) ) LIMIT 50

Wewnętrzny select pobiera największe id rekordu, a następnie przekazuje tę liczbę do równania, które losuje wartość od 1 do max( id ). Działa to w miarę dobrze (chociaż zawsze jest szansa, że trafi się na koniec tabeli).

# Query_time: 0.000365  Lock_time: 0.000041 Rows_sent: 50  Rows_examined: 1629

JOIN losowa_liczba where tabela.id >= losowa_liczba

Przykładowe zapytanie:

select term, type from  tb_search_hits as r1 JOIN
 (SELECT (RAND() *
 (SELECT MAX(id)
 FROM tb_search_hits)) AS id)
 AS r2
 WHERE r1.id >= r2.id
 ORDER BY r1.id ASC
 LIMIT 50

Tak jak poprzednio bierzemy największe id, przepuszczamy przez funkcję generującą losowe liczby i przekazujemy do głównego zapytania.

Zapytanie nie pojawia się w logach mysql.

Where id >= losowa_liczba order by id

Tak jak we wcześniejszym rozwiązaniu, tylko z dodanym sortowaniem po id.

Przykładowe zapytanie:

select term,type from  tb_search_hits where id >=
FLOOR( 1 + RAND( ) * ( SELECT MAX( id ) FROM tb_search_hits ) ) order by id LIMIT 50

# Query_time: 0.001190  Lock_time: 0.000044 Rows_sent: 50  Rows_examined: 1670

• Tweet
• Pin It