php - 通过php计算行数比SQL中的COUNT更快？

简而言之，我的问题是:这为什么

SELECT r.x, r.y FROM `base` AS r
WHERE r.l=50 AND AND r.n<>'name' AND 6=(SELECT COUNT(*) FROM surround AS d
    WHERE d.x >= r.x -1 AND d.x <= r.x +1 AND
          d.y>=r.y -1 AND d.y<=r.y +1 AND d.n='name')

比这慢很多:

$q="SELECT x,y FROM `base` WHERE l=50 AND n<>'name'";
$sr=mysql_query($q);
if(mysql_num_rows($sr)>=1){
    while($row=mysql_fetch_assoc($sr)){
        $q2="SELECT x,y FROM surround WHERE n='name' AND x<=".
            ($row["x"]+1)." AND x>=".($row["x"]-1).
            " AND y<=".($row["y"]+1)." AND y>=".($row["y"]-1)." ";
        $sr2=mysql_query($q2);
        if(mysql_num_rows($sr2)=6){
            echo $row['x'].','.$row[y].'\n';
        }
    }
}

php版本大约需要300毫秒才能完成，如果我运行“纯SQL”版本(通过phpadmin或php)，则大约需要5秒钟(当我在x和y的范围内使用BETWEEN时，甚至需要13秒钟)

我可能会怀疑SQL版本通常会更快，至少效率更高，所以我想知道我是在做错什么，还是有意义？

编辑:我根据要求添加了两个表的结构:

CREATE TABLE IF NOT EXISTS `base` (
  `bid` int(12) NOT NULL COMMENT 'Base ID',
  `n` varchar(25) NOT NULL COMMENT 'Name',
  `l` int(3) NOT NULL,
  `x` int(3) NOT NULL,
  `y` int(3) NOT NULL,
  `LastModified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `coord` (`x`,`y`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `surround` (
  `bid` int(12) NOT NULL COMMENT 'Base ID',
  `n` varchar(25) NOT NULL COMMENT 'Name',
  `l` int(3) NOT NULL,
  `x` int(3) NOT NULL,
  `y` int(3) NOT NULL,
  `LastModified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `coord` (`x`,`y`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

编辑2:

上面的查询的EXPLAIN SELECT :(关键坐标是x和y的组合)

id  select_type         table   type    possible_keys   key     key_len     ref     rows    Extra
1   PRIMARY             r       range   coord,n         coord   4           NULL    4998    Using where
2   DEPENDENT SUBQUERY  d       ALL     coord           NULL    NULL        NULL    57241   Range checked for each record (index map: 0x1)

最佳答案

您将自己联接两个表。您是一个优化器。您选择“基础”表是嵌套循环联接的外部表。我猜想MySQL的优化器产生了执行计划，它与您不同。

所以人们希望EXPLAIN输出查看连接顺序并检查使用的索引。

顺便说一句，您可以尝试这个查询吗？:

SELECT r.x, r.y
FROM `base` AS r, surround AS d
WHERE r.l=50
  AND r.n<>'name'
  AND d.x >= r.x -1
  AND d.x <= r.x +1
  AND d.y>=r.y -1
  AND d.y<=r.y +1
  AND d.n='name'
GROUP BY r.x, r.y
HAVING COUNT(*) = 6

更新了

您原始查询的工作方式

这是第一次看到Range checked for each record (index map: 0x1)，所以我不知道您的查询是如何工作的。 MySQL Manual提供了一些有关它的信息。似乎surround中的每一行(surround有57k行？)都与base的x，y相比较。如果是这样，您的查询将使用3个深度的嵌套循环联接进行评估。 (基本=>环绕=>基本)，并且比较surround中的每一行(效率低下)

我将在以后的工作中付出更多的努力。该工作了。