PHP MySQL "Select" then "Insert" Large Data

Question

what is the best way to Select then Insert large data into the Database (InnoDB Engine)?

I have got more than 60000 rows need to be inserted from another table without LIMIT the result.

Is there a way to prevent my server memory being consumed too much or speed up the query? Could I split the query with let say LIMIT 5000 until its done copying all the data?

this is example query:

$query = 'SELECT * FROM orig_table WHERE user_id = 1 AND sent = 0'; // more than 60000 rows

if ( $query ) {
  foreach ( $query as $row ) {
     'INSERT INTO copy_table VALUE()';    
  }   
}

Possible duplicate of [Speeding up large numbers of mysql updates and inserts](http://stackoverflow.com/questions/3952288/speeding-up-large-numbers-of-mysql-updates-and-inserts) — , Nov 12 '15 at 12:35
[You can insert the results of a select statement directly](https://dev.mysql.com/doc/refman/5.6/en/insert-select.html) — , Nov 12 '15 at 12:37

score 1 · Answer 1 · answered Nov 12 '15 at 12:42

1

Maybe just execute in in one query

insert into copytable (column1,column2)
SELECT column1,column2 FROM orig_table WHERE user_id = 1 AND sent = 0

answered Nov 12 '15 at 12:42

z48o0

98
7

PHP MySQL "Select" then "Insert" Large Data

1 Answers1