Create A Query Outside The Loop

Question

I am trying to check if the words in a large sentence (10k or more words) are present in a dictionary, however executing my query this many times makes my page slow. How can I move the query outside the loop to speed the page up?

foreach ($word as $key => $value) {
   $checkWord = checkDict($value);
   if($checkWord==true){
     return $value;
   } else {
     $word[$key] = Del_Inflection_Suffixes($value); //skip this function
     if(checkWord($value)){
        return $value;
     }
     $word[$key] = Del_Derivation_Suffixes($value); //skip this function
     if(checkWord($value)){
        return $value;
     }
     $word[$key] = Del_Derivation_Prefix($value); //skip this function
     if(checkWord($value)){
        return $value;
     }
   }  
}

function checkDict($rootWord){ //check if the word already exist in the database
   $sql = mysql_query("SELECT * from dictionary where rootword ='$rootWord' LIMIT 1");
   $result = mysql_num_rows($sql);
   if($result==1){
     return true;
   }else{
     return false;
   }
}

The `mysql_*` functions are deprecated as of PHP v5.5 and have been removed as of v7.0. They should not be used for new code and should be swapped out for [mysqli](http://php.net/manual/en/book.mysqli.php) or [PDO](http://php.net/manual/en/book.pdo.php) equivalents as soon as possible. — Alex Howansky, Apr 06 '17 at 19:14

score 1 · Accepted Answer · edited May 23 '17 at 12:32

The reason your script is so slow is because typically executing thousands of DB queries is going to be quite a huge bottleneck. You should therefore rethink your logic. I am also going to assume you are going to ditch mysql_* functions as they should not be used. I am using PDO in my answer.

1. Select entire table:

Select the entire table, put it in an array and use PHP to check if that word is present or not.

$stmt = $dbh->query("SELECT rootword from dictionary");
$dictionary = $stmt->fetchAll();

foreach ($word as $key => $value) {
    if (in_array($value, $dictionary)) {
        // word exists
    }
    else {
        // word doesn't exist
    }
}

2. Use `IN`

Depending on the size of your table, the above way may not work very efficiently either, and you could even run out of memory depending on your PHP settings. You could therefore add all of your words to IN() in MySQL. Just beware that there is also a limit to the amount you can pass to IN as well.

$in  = str_repeat('?,', count($word) - 1) . '?';
$stmt = $db->prepare("SELECT rootword FROM dictionary WHERE rootword IN ($in)");
$stmt->execute($word);
$dictionary = $stmt->fetchAll();

foreach ($word as $key => $value) {
    if (in_array($value, $dictionary)) {
        // word exists
    }
    else {
        // word doesn't exist
    }
}

Create A Query Outside The Loop

1 Answers1

1. Select entire table:

2. Use IN

2. Use `IN`