MySQL table column replication, MySQL connection time and memory control

MySQL table column replication, MySQL connection time and memory control.
There are two table temporary table temp_t, and the positive table table1, are MyISAM, respectively, there are about 28W records.
Cron periodically fetches some information from the network every minute and stores it to temp_t (frequent writes).
Then cron timed every 10 minutes to copy information from temp_t to positive table table1 (read frequently, write once every 10 minutes).

temp_t Field ID (auto_increment), title (varchar), content (varchar), date (DATETIME), MyKey (tinyint (1) Default 1, This to determine whether it has been copied to the positive table, the update is 2 after replication)

Table1 Field ID (no auto_increment), title (varchar), content (varchar), date (DATETIME)


28W records, each data table about 300MB, each update almost to 2k-3k record.
If you copy directly, MySQL connection time is too long, resulting in MySQL performance degradation table1 will appear similar to the lock table phenomenon, query table1 time significantly longer

Require DirName (__file__). '/.. /connection.php ';
mysql_select_db ("News", $connextion);
mysql_query ("SET NAMES UTF8");
$query = mysql_query ("select * from temp_t where MyKey = ' 1 '");
while ($rows = Mysql_fetch_array ($query)) {
mysql_query ("UPDATE temp_t SET mykey= ' 2 ' WHERE id= '". mysql_real_escape_string ($rows [' ID ']). "'";
mysql_query ("INSERT into table1 (id,title,content,date) VALUES ('". mysql_real_escape_string ($rows [' ID ']). "', '". Mysql_real_escape_string ($rows [' title ']). "', '". mysql_real_escape_string ($rows [' content ']). "', '". Mysql_real_ Escape_string ($rows [' Date ']).
}
Mysql_close ($connextion);

So I want to first read the records from the temp_t data, save as a JSON array, and then copy the data to Table1, but sometimes encounter cannot allocate memory, and then cause the entire server crashed (is not this JSON array too large, Account for a huge amount of memory). PS: Already in the php.ini memory limit added to 64M (but always think that the temporary solution, such a replication process, do not need so much memory, the key or the code is unreasonable).

Require DirName (__file__). '/.. /connection.php ';
mysql_select_db ("News", $connextion);
mysql_query ("SET NAMES UTF8");
$query = mysql_query ("select * from temp_t where MyKey = ' 1 '");
$jon = Array ();
while ($rows = Mysql_fetch_array ($query)) {
$jon [' a '] = $rows [' id '];
$jon [' b '] = $rows [' title '];
$jon [' c '] = $rows [' content '];
$jon [' d '] = $rows [' Date '];
$pjon. = Json_encode ($jon). ', ';
}
Mysql_close ($connextion);
$NJSO = Json_decode (' ['. substr ($pjon, 0,-1) '];
foreach ($njso as $nx) {
if ($nx->a) {
Require DirName (__file__). '/.. /connection.php ';
mysql_select_db ("News", $connextion);
mysql_query ("SET NAMES UTF8");
mysql_query ("UPDATE temp_t SET mykey= ' 2 ' WHERE id= '". Mysql_real_escape_string ($nx->a). "'");
mysql_query ("INSERT into table1 (id,title,content,date) VALUES ('". Mysql_real_escape_string ($nx->a). "', '". Mysql_ Real_escape_string ($nx->b). "', '". Mysql_real_escape_string ($nx->c). "', '". Mysql_real_escape_string ($nx- >d). "'");
Mysql_close ($connextion);
}
}

So to help prawns, there is no more saving code to copy the information of the table? Require MySQL to connect for a short time with less memory consumption? Thank you.

Share to:

------Solution--------------------
Execute these two sentences in turn.
INSERT into table1 (id,title,content,date) values
SELECT ID, title,content,date from temp_t where MyKey = ' 1 '

UPDATE temp_t SET mykey= ' 2 ' WHERE mykey = ' 1 '

No need to excuse PHP transfer

Since temp_t is temporarily deposited, why not delete the data after the transfer?
------Solution--------------------

