php mysql select insert on different DBs in same server

Question

I have two DBs on the same server. Table1 in db1 has 11 columns, and around 8 million rows of data. I want to insert four columns' worth of table1 data (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 a while loop.

But -- based on an experiment to insert 10,000 rows (which took 7 minutes) I think it will take 12 hours to put all 8 million rows into db2.table2. My approach appears extremely inefficient, but have 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 wonder if doing an INSERT INTO SELECT would be faster and more efficient, but I can't figure out how to handle the two db connections.

Any ideas?

Answer 1

If your connection has rights to both databases, there is no need to have two connections. A database connection is a connection to the server, not to a specific database (although you can select a default database within the connection).

What you can do is normal INSERT INTO SELECT within 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 in the database server, without need to move the data to PHP (and back).