PHP: Help with preformance of a series of queries

Question

I have a method that loops through all the rows of a table where a certain condition is met, and then checks if one of the columns in the row appears in a text. The method is below:

public function isRecipeType($ingredients, $type)
{
    $rows = $this->fetchAll($this->select()->where("type = ?", $type));

    foreach($rows as $row)
    {
        if((strpos($ingredients, $row->name)) !== false)
        {
            return true;
        }
    }
}

This takes forever, though. How can I speed this up (without removing rows from my table)?

Answer 1

First things first, do you have an index defined on the type column? With that being the only condition in your search, you'll want it to be an efficient one.

Answer 2

Can´t you do everything in SQL?

Corrected - untested - example:

"... WHERE `type`=? AND FIND_IN_SET(name, `$ingredients`) > 0 LIMIT 1"

// return TRUE if a row is found

For FIND_IN_SET to work, the values have to be separated by commas so you might have to transform your $ingredients variable.

Answer 3

The problem you are trying to solve could be expressed as "are there any rows, with type=X, whose name contains text Y?"

This is a question the database can answer, without you having to write a loop in PHP. It's quicker to use the database, the database has access to more information about tuning than you do. It's inefficient to basically download the entire subset of the table matching that type to the PHP processor, from the database, probably over a network, in order to answer the query. Let the DB do the work for you. It's also easier to read as SQL is a declarative language, as opposed to the PHP solution being imperative.

public function isRecipeType($ingredients, $type)
{
    $sql = "SELECT COUNT(*) AS c ".
           "FROM table ".
           "WHERE type = ? ".
           "   AND ? LIKE CONCAT('%', name, '%')";
    $rows = execute($sql, $type, $ingredients);
    $row = $rows[0];
    return $row["c"] > 0;
}

This uses MySQL syntax, if you use another database then replace the last line of the SQL with

           "   AND ? LIKE '%' || name || '%'";

I have invented an execute method in your code there, I don't know what database access layer you are using, but no doubt you will have access to something similar.

As another answer says, make sure you have an index on the type column. The execution plan for this statement will then be: Find all the rows with the matching type (using the index) and then go through the data and apply the LIKE.

Unless more than e.g. 10% of the rows match the type column, then a sequential read over all the data (i.e. not using the index) will be faster than using the index to identify rows and then random-access reading them. But the database will know if that's the case or not, it doesn't hurt to create the index, then the database can use it or not based on this criteria.