counting rows via php is faster than COUNT in SQL?

Question

In short my question is this: Why is this

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')

a lot slower than this:

$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';
        }
    }
}

The php version takes about 300 ms to complete, if I run the "pure SQL" version, be it via phpadmin or via php, that takes roughly 5 seconds (and even 13 seconds when I used BETWEEN for those ranges of x and y)

I would suspect that the SQL version would in general be faster, and more efficient at least, so I wonder, am I doing something wrong, or does it make sense?

EDIT: I added the structure of both tables, as requested:

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;

EDIT 2:

EXPLAIN SELECT for the query above: (the key coord is the combination of x and 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)

Answer 1

You are joinning two tables by yourself. you're an optimizer. you choice 'base' table is outer table for nested loop join. I guess MySQL's optimizer produced execution plan and it was not same as you.

so people want EXPLAIN output to see join order and to check index was used.

by the way, can you try this query?:

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

UPDATED

how your original query works

It was first time seeing Range checked for each record (index map: 0x1) so I can't figure out how your query works. MySQL Manual gives us some information about it. It seems like that every row in surround (surround has 57k rows?) is compare to base's x,y. If so, your query is evaluated using 3 depth nested loop join. (base => surround => base) and moreover every row in surround is compared (this is inefficient)

I will make more effort to find how it works later. It's time to work.