Update databáze náhodný řádek

$this->db->table('table_name')
    ->where('status', 0)
    ->where('userId', null)
    ->order('RAND()')
    ->limit(1)
    ->update([
        'status' => 1,
        'userId' => $userId,
    ]);

Jen doporučuju dodržovat v rámci databáze jmenné konvence a pojmenovat sloupec ‚user_id‘

Editoval Polki (30. 8. 2021 17:13)

@DavidGrudl
Prosím napiš efektivní. Já totiž myslel, že takto je to správně. Udělá to 1 jediný dotaz, vybere na základě hodnot, které potřebujeme a taktéž to upraví jen ty hodnoty, které potřebujeme.

Jediný problém vidím v řazení a zahození zbytku, když chci jen 1 prvek.

Vzhledem k tomu, že ale snad ve všech článcích se vybírá náhodný řádek pomocí rand a učí to tak i na školách mi přijde, že to je v pohodě.

Nebo myslíš, že by bylo lepší dělat 2 dotazy, jeden na všechny řádky vyhovující podmínkám, potom pomocí random funkce vygenerovat index některého z řádků a nad tím zavolat update? To mi zase při větších tabulkách sežere ramku…

Jediné co mě napadá je při insertu nového prvku vygenerovat náhodné číslo a podle toho to pak řadit.. Aby se random negeneroval.

EDIT 1:
Ještě mě napadlo něco jako:

$query = $this->db->table('tableName')
    ->where('status', 0)
    ->where('user_id', null);

$offset = $query
    ->count('id');


$aaa = $query
    ->limit(1, rand(0, $offset))
    ->fetch();

$aaa->update([
    'status' => 1,
    'user_id' => 2,
]);

Ale rozdíl mezi prvním příspěvkem (13155ms) a těmito dotazy (11524ms) není tak markantní, aby se tím člověk moc zaobíral si myslím.
(testován na 30 milionech záznamů a tabulkou se 30 sloupci – číslo 30 je super :D)

Editoval Polki (30. 8. 2021 21:56)

David Grudl napsal(a):

Což je zároveň nejméně efektivní řešení, ale u malých tabulek to nevadí.

Na tohle je @Milo strašně háklivej :)
Ale opravdu by stálo za to to rozvést, protože pokud jsou na status a user_id indexy, tak marně přemýšlím, jak to udělat efektivněji.

Spíš bych řekl, že (ne)efektivita bude závislá na počtu status=0, user_id=null. Pokud takových vět bude velké množství, asi bych bokem dělal rand() z jejich id a do sql už jen na konkrétní větu.
Pokud se vybírá z několika málo záznamů, tak by mně efektivnější řešení také zajímalo.

Souvislost s velikostí tabulky tam ale nevidím.

ORDER BY RAND() LIMIT 1 netřídí celou tabulku, ale jen resultset ovlivněný WHERE.
A to je to zásadní, co může query pohřbít, nebo učinit legitimní.

Pokud to WHERE např. pomocí indexu stáhne na 5 vět, udělat z nich RAND bude chvilka, ačkoliv tam bude stále filesort.

@KamilValenta
Pravdu máš. Moje testy, jak jsem psal výše běžely ještě nad MariaDB 10.4.10 a schválně jsem všech 30M záznamů měl nastavených, aby splňovaly podmínku where.

A 1.5 s rozdíl mi přišel, že to musí mít optimalizované a netřídí tedy celý výsledek wheru, ale setřídí jen tolik záznamů, kolik je napsáno v limit.

Nepřijde mi totiž, že celé řazení podle náhody by pro 30M prvků zabralo jen 1.5s

Mě to sice smysl dává, ale do core MariaDB nevidím.

Každopádně vygenerovat 30M náhodných hodnot a seřadit podle nich 30M záznamů mi přijde složitější, než na 1,5s

EDIT 1:
Stroj, na kterém to je pouštěno:
Windows 10
16GB RAM DDR4
Intel Core i7–9750H, 2.60GHz

Editoval Polki (31. 8. 2021 21:10)

No myslel jsem, že pokud mám nad sloupcem index, tak má databáze uložený daný sloupec ještě v binárním vyhledávacím stromě, což zajistí vyhledání daného prvku logaritmickou složitostí, díky čemuž, když budu chtít nejlevnějších N prvků, tak vlastně order nad sloupcem ‚price‘ jen určí směr průchodu stromem a žádný sort se dělat nemusí, protože z podstaty stromu je už seřazeno, jen se díky limitu neprojde celý strom, ale ve tvém dotazu se sebere jen první nejlevější prvek..

Stejně by to mohlo fičet i s randem, který by mohl fungovat třeba tak, že by vzal strom s ID a procházel by jej náhodně (šel by buď vlevo, nebo vpravo), dokud by se nedostal k listu, který by vložil do nového stromu a v momentě, kdy by bylo ve stromě tolik prvků, kolik je uvedeno v limit, tak by mohl s random vyhledáváním skončit. (nebo aby pořád nevyhledával, tak by mohl generovat náhodné indexy do pole s ID třeba, čím by u vyhledávání shodil složitost z O(log(n)) na O(k)…)

To, že to tak není je pro mě docela rozčarování :D No co. O databázi jako takovou jsem se nikdy dopodrobna nezajímal. Myslel jsem, že to mají schválně optimalizované, aby to ten, kdo ji používá nemusel řešit. Co už :D

PS: stejně tak jsem myslel, že count prostě vrátí konstantní číslo, kolik prvků má daný podstrom vrácený clausulí where. Taky to pro mě byl docela šok, že si toto db neuchovává a pokaždé počítá, kolik je v daném podstromě prvků a pro 30M záznamů trvá spočítat všechny podle ID 5.5s

EDIT 1:
Musí to mít nějaké optimalizace, nebo DB neumí počítat, protože EXPLAIN jak píšeš mi vyhodil, že sahá na 11336942 řádků a v db je jich 33240016

Editoval Polki (31. 8. 2021 22:00)

David Grudl napsal(a):

Databáze nejprve udělá WHERE, potom ORDER BY a nakonec LIMIT. Takže ORDER BY řadí všechny vybrané řádky. A nelze k tomu využít index, protože se neřadí podle sloupce, ale podle funkce RAND(), ke které žádný index není.

Jasně, tomu rozumím. Jen mi to přijde docela škoda.