Nadam se da ovaj kôd govori sve sam od sebe i da ne moram komentarisati nista
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.