StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POcounting rows via php is faster than COUNT in SQL?
text
Body
copied!<p>In short my question is this: Why is this</p> <pre class="lang-sql prettyprint-override"><code>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') </code></pre> <p>a lot slower than this:</p> <pre><code>$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'; } } } </code></pre> <p>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)</p> <p>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?</p> <p>EDIT: I added the structure of both tables, as requested:</p> <pre><code>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; </code></pre> <p>EDIT 2:</p> <p>EXPLAIN SELECT for the query above: (the key coord is the combination of x and y)</p> <pre><code>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) </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload