SQL

Naključen zapis iz MySQL tabele

petek, 23. marec 2007

Kako izbrati naključen zapis iz MySQL tabele? Kar nekaj rešitev pride na misel, pa so dobre? Zadnjih nekaj dni opazujem nekoga, ki se matra s tem, pa mu nikakor ne uspe tako, da si ne bi preobremenil strežnika. Imena ne bom omenjal, če pa kdo ve, na koga mislim, mu pa lahko pove za tale članek.

Torej, kaj nam prvo pade na pamet za rešitev tega problema? Naslednje seveda:

SELECT * FROM tabela ORDER BY RAND() LIMIT 1;

Zadeva sicer deluje, vendar joj, če imamo v tabeli nekaj tisoč zapisov, se stvari zapletejo. Naš strežnik nas kar naenkrat ne mara, saj mora za pridobitev enega zapisa obdelati kar celo tabelo, za to pa potrebuje kar nekaj časa.

Rešitev je povsem preprosta. Vsak zapis v tabeli imamo, če se seveda držimo nekih osnovnih pravil, označen z unikatno ID številko, to polje je pa seveda tudi naš PRIMARY KEY. Najprej poskusimo ugotoviti, katera je največja ID številka v tabeli. Enostavno:

SELECT MAX(id) FROM TABELA;

Gornjo poizvedbo MySQL kar pridno optimizira in pogleda le ID zadnjega zapisa, torej je obdelava enostavna in hitra. Super, kaj pa sedaj? Predpostavimo, da delamo s PHPjem in da smo rezultat gornje poizvedbe shranili v spremenljivko $maxID.

$randomID = rand(1,$maxID);

V spremenljivko $randomID smo shranili neko naključno vrednost med 1 in $maxID, recimo da je to 3245. Samo še naslednji query:

SELECT * FROM tabela WHERE id = 3245;

In imamo svoj naključni zapis. Hmm, ni tako enostavno, kaj pa če smo kakšen zapis kdaj brisali? Mogoče prav izbrana ID številka v tabeli manjka. Če ne, potem je gornja koda čisto pravilna rešitev, drugače pa takole:

SELECT * FROM tabela WHERE id >= 3245 LIMIT 1;

Tale poizvedba tudi uporabi indeks in zato ni nič počasnejša kot prejšnja.

Poskusite ta sistem na kakšni večji tabeli in primerjajte rezultate. Le-te mi tudi lahko pustite v komentarjih. Moji rezultati na tabeli z 19.000 zapisi:

SELECT * FROM tabela ORDER BY RAND() LIMIT 1;

1 row in set (0.26 sec)

SELECT MAX(id) FROM tabela;

1 row in set (0.00 sec)

SELECT * FROM tabela WHERE id >= 7000 LIMIT 1;

1 row in set (0.00 sec)

Ni prav zanemarljiva razlika, kajne?

1.
hoho
23. marec 2007, 17:41

Ja, dokler so IDji lepi od 1 do max stvar uredu. Če pa npr. zbrišeš polovico tabele se pa veselje konča.

Bolj zanesljiva rešitev je dodat polje rand z naključno številko npr. 0.120834 in potem vsakič izvest 'WHERE rand_polje >= '.$rand_stevilka

2.
23. marec 2007, 18:24

Res je hoho, tudi to je zelo dobra rešitev.

Veselje se sicer ne konča, le nekateri zapisi se prikazujejo večkrat kot drugi, pa je potem ves namen naključne funkcije skorajda izničen. Tudi tvoja rešitev ima podoben problem, namreč težko kontroliraš distribucijo teh naključnih vrednosti, pa bi se spet določeni zapisi lahko prikazovali večkrat kot drugi. Statistično bi se kajpak morale naključne vrednosti enakomerno razporediti, ampak nikoli ne veš.

Ena od rešitev bi bila tudi uporaba materialized views, torej neka vmesna tabela, ki ima zaporedne ID številke, je pa vprašanje, koliko je takšno kompliciranje res smotrno.

3.
24. marec 2007, 00:20

Lahko pa recmo locis tale "pomozni_unique_id" od "unique_id" (naredis se en stolpec).
Potem takrat, ko zbrises pol tabele, naredis en garbage collection (torej se enkrat not napises cifre od zacetka do konca v "pomozni_unique_id"), ce si pa res picajzelj mas pa lahko vsako noc ali vsak vikend ali... (pac takrat, ko ni veliko traffica) cistilno akcijo.

4.
joško
13. april 2007, 14:59

kaj pa? SELECT * FROM tabela WHERE id = (SELECT id FROM tabela ORDER BY RAND() LIMT 1) LIMIT 1

5.
13. april 2007, 15:14

joško, kaj natančno si pa s tem pridobil?

6.
Tine
13. april 2007, 16:10

Kaj pa
... LIMIT $var, 1
s tem da je $var rand(x);
s tem da je x število vrstic v bazi

7.
14. april 2007, 10:08

Tine, poskusi to na kakšni večji tabeli, pa primerjaj rezultate. Seveda nam poročaj.