0

I am new at using PHP-MySQL. I have two MySQL tables:

  • Concreteness: A table that contains concreteness scores for 80K words
  • Brian: A table with 1 million rows, each containing one or two words.

I have a small PHP script that takes each row in "Brian", parses it, looks for the scores in "Concreteness" and records it in "Brian."

I have been running this script with several other tables that had 300-400k rows with each hundreds of words. "Brian" is different because it has 1 million rows with 1 or 2 words per row. For some reason, my script is SUPER slow with Brian.

Here is the actual script:

 <?php
include "functions.php";
set_time_limit(0); // NOTE: no time limit
if (!$conn)
    die('Not connected : ' . mysql_error());
$remove = array('{J}','{/J}','{N}','{/N}','{V}','{/V}','{RB}','{/RB}'); // tags to remove       
$db = 'LCM';
mysql_select_db($db);

$resultconcreteness = mysql_query('SELECT `word`, `score` FROM `concreteness`') or die(mysql_error());
$array = array(); // NOTE: init score cache
while($row = mysql_fetch_assoc($resultconcreteness))
    $array[strtolower($row['word'])] = $row['score']; // NOTE: php array as hashmap
mysql_free_result($resultconcreteness);

$data = mysql_query('SELECT `key`, `tagged` FROM `brian`') or die(mysql_error()); // NOTE: single query instead of multiple
while ($row = mysql_fetch_assoc($data)) {
    $key = $row['key'];
    $tagged = $row['tagged'];
    $weight = $count = 0;
    $speech = explode(' ', $tagged);
    foreach ($speech as $word) {
        if (preg_match('/({V}|{J}|{N}|{RB})/', $word, $matches)) {
            $weight += $array[strtolower(str_replace($remove, '', $word))]; // NOTE: quick access to word's score
            if(empty($array[strtolower(str_replace($remove, '', $word))])){}else{$count++;}

        }
    }
    mysql_query('UPDATE `brian` SET `weight`='.$weight.', `count`='.$count.' WHERE `key`='.$key, $conn) or die(mysql_error());
// Print out the contents of the entry 
        Print "<b>Key:</b> ".$info['key'] .  " <br>";  
}
mysql_free_result($data);
?>
user1029296
  • 609
  • 8
  • 17
  • 1
    `mysql_*` is deprecated, consider using [MySQLi or PDO](http://stackoverflow.com/questions/13569/mysqli-or-pdo-what-are-the-pros-and-cons)' – Jack Oct 11 '14 at 21:36
  • Also, you don't need to explode in your while loop, try - `$tagged[] = $row['tagged']` – Jack Oct 11 '14 at 21:47

1 Answers1

0

I guess the real problem is the 1 million mysql update statements you fire to the database. Consider bundling the update statements (and also remove the print):

$i=0;
while ($row = mysql_fetch_assoc($data)) {

  // ... left out the obvious part

  $sql .= "'UPDATE `brian` SET `weight`='.$weight.', `count`='.$count.' WHERE `key`='.$key;";

  $i++;
  if ($i%1000 == 0) {
    mysql_query($sql) or die(mysql_error());
    $i=0;
    $sql = "";
  }
}
// remember to save the last few updates
mysql_query($sql) or die(mysql_error());
edlerd
  • 2,145
  • 1
  • 16
  • 24
  • `mysql_` extension does not support multiple queries at once, but `mysqli_` does, especially useful are prepare statements. – Cheery Oct 11 '14 at 22:01
  • It must seem like I am retarded but I can't get this to work. I get the rror: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''UPDATE `brian` SET `weight`='.0.', `count`='.0.' WHERE `key`='.1, Resource id #' at line 1 – user1029296 Oct 11 '14 at 22:07
  • like Cheery already said, mysql_ does not allow this, but with prepared statements it should work, you can use the same idea there. (fire to the database every 1000th run). – edlerd Oct 11 '14 at 22:09
  • Ok, this is beyond my skills. Thanks for your help anyway. – user1029296 Oct 11 '14 at 22:12
  • if you want to keep the code the way it is, just replace mysql_query with mysqli_query, also all the other mysql_* functions with mysqli_ and it should work. – edlerd Oct 13 '14 at 09:55
  • Thanks for your answer. Should I keep the code above or the original code? – user1029296 Oct 13 '14 at 16:34