Sebastian wrote:
Gleb Paharenko wrote:
Hello.
What about this:
UPDATE news
SET comments =
( SELECT COUNT(id)
FROM comments
WHERE newsid = news.id
GROUP BY newsid
);
Hi. i came up with a similar query last night, but i didnt use group by.
I have question, is it better to use COUNT(*) ? i thought there is extra
mysql optimization when you use *
thanks.
Not exactly. They aren't the same. COUNT(id) counts distinct values of id,
while COUNT(*) simply counts rows. When you say, "extra mysql optimization",
I expect you are thinking of the case where you want the number of rows in a
table, with no WHERE or GROUP BY clause, like this:
SELECT COUNT(*) FROM mytable;
If the table is MyISAM, this is very quick, because MyISAM tables keep the
current row count in the table information. This doesn't apply to your case,
however.
On the other hand, it will certainly be the case for you that no id to be
counted will be repeated, so COUNT(*) and COUNT(id) will give the same answer.
In that case, COUNT(*) may be faster, as there is no need to waste time
checking ids for distinctness.
I also think you don't want the GROUP BY newsid. It isn't needed, because for
each run of the subquery, only 1 newsid is considered, and it causes NULLs to
be entered for items with no comments, when I expect you want 0s.
Gleb's example data and query:
UPDATE news
SET comments =
(
SELECT COUNT(id)
FROM comments
WHERE newsid = news.id
GROUP BY newsid
);
SELECT * FROM news;
+------+----------+
| id | comments |
+------+----------+
| 1 | 2 |
| 2 | 1 |
| 3 | NULL |
| 4 | 3 |
| 5 | NULL |
+------+----------+
5 rows in set (0.00 sec)
Same data, but with COUNT(*) and no GROUP BY:
UPDATE news
SET comments =
(
SELECT COUNT(*)
FROM comments
WHERE newsid = news.id
);
SELECT * FROM news;
+------+----------+
| id | comments |
+------+----------+
| 1 | 2 |
| 2 | 1 |
| 3 | 0 |
| 4 | 3 |
| 5 | 0 |
+------+----------+
5 rows in set (0.00 sec)
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
