Zufällige Datensätze aus Tabelle (MySQL)

[Dave Waterstone]

Hi Leute,
vielleicht hat hier einer eine Idee..

Eine Tabelle mit zur Zeit 1,2 Millionen Datensätze.

Hier raus benötige ich nun x ZUFÄLLIG Datensätze.

ORDER BY RAND() LIMIT x ist ja gut und schön aber sehr unperformant.


Aufgrund von einigen Filltern (unter anderem ob die Datensätze schon bearbeitet wurden) werden 5 Tabellen per LEFT JOIN und 5 per INNER JOIN angehängt.
Dazu noch einige EXISTS...

Aktuell hole ich mir 100 der gefilterten und shuffle die dann per php um hier x stk zu erhalten.
Klappt auch, bis auf die tatsache, das es halt nicht wirklich zufällige sind.

Da die Daten von x Bearbeitern berachtet werden müssen, ist es auch nicht klug hier tmp tabellen für jeden mitarbeiter zu erstellen.

Stehe hier doch etwas auf dem Schlauch...

gruß Dave

[akretschmer]

ich würde dafür TABLESAMPLE nehmen:

postgres=# create table waterstone(id int generated always as identity primary key, data numeric);
CREATE TABLE
postgres=# insert into waterstone (data) select random()*1000000 from generate_series(1,1000000) s;
INSERT 0 1000000

Damit sind nun 1000000 Rows drin. Nun mal welche zufällig holen:

postgres=# select * from waterstone tablesample bernoulli(0.1) limit 10;
  id  |       data       
------+------------------
   69 | 456921.608917247
  703 | 99737.2930239793
 1350 | 87922.5659502365
 2200 | 628460.348083758
 2646 |  27317.554077974
 3719 | 535605.434548526
 4209 | 35852.5607269229
 4725 | 843782.903058144
 5574 | 425851.935380382
 5889 | 992495.890591874
(10 rows)

postgres=# select * from waterstone tablesample bernoulli(0.1) limit 10;
  id  |       data       
------+------------------
 1450 | 433362.411941714
 1685 | 861499.307515202
 2614 | 463834.568549498
 2945 | 45169.1407767532
 3394 | 735515.208741706
 6283 | 939269.805789087
 6301 | 664893.411980268
 7106 | 529200.920312182
 7812 | 415727.228518731
 8955 | 740337.765487854
(10 rows)

postgres=#

tablesample bernoulli(0.1) holt 0,1%, das limitiere ich noch mal auf 10.

Das geht auch flott:

postgres=# explain analyse select * from waterstone tablesample bernoulli(0.1) limit 10;
                                                     QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..62.96 rows=10 width=16) (actual time=0.011..0.109 rows=10 loops=1)
   ->  Sample Scan on waterstone  (cost=0.00..6296.00 rows=1000 width=16) (actual time=0.010..0.107 rows=10 loops=1)
         Sampling: bernoulli ('0.1'::real)
 Planning Time: 0.115 ms
 Execution Time: 0.165 ms
(5 rows)

Vergleich zu ORDER BY RANDOM() LIMIT 10

postgres=# explain analyse select * from waterstone order by random() limit 10;
                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=43786.00..43786.03 rows=10 width=24) (actual time=208.101..208.103 rows=10 loops=1)
   ->  Sort  (cost=43786.00..46286.00 rows=1000000 width=24) (actual time=208.100..208.101 rows=10 loops=1)
         Sort Key: (random())
         Sort Method: top-N heapsort  Memory: 26kB
         ->  Seq Scan on waterstone  (cost=0.00..18786.00 rows=1000000 width=24) (actual time=0.009..91.440 rows=1000000 loops=1)
 Planning Time: 1.090 ms
 Execution Time: 208.127 ms
(7 rows)

[Dave Waterstone]

Hi akretschmer

danke für den schnellen Post. Werde mir das einmal ansehen und testen. Vielleicht hast du mir gerade den Arsch gerettet^^
Gruß

[Dave Waterstone]

Ah ok, postgres.... es handelt sich hier um mysql

[akretschmer]

ja, viele solch coolen Features gibt es wohl nicht in MySQL...

[akretschmer]

was Du vielleicht noch machen könntest:

postgres=# select * from waterstone where random() <= 0.00001;
   id   |       data       
--------+------------------
 140233 | 342566.175455251
 220217 | 120800.271231545
 221302 | 796133.014676852
 400964 | 377324.347753335
 491823 |  592143.88477895
 583688 | 750536.603987446
 738349 | 213088.952364056
 803667 |  146430.09340439
 864142 | 390772.894688607
(9 rows)

postgres=# explain analyse select * from waterstone where random() <= 0.00001;
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Seq Scan on waterstone  (cost=0.00..21286.00 rows=333333 width=16) (actual time=11.921..44.435 rows=6 loops=1)
   Filter: (random() <= '1e-05'::double precision)
   Rows Removed by Filter: 999994
 Planning Time: 0.056 ms
 Execution Time: 44.447 ms
(5 rows)

postgres=#

Ist noch immer ein seq-scan, aber besser als gar nichts ...
Besser wäre natürlich eine bessere DB zu verwenden ...

[erc]

Aufgrund von einigen Filltern (unter anderem ob die Datensätze schon bearbeitet wurden) werden 5 Tabellen per LEFT JOIN und 5 per INNER JOIN angehängt.
Dazu noch einige EXISTS...

Ohne Verständnis für die Daten und Abfrage lässt sich da keine Antwort drauf geben. Ein ORDER BY RAND() LIMTI X macht genau das was man erwartet. 1. filter 2. sortieren bzw. mischen 3. limitieren. Wenn bei 1. umfangreichen intermediate results "entstehen", ist das natürlich Gift für die Performance. Wenn du das performant haben willst, musst du an die Reihnfolge ran und das ggf. auch mehrstufig machen. Das geht aber nur mit Verständnis für die Daten.

Ein Beispiel wie sowas aussehen könnte. Angenommen deine Abfrage sieht derzeit so aus:

SELECT
    *
FROM
    datenhalde INNER JOIN
    tags ON (...) INNER JOIN
    user ON (...)
WHERE
    tags.name = 'foobar'
ORDER BY
    RAND()
LIMIT
    10

In datenhalde sind 10mio Datensätze enthalten. tags.name hat im Schnitt eine selektivität von 5%. Bei dem Query würde also eine intermediate result mit 500.000 Datensätze "entstehen", die sortiert werden müssen.

Das würde sich z.B. so optimieren lassen:

SELECT
    *
FROM
    (
        SELECT
            *
        FROM
            tags INNER JOIN
            datenhalde ON (...)
        WHERE
            tags.name = 'foobar' AND
            RAND() < 0.0001
    ) t INNER JOIN
    user ON (...)
WHERE
    tags.name = 'foobar'
ORDER BY
    RAND()
LIMIT
    10

Bei diesem Query würde im Schnitt 500 Datensätze sortiert werden müssen. Wenn du aber in dem Fall die Selektivität von tags.name = 'foobar' falsch einschätzt, die sich über die Zeit ändert oder nicht kennst, wird das ganze Brühe.
Es gibt noch X andere Varianten wie sich das optimieren lässt. Die kommen aber alle mit Vor- und Nachteilen daher. Was sinnvoll ist hängt von den Zielen, der Datenstruktur und den Daten ab.

Wenn du weitere hilfe brauchst, müsstest du zumindestens mal den Query posten.

PS: vielleicht auch mal schauen ob der Query ansich i.O. (EXPLAIN -> läuft alles über indizies?)
*edit* Wieviel Datensätze bekommst du, wenn du das ORDER BY RAND() LIMIT x weglässt? Wenn das unter 10.000 sind (aus der Luft gegriffene), ist dein Problem nicht das ORDER BY, sondern die JOINs und Filter.

[Soulrider]

Nimm doch einfach OFFSET. Entweder fragst du zuerst die Gesamtanzahl der Daten ab oder speicherst dir die jeweils beim Eintrag neuer Daten irgendwo. Dann machst du dir 2 zufällige Zahlen innerhalb der Anzahl Datensätze mit PHP und fragst diese beiden Datensätze ab mit LIMIT1 OFFSET ^^^Zufallszahl^^^.