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