Php mysql select insert on different db on the same server
I have two databases on the same server. Table 1 in db1 has 11 columns and about 8 million rows of data. I want to insert four data tables of table1 (i.e. 4 columns x 8 million rows) into table2 in db2.
I tried this using php in the script below, first selecting everything in db1.table1, then inserting the results inside the while loop.
But - based on experiment, to insert 10,000 rows (which took 7 minutes), I think it will take 12 hours to fit all 8 million rows in db2.table2. My approach seems to be extremely inefficient, but take a look:
<?php
/////////// set up connections to db1 and db1
$link = new mysqli('localhost', 'root', '', 'db1');
/////////// query to get data from db1/table1
$sql1 = "select * from db1.table1";
$sql1_result = mysqli_query($link, $sql1) or die ("sql1 failed: " . mysqli_error());
/////////// turn data from db1.table1 into php variables
while ( $row = mysqli_fetch_assoc($sql1_result))
{
extract($row);// gives 8 million rows, each containing 11 pieces of data
/////////// connect to database db2
$link->select_db('db2');
/////////// put query results into db2
$sql2 = "insert ignore into db2.table2 (field4, field5, field6, field7)
values ('$field4', '$field5', '$field6', '$field7')";
$sql2_result = mysqli_query($link, $sql2) or die ("sql2 failed: " . mysqli_error());
}
?>
After researching, I'm wondering if doing INSERT INTO SELECT would be faster and more efficient, but I can't figure out how to handle the two db connections.
Any ideas?
source to share
If your connection has rights to both databases, there is no need to have two connections. A database connection is a connection to a server, not a specific database (although you can choose the default database in the connection).
What you can do is a regular INSERT INTO SELECT in one connection:
insert ignore into db2.table2 (field4, field5, field6, field7)
select field4, field5, field6, field7
from db1.table1;
This way, the execution is done on the database server, without having to move the data to PHP (and vice versa).
source to share