How to fasten MySQL inserts?

MySQLMySQLi Database

<p>You can speed the MySQL insert when you are inserting multiple records at the same time with the help of the following syntax</p><pre class="result notranslate">START TRANSACTION insert into insertDemo(yourColumnName1,yourColumnName2,...N) values(yourValue1,yourValue2,....N),(yourValue1,yourValue2,....N),.......N commit</pre><p>Let us first create a demo table</p><pre class="result notranslate">mysql&gt; create table insertDemo &nbsp; &nbsp;-&gt; ( &nbsp; &nbsp;-&gt; StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, &nbsp; &nbsp;-&gt; StudentName varchar(20), &nbsp; &nbsp;-&gt; StudentAge int &nbsp; &nbsp;-&gt; ); Query OK, 0 rows affected (0.72 sec)</pre><p>Insert multiple records at the same time. The query is as follows &minus;</p><pre class="prettyprint notranslate">mysql&gt; START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql&gt; insert into insertDemo(StudentName,StudentAge) values(&#39;John&#39;,21),(&#39;Carol&#39;,22),(&#39;Bob&#39;,21),(&#39;David&#39;,24), -&gt; (&#39;Maxwell&#39;,25),(&#39;Mike&#39;,22); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql&gt; commit; Query OK, 0 rows affected (0.14 sec</pre><p>Display all records from the table using select statement. The query is as follows &minus;</p><pre class="prettyprint notranslate">mysql&gt; select *from insertDemo;</pre><p>The following is the output</p><pre class="result notranslate">+-----------+-------------+------------+ | StudentId | StudentName | StudentAge | +-----------+-------------+------------+ | &nbsp; &nbsp; &nbsp; &nbsp; 1 | John &nbsp; &nbsp; &nbsp; &nbsp;| &nbsp; &nbsp; &nbsp; &nbsp; 21 | | &nbsp; &nbsp; &nbsp; &nbsp; 2 | Carol &nbsp; &nbsp; &nbsp; | &nbsp; &nbsp; &nbsp; &nbsp; 22 | | &nbsp; &nbsp; &nbsp; &nbsp; 3 | Bob &nbsp; &nbsp; &nbsp; &nbsp; | &nbsp; &nbsp; &nbsp; &nbsp; 21 | | &nbsp; &nbsp; &nbsp; &nbsp; 4 | David &nbsp; &nbsp; &nbsp; | &nbsp; &nbsp; &nbsp; &nbsp; 24 | | &nbsp; &nbsp; &nbsp; &nbsp; 5 | Maxwell | 25 | | 6 | Mike | 22 | +-----------+-------------+------------+ 6 rows in set (0.00 sec)</pre>
raja
Updated on 30-Jul-2019 22:30:25

Advertisements