count sql 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秒钟(当我使用BETWEEN用于x和y的那些范围时甚至是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)