php mysql and in_array performance

Question

Is there anything to get more performance? Because it tooks about 8 secs. car_models database has over 100.000 records, cars database has over 20.000 records and finaly car_parts has over 20.000 records. I could do for that a database something like keywords. But the problem is the databases are dynamic. I mean the records adding everyday by editors. And I can't touch the part of the software because it is not open source...

$news = dbquery("SELECT * FROM " . DB_NEWS . " ORDER BY news_id DESC LIMIT 0,1");
while ($news_data = dbarray($news))
{

    echo $news_data['news_subject'];
    $news_first_part = str_replace("\\", "", $news_data['news_news']);
    $news_first_part = explode('.', $news_first_part); //first phrase
    $news_first_part = $news_first_part[0];
    $news_second_part = str_replace("\\", "", $news_data['news_extended']);
    $find = array();
    $keywords = dbquery("SELECT name FROM cars GROUP BY name");
    while ($keywords_data = dbarray($keywords))
    {

        $my_keyword = $keywords_data['name'];
        $news_first_part = str_replace($my_keyword, "<a href='keyword.php?keyword=$my_keyword' title='$my_keyword'>$my_keyword</a>", $news_first_part);
        $news_second_part = str_replace($my_keyword, "<a href='keyword.php?keyword=$my_keyword' title='$my_keyword'>$my_keyword</a>", $news_second_part);
        if ($news_first_part OR $news_second_part AND !in_array($my_keyword, $find,true))
        {

            array_push($find, $my_keyword);

        }



    }


    $my_keyword="";
    $keywords = dbquery("SELECT name FROM car_models GROUP BY name");
    while ($keywords_data = dbarray($keywords))
    {

        $my_keyword = $keywords_data['name'];
        if (strlen($my_keyword) > 10 && !in_array($my_keyword, $find, true))
        {

            $news_first_part = str_replace($my_keyword, "<a href='keyword.php?keyword=$my_keyword' title='$my_keyword'>$my_keyword</a>", $news_first_part);
            $news_second_part = str_replace($my_keyword, "<a href='keyword.php?keyword=$my_keyword' title='$my_keyword'>$my_keyword</a>", $news_second_part);

        }



    }


    $keywords = dbquery("SELECT name FROM car_parts GROUP BY name");
    while ($keywords_data = dbarray($keywords))
    {

        $my_keyword = $keywords_data['name'];
        if (strlen($my_keyword) > 10)
        {

            $news_first_part = str_replace($my_keyword, "<a href='keyword.php?keyword=$my_keyword' title='$my_keyword'>$my_keyword</a>", $news_first_part);
            $news_second_part = str_replace($my_keyword, "<a href='keyword.php?keyword=$my_keyword' title='$my_keyword'>$my_keyword</a>", $news_second_part);

        }



    }


    $my_keyword="";
    echo $news_first_part . '.'; //note I added the final ponctuation
    $news_first_part .= ".";
    $news_second_part = str_replace($news_first_part, "", $news_second_part);
    echo nl2br($news_second_part);

}

Answer 1

You could build some kind of array tree-structure, to limit the size of the sub-arrays. The array would then become:

• $arr['a']['apple'];
• $arr['a']['anna'];
• $arr['a']['awesome'];
• $arr['b']['bread'];
• $arr['b']['beer'];
• $arr['c']['cucumber'];

Instead of

• $arr['apple'];
• $arr['anna'];
• $arr['awesome'];
• $arr['bread'];
• $arr['beer'];
• $arr['cucumber'];

As you can see, an in_array would become lots faster.

• If we would like to check "php", there is no array for the first-letter "p": nothing has to be walked through again
• If we would like to check "ananas", we would have to lookup 3 items, instead of 6!

Note: the "outer" array could consist of the 1st letter (or 2, 3, 4, etc.) depending on the size of your set.

In PHP code you would get something like

$word = "ananas";
$arr = array();
if (!isset($arr[$word{0}]) || !in_array($word, $arr[$word{0})) {
  // New word
  if (!isset($arr[$word{0}])) {
    $arr[$word{0}] = array($word);
  } else {
    $arr[$word{0}][] = $word;
  }
}

Answer 2

Try it this way, it is much faster.

  if ($news_first_part OR $news_second_part AND !isset($find[$my_keyword]))
        {

            $find[$my_keyword] = 1;

        }

Update the rest of the code correspondingly. Actually, you do not need to use a !isset($find[$my_keyword]) check here.