DevProTalk - random select na velikoj tabeli

DevProTalk (http://www.devprotalk.com/index.php)

- SQL baze podataka - Sponzor: Baze-Podataka.net (http://www.devprotalk.com/forumdisplay.php?f=10)

- - random select na velikoj tabeli (http://www.devprotalk.com/showthread.php?t=5061)

random select na velikoj tabeli

problem:
imam tabelu (~2M unosa), primarni kljuch je polje id, medjutim ono nije uniformno rasporedjeno, tj postoje veliki blokovi obrisanih unosa, naprimer id moze da ide:
1
2
3
434
454
455
456
900
901
.
.
.
i tako, e sad meni treba query koji ce na najbrzi nacin da mi vrati recimo 2000 random row-ova iz tablele (tih 2000 moraju biti unikatni)

any idea? (mySQL)

P.S.
znam za
ORDER BY RAND()
LIMIT XYZ
ali je to jako sporo, i ima tendenciju da ubije cpu :)

mozes da smanjis broj recorda koji se sortiraju tako sto ces da stavis:
"WHERE id BETWEEN ". $rand ." AND ". ($rand+10000) ."
ORDER BY RAND()
LIMIT XYZ"

ako 10000 nije dovoljno stavis vecu granicu

hmm...

to bi definitivno smanjilo vreme, i olaksalo stvari, ali bi onda favorizovalo unose iz tih 10k, sto na 2M unosa nije naivno..
a ako povecam na recimo 100k opet ce trajati mnogo taj query

evo sad googlam, i izgleda da uopste nema lakog resenja za ovaj problem.. :(

jel ima neko neku ideju, ili sam osudjen na dodatno polje sa sekvencom koja ce se updejtovati na svaki insert i delete :1027:

http://akinas.com/pages/en/blog/mysql_random_row/
Valjda Solution 3 na toj strani pokriva i tvoj slucaj...

@zira: ovaj primer dohvat samo jedan rekord

@kodi: mozes da umesto jednog opsega od 10000 rekorda dohvatis par manjih (5-6 recimo, ili koliko mislis da ti treba), pa da ih unijom spojis, pa onda na celom rezultatu uradis ORDER BY rand()

u sustini, ovde ti je pitanje jel ti treba stroga uniformna raspodela verovatnoce, ili ti treba brzina, treba naci neki najbolji odnos..

Citat:

u sustini, ovde ti je pitanje jel ti treba stroga uniformna raspodela verovatnoce, ili ti treba brzina, treba naci neki najbolji odnos..

:/

trabaju mi oba :)

odustao sam od uniformne raspodele, i posto ti blokovi obrisanih podataka nisu toliko veliki uradio sam varijantu da prvo u phpu generishem niz sa offsetima a onda fetchujem sa where id >=$offset, radi prilichno brzo....

Off Topic:
btw, radi se o gomili keyworda, cije rezultate sa googla treba da updatujem i da sve bude mladje od 10 dana, u medjuvremenu je dataset bash narastao, tako da smo preshli na model gde se na svakihsat vremena updejtuje deo sadrzaja, a onda na svakih 10 dana pokrene skripta koja pokupi sve sto je po timestamp-u starije od 10dana, obichno ostane manje od procenat onoga sto se ovakvom metodom promashi, a generalno podaci budu mnogo sveziji, samo treba nashtelovati koliku porciju da uzima pri svakom prolazu i koliko chesto to da ide

eto mozda nekome bude korisno...

evo dodjosmo do ovoga

PHP kôd:


		
			
SELECT FLOOR(1 + (RAND() * (SELECT COUNT(*) from table_name))) from table_name limit 4;

primer je testiran na tabeli sa oko 1.200.000 recorda sa limitom 4

nisam siguran da razumem najbolje ovo, ovo mi vrati samo xyz id-jeva.. opet bih morao da radim fetch za svaki od njih, a i cini mi se da je moguce da mi vrati id koji je recimo izbrisan (?) ili greshim ?

A mozda moze da se naprosto izgenerise jedno 4000 random brojeva (lepa okrugla cifra), i onda uradis:

SELECT * FROM tabela
WHERE id IN ($gomila_rnd_brojeva)
ORDER BY RAND()
LIMIT 2000;

Ako ti se ne vrati dovoljno rezultata uradis naprosto ponovo upit sa korigovanim limitom, i tako sve dok ne skupis dovoljno...

Nadam se da ovaj kôd govori sve sam od sebe i da ne moram komentarisati nista :D

Kôd:

use test;

create table keywords (

  id int unsigned not null auto_increment,

  keyword varchar(32) not null,

  url varchar(255),

  primary key(id)

);

DELIMITER $$

DROP PROCEDURE IF EXISTS test.unesi_podatke $$

CREATE PROCEDURE test.unesi_podatke(IN koliko INT)

BEGIN

DECLARE brojac INT DEFAULT 1;

while brojac <= koliko do

INSERT INTO keywords (id, keyword, url)

VALUES(NULL, CONCAT('keyword_', brojac), CONCAT('http://www.url', brojac, '.com'));

set brojac = brojac + 1;

end while;

END $$

DELIMITER ;



mysql> create table keywords (

    ->   id int unsigned not null auto_increment,

    ->   keyword varchar(32) not null,

    ->   url varchar(255),

    ->   primary key(id)

    -> );

Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER $$

mysql> DROP PROCEDURE IF EXISTS test.unesi_podatke $$

Query OK, 0 rows affected (0.03 sec)

mysql> CREATE PROCEDURE test.unesi_podatke(IN koliko INT)

    -> BEGIN

    -> DECLARE brojac INT DEFAULT 1;

    -> while brojac <= koliko do

    -> INSERT INTO keywords (id, keyword, url)

    -> VALUES(NULL, CONCAT('keyword_', brojac), CONCAT('http://www.url', brojac,

 '.com'));

    -> set brojac = brojac + 1;

    -> end while;

    -> END $$

Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> call test.unesi_podatke(2000000);

Query OK, 1 row affected (1 min 31.95 sec)

mysql> select count(*) from keywords;

+----------+

| count(*) |

+----------+

|  2000000 |

+----------+

1 row in set (0.00 sec)

mysql> create index indeks_id on keywords(id);

Query OK, 2000000 rows affected (15.28 sec)

Records: 2000000  Duplicates: 0  Warnings: 0

mysql> DELETE FROM keywords WHERE mod(id, 7) = 0;

Query OK, 285714 rows affected (6.06 sec)

mysql>

mysql> select count(*) from keywords;

+----------+

| count(*) |

+----------+

|  1714286 |

+----------+

1 row in set (0.00 sec)

 

mysql> select * from keywords

    -> order by rand()

    -> limit 2000;

2000 rows in set (1 min 43.15 sec)



mysql> SELECT *

    -> FROM keywords k

    -> JOIN (SELECT FLOOR( MAX(ID) * RAND()) AS Rand_ID FROM keywords) AS x

    -> ON k.id >= x.Rand_ID

    -> limit 2000;

2000 rows in set (0.63 sec)

Posto je RAND() prilicno spor, mozes upit iz PHP-a proslijediti i drugacije:

Kôd:

$sql = "SELECT * FROM keywords k JOIN (SELECT FLOOR( MAX(ID) * ".lcg_value().") AS Rand_ID FROM keywords) AS x ON k.id >= x.Rand_ID limit 2000";

Ukoliko takav upit proslijedis MySQL-u, mozes dobiti npr. ovakav rezultat:

Kôd:

mysql> SELECT *

    ->  FROM keywords k

    ->  JOIN (SELECT FLOOR( MAX(ID) * 0.234) AS Rand_ID FROM keywords) AS x

    ->  ON k.id >= x.Rand_ID

    ->  limit 2000;

2000 rows in set (0.02 sec)

Nema na cemu.