MySQL batch update data technique

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

    When in the development of the sometimes repair data manually or update data, generally in their own projects to write a main function can be modified to read data or, but the implementation of the time from the local network connectivity,

If a lot of data. Hundreds of thousands or millions of other words above, performance will be very poor, this time the SQL statement written on the database directly executed will be much better.

In an MySQL database engine of InnoDB, the general data crud have built-in affairs, but also the default timeout, like the 50s, if a large amount of data and many join query words will timeout,

This time may wish to query data to create temporary table and create an index on a temporary table, then add or modify data on temporary table connection, when the performance will be a lot faster, for example:

 DROP TEMPORARY TABLE IF EXISTS tmp;

 CREATE TEMPORARY TABLE tmp
   select b.UserId,b.field1,b.field2, b.field3 from
                   (select Max(AutoId) as AutoId
                   from T_Table2 where UserId
                   group by UserId) a left join T_Table1 b on a.AutoId=b.AutoId;
 ALTER TABLE tmp ADD INDEX `index_UserId` (`UserID` ASC) ;

INSERT INTO T_Table3(`UserId`,`field1`,`field2`,`field3`, `CreateTime`,`CreateIp`,`ModifyTime`,`ModifyIp`)
   select a.UserId,a.field1,a.field2,a.field3,now(),'127.0.0.1',now(),'127.0.0.1'
   from tmp;

To delete the temporary table after, DROP TEMPORARY TABLE tmp;

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Fabian at November 26, 2013 - 7:00 PM