CODEWITHSUNDEEP
phpmysqlmysqliimport
Chris Maher
Chris Maher

Reputation: 43

Better way of inserting multiple strings in a mysql database

So, I've got a few txt files, each container around 400,000 lines.

Each line is a word, I need to add to my database, if it isn't in there already.

Currently my code for checking/adding every word is

$sql = mysql_sql("SELECT `id` FROM `word_list` WHERE `word`='{$word}' LIMIT 1");
$num = mysql_num($sql);
if($num == '0'){
    $length = strlen($word);
    $timestamp = time();
    @mysql_sql("INSERT INTO `word_list` (`word`, `length`, `timestamp`) VALUES ('{$word}', '{$length}', '{$timestamp}')");
}

and the functions being called are:

function mysql_sql($sql){
    global $db;

    $result = $db->query($sql);

    return $result;
}
function mysql_num($result){
    return $result->num_rows;
}

I'm looking for a better way to insert each word into the database.

Any ideas would be greatly appreciated.

Upvotes: 1

Views: 947

Answers (2)

O. Jones
O. Jones

Reputation: 108676

I can think of some ways to do this.

First, if you have access to the MySQL server's file system you can use LOAD DATA INFILE to create a new table, then do an insert from that new table into your word_list table. This will most likely be your fastest option.

Second (if you don't have access to the MySQL server's file system), put a primary key or unique index on word_list.word. Then get rid of your SELECT query and use INSERT IGNORE INTO word_list .... That will allow MySQL automatically to skip the duplicate items without any need for you to do it with a query/insert operation.

Third, if your table uses an access method that handles transactions (InnoDB, not MyISAM), issue a BEGIN; statement before you start your insert loop. Then every couple of hundred rows issue COMMIT;BEGIN; . Then at the end issue COMMIT;. This will wrap your operations in multirow transactions so will speed things up a lot.

Upvotes: 1

Umair Ayub
Umair Ayub

Reputation: 21261

Try out this code. It will first create query with all your values and you will run query only ONCE ... Not again and again for ever row

$values = array();

$sql = mysql_sql("SELECT `id` FROM `word_list` WHERE `word`='{$word}' LIMIT 1");
$num = mysql_num($sql);

$insert_query = "INSERT INTO `word_list` (`word`, `length`, `timestamp`) VALUES ";

if ($num == '0') {
    $length = strlen($word);
    $timestamp = time();
    $values[] = "('$word', '$length', '$timestamp')";
}

$insert_query .= implode(', ', $values);

@mysql_sql($insert_query);

Upvotes: 0

Related Questions