CODEWITHSUNDEEP
phpmysqlinsertsql-insert
N3mo
N3mo

Reputation: 1393

MySQL and INSERT IGNORE

I am trying to read from a database in MySQL and insert my data in another database in MySQL .

my first table is like this

CREATE TABLE IF NOT EXISTS `link` (
  `_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `country` varchar(30) COLLATE utf8 DEFAULT NULL,
  `time` varchar(20) COLLATE utf8 DEFAULT NULL,
  `link` varchar(100) COLLATE utf8 DEFAULT NULL,
  PRIMARY KEY (`_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6149 ;

and the second table is 

CREATE TABLE IF NOT EXISTS `country` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(15) CHARACTER SET utf8 NOT NULL,
  `Logo` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `Name_3` (`Name`),
  UNIQUE KEY `ID` (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8457 ;

There are about 6114 rows in first table that I'm trying to insert to second using this code 

<?php

$tmp = mysqli_connect(******, *****, ****, *****); // First table in here

if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$main = mysqli_connect(*****, *****, ****, ******); //Second table in here

if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$req = "SELECT country  FROM link";
$result = mysqli_query($tmp, $req) or die( mysqli_error($tmp) );

echo "-> ".mysqli_num_rows($result)."<br>";

while ($row = mysqli_fetch_array($result)) {
    $con = $row["country"];

    $req = "INSERT IGNORE INTO country (Name) VALUES ('$con')";
    mysqli_query($main, $req) or die( mysqli_error($main) ) ;

}
?>

problem is the php code works but for 6114 take a very long time which I can't afford . what is causing the code to take this long to work ? is it the "INSERT IGNORE" ? is there any way I can do this faster ?

Upvotes: 0

Views: 641

Answers (2)

bomberdini
bomberdini

Reputation: 9

you can try a create an index on column "country" of table Link.

Upvotes: 1

eggyal
eggyal

Reputation: 125855

Since the databases are on the same server, you can simply use INSERT ... SELECT (which avoids having to bring the data into PHP and loop over the results executing separate database commands for each of them, so will be considerably faster):

INSERT INTO db2.country (Name) SELECT country FROM db1.link

Upvotes: 5

Related Questions