MariaDB – Bulk Inserts using Store Procudere

MariaDB [partition_db]> DROP TABLE IF EXISTS mytable;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [partition_db]> CREATE TABLE mytable (id int(10), pin int(10), dt datetime) PARTITION BY HASH ( SECOND(dt) );
Query OK, 0 rows affected (0.03 sec)

MariaDB [partition_db]>
MariaDB [partition_db]> DROP PROCEDURE IF EXISTS forLoop;
Query OK, 0 rows affected (0.00 sec)

MariaDB [partition_db]>
MariaDB [partition_db]> DELIMITER //
MariaDB [partition_db]>
MariaDB [partition_db]> CREATE PROCEDURE forLoop()
-> BEGIN
-> DECLARE i INT DEFAULT 1;
-> WHILE i <= 10000 DO
-> INSERT INTO `mytable` (id, pin, dt) values (i, 50000+i, NOW());
-> SET i = i+1;
-> END WHILE;
-> END;
-> //
Query OK, 0 rows affected (0.01 sec)

MariaDB [partition_db]>
MariaDB [partition_db]> DELIMITER ;
MariaDB [partition_db]>
MariaDB [partition_db]> CALL forLoop();
Query OK, 1 row affected (35.02 sec)

MariaDB [partition_db]>
MariaDB [partition_db]>
MariaDB [partition_db]> SELECT COUNT(*) FROM mytable;
+———-+
| COUNT(*) |
+———-+
| 10000 |
+———-+
1 row in set (0.01 sec)

MariaDB [partition_db]> select * from mytable limit 5;
+——+——-+———————+
| id | pin | dt |
+——+——-+———————+
| 1 | 50001 | 2017-01-23 07:06:16 |
| 2 | 50002 | 2017-01-23 07:06:16 |
| 3 | 50003 | 2017-01-23 07:06:16 |
| 4 | 50004 | 2017-01-23 07:06:16 |
| 5 | 50005 | 2017-01-23 07:06:16 |
+——+——-+———————+
5 rows in set (0.00 sec)

MariaDB [partition_db]> SHOW CREATE TABLE mytable\G
*************************** 1. row ***************************
Table: mytable
Create Table: CREATE TABLE `mytable` (
`id` int(10) DEFAULT NULL,
`pin` int(10) DEFAULT NULL,
`dt` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH ( SECOND(dt)) */
1 row in set (0.00 sec)

MariaDB [partition_db]>
MariaDB [partition_db]>
MariaDB [partition_db]>