MySql ORDER BY in paginacija - optimizacija

5 naročnikov

Imam situacijo, ko imam v bazi več kot 200.000 zadetkov, ki ustrezajo kriterijem. Torej SELECT id FROM karnekej WHERE karnekej2>100 AND ... bi vrnilo 200.000 zadetkov.

Problem nastane ker se ti queriji izvajajo zelo pogosto. V headerju bi rad prikazoval na random 10 rezultatov na vsak prikaz strani.

LIMIT 10 sicer izjemno skrajša čas, toda ko se temu doda ORDER BY rand() vzame skoraj isto časa kot če bi zahteval vse. SELECT MAX(id)... je enako potraten pa niti ne pomaga kaj dosti. Če pa dam samo LIMIT 10, se mi bodo prikazovali vedno isti prvi rezultati, tisti zadnji pa nikoli. Ima kdo kako idejo kako rešit zadevo?

Drugi primer pa je isti, le da moram k temu pa moram potem dodati še paginacijo kar še dodatno zakomplicira zadevo. Tudi tu dobrodošla kak ideja, bom pa vesel že če zna kdo odgovorit na prvi primer.

Hvala!

Moje mnenje je, da načeloma random ni treba narediti ravno RANDOM.
Kaj bi jaz naredil v tem primeru:
1. Iz baze bi izmed vseh na random (se pravi z rand() ) vzel cca. 100-300 zadetkov.
2. To bi shranil v cache
3. na random izbereš 10 zadetkov in jih pokažeš

TTL daš odvisno od podatkov.

Sedaj če je to neka internet stran pomoje preden uporabnik dojame, da random deluje na 100 zapisih in ne na 200k zapisih pri tem ko prikazuješ 10 podatkov je zlo majhna. Če pa že opazi se mu pa zdi, da je imel pač srečo in je random tako zbral :D

Random:
Iz baze v x spremenljivko shraniš vse id-je. Potem pa v zanki generiraš random število z. Če je z v x arrayu (pomeni da zapis v bazi obstaja) in z ni v y arrayu (pomeni, da se ne ponovi), jo shraniš v y array. V nasprotnem primeru zmanjšaš števec (tako, da v vsakem primeru dobiš točno 10 random števil). Na koncu ustvariš query, ki iz baze selecta števila v y arrayu. To je za moje pojme najboljša rešitev pri ogromno zapisih. Upam, da sem napisal dovolj razumljivo.

Random paginacija mislim, da ni dobra rešitev za to. Ker bi v tvojem primeru za moj način "nekam" moral shranit 20.000 različnih arrayov, da se zapisi ne bi ponavljali. Najbolje bi bilo, da paginacijo narediš z order by id limit x,y.

Mogoče tole pomaga:

$offset_result = mysql_query( " SELECT FLOOR(RAND() * COUNT(*)) AS `offset` FROM `table` ");
$offset_row = mysql_fetch_object( $offset_result );
$offset = $offset_row->offset;
$result = mysql_query( " SELECT * FROM `table` LIMIT $offset, 1 " );

pa še članek, kjer sem to izluščil :) KLIK

1. RAND() je na večjih datasetih zelo počasen zato, če se le, da se ga v takšnih primerih izogibaj (indekse domnevam, da imaš urejene).

2. Predlog od blackmamba pa bo se še najverjetneje izvajal počasneje od tega, kar imaš sedaj.

RAND() je že tako počasen, LIMIT nekovelikoštevilo, 1 pa je na večjih datasetih v večini primerov tudi zelo počasen.

Če se ti SELECT MAX(id) .... LIMIT 1 izvaja enako počasi, potem sem sam skoraj brez kakšnih drugih konkretnih predlogov.

Sicer vseeno menim, da se ti ta stavek ne bi smel izvajati skoraj enako dolgo in bi ta rešitev morala delovati v redu (torej dobiš max id, nato programsko naključno generiraš toliko različnih idjev, kot jih pač potrebuješ, na koncu pa SELECT-aš vrstice s temi ID-ji iz baze).

Ena izmed "ne ravno lepih" rešitev, ki se jo še trenutno spomnim pa je ta, da bi v tabeli dodal še eno polje, kjer bi vedno ob vnosu vpisal naključno vrednost, potem bi namesto RAND() sortiral po tem stolpcu.

Seveda bi ta stolpec moral biti indeksiran, rabil bi pa še neko skripto, ki bi na primer vsako noč šla čez vse vrstice v tej tabeli in bi posodobila naključne vrednosti v tem novem stolpcu.

Testirano na 140.000 zapisih:

ORDER BY RAND() LIMIT 10

~ 1.8 sekunde

Spodnji primer pa 0.28 sekunde.

SELECT @bla :=1 / count(*) * 10
FROM tabela;

SELECT * FROM tabela
WHERE rand( ) < @bla
ORDER BY rand( )
LIMIT 10 ;

Hvala za vse ideje! Sem se odločil da je res nesmiselno prikazovati random vseh x00.000 zadetkov, tako da bom uporabil LIMIT 5000 in while loop.

...
$rnd=rand(1,5000);
while($row=mysql_fetch_assoc($result)){

  if($rnd==$counter){
    echo $row[whom];
  }
  $counter++;
}

Celoten proces brez while loop vzame 0.078 s, z while pa 0.094s (testirano z ab.exe). Sem mislil da sta while in mysqlfetchassoc bolj potratna.