PHP:帮助实现一系列查询-PHP:Helpwithpreformanceofaseriesofqueries

3  

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

您试图解决的问题可以表示为“是否有任何行，类型为=X，其名称包含文本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.

这是数据库可以回答的问题，无需编写PHP循环。使用数据库更快，数据库可以访问更多关于调优的信息。从数据库(可能是通过网络)下载与该类型匹配的表的整个子集，以便回答查询，这样做效率会很低。让DB为你做这项工作。它也更容易阅读，因为SQL是一种声明性语言，而PHP解决方案是必需的。

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

这使用了MySQL语法，如果您使用另一个数据库，那么请将SQL的最后一行替换为

           "   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.

我在您的代码中创建了一个execute方法，我不知道您正在使用哪个数据库访问层，但是毫无疑问您将访问类似的东西。

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.

正如另一个答案所说，请确保在type列上有索引。该语句的执行计划将是:查找具有匹配类型的所有行(使用索引)，然后遍历数据并应用类似的操作。

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.

除非有超过10%的行与类型列匹配，否则顺序读取所有数据(即不使用索引)将比使用索引来识别行和随机访问读取它们更快。但是数据库将知道，如果是这样的话，那么创建索引并没有什么害处，那么数据库可以根据这个条件使用它或不使用它。

2  

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.

首先，您是否在type列上定义了索引?这是你搜索的唯一条件，你会希望它是一个有效的条件。

2  

Can´t you do everything in SQL?

可以在SQL ?´t你所做的一切

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.

对于FIND_IN_SET的工作，这些值必须用逗号分隔，因此您可能需要转换$成分变量。