random select na velikoj tabeli

[kodi]

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

[ivanhoe]

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

[kodi]

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

[zira]

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

[ivanhoe]

@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..

[kodi]

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...

[conica]

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

[kodi]

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 ?

[ivanhoe]

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...

[Dejan Topalovic]

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

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:

$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:

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.