MySQL Approximate test update time

1: Need: How long does it take for a table of 20 million data to be randomly updated with 20 rows?

DROP TABLE IF EXISTS test20;

　　

CREATE TABLE test20 (id INT auto_increment PRIMARY key,money double,createdtime DATETIME) engine=innodb DEFAULT charset= UTF8;

　　

DROP PROCEDURE IF EXISTS pro_test20; DELIMITER &&create PROCEDURE pro_test20 (in INR int)  BEGIN  DECLARE i int DEFAULT 0;  START TRANSACTION;   While I<inr does    INSERT into test20 (money,createdtime) VALUES (234567891023, ' 2014-06-22 20:18:38 ');    SET i=i+1;   END while;  COMMIT;  END &&  DELIMITER;

Mysql> call pro_test20 (10000000); Query OK, 0 rows affected (4 min 50.37 sec)

　　

Mysql> call pro_test20 (10000000); Query OK, 0 rows affected (8 min 18.97 sec)

　　

#20次INSERT into test20 (money,createdtime) VALUES (234567891023, ' 2013-03-33 20:18:38 ');

Mysql> INSERT into test20 (money,createdtime) VALUES (234567891023, ' 2013-03-03 20:18:38 '); Query OK, 1 row affected, 1 Warning (0.02 sec)

UPDATE test20 SET money=money+10000000,createdtime=now () WHERE createdtime= ' 2013-03-03 20:18:38 ';

mysql> UPDATE test20 SET money=money+10000000,createdtime=now () WHERE ID between 200000 and 200020; Query OK, rows affected (0.01 sec) rows matched:21  changed:21  warnings:0mysql> UPDATE test20 SET Money=mon Ey+10000000,createdtime=now () where ID in ( 36,457,67842,21,324,68,23,12,345,547,56,234,7263,233525,252522,3523,76666,1231,54); Query OK, rows affected (0.05 sec) rows matched:19  changed:19  warnings:0

　　

　　

1: Need: How long does it take for a table of 20 million data to be randomly updated with 1000 rows?

Stored Procedure Test 1: (very inefficient)

DROP PROCEDURE if EXISTS pro_tab1000;drop TABLE if EXISTS tab_test20; CREATE TABLE tab_test20 (id int) engine=innodb DEFAULT charset=utf8;delimiter &&create PROCEDURE pro_tab1000 (in INR int)  BEGIN  DECLARE i int DEFAULT 0;   While I<inr does    INSERT into tab_test20 Select Floor (RAND () * (select MAX (ID) from test20));    SET i=i+1;   END while;  END &&  DELIMITER; Call pro_tab1000 (1000); UPDATE test20 SET money=money+10000000,createdtime=now () WHERE ID in (SELECT DISTINCT ID from tab_test20);

　　

Stored Procedure Test 2:2s

DELIMITER &&drop PROCEDURE IF EXISTS pro_test1000; CREATE PROCEDURE pro_test1000 (in INR int)  BEGIN  DECLARE i int DEFAULT 0;  DECLARE J INT;  Select Floor (RAND () * (SELECT MAX (ID) from test20)) into J;   While I<inr do    UPDATE test20 SET money=money+10000000,createdtime=now () WHERE id = j;    SET i=i+1;   END while;  END &&  DELIMITER;

　　

Low-efficiency statements:

SELECT ID from test20 ORDER by rand () LIMIT 10

　　

mysql> SELECT ID from test20 ORDER by rand () LIMIT 10;+----------+| ID       |+----------+|  1876355 | | 10266755 | | 14746331 | | 17533398 | |  8164806 | |  8438406 | | 12884382 | | 17285257 | |  9817314 | | 10273314 |+----------+10 rows in Set (26.67 sec)

　　

1: Is there a fast index or no index on the field when there are many updates to the record?

MySQL Approximate test update time