Mysql COUNT VS num rows performance

Question

I want to select the amount of forum posts compared to a list of users. So it will look like this:
USERID --- FORUMPOSTS
3647 - 2
7467 - 14
2673 - 39
3224 - 5
... and so on

Now I'm asking if it would be faster to count the forum post via COUNT(*) or looping through the userlist before and creating a new query for each user to count his/her forum posts via mysql_num_rows.

juergen d · Accepted Answer · 2013-07-22 16:19:41Z

1

You can let SQL do the grouping and counting

select userid, count(*) as forumposts
from your_table
group by userid

answered Jul 22, 2013 at 16:19

juergen d

205k40 gold badges305 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

eggyal · Accepted Answer · 2013-07-22 16:19:37Z

0

Now I'm asking if it would be faster to count the forum post via COUNT(*) or looping through the userlist before and creating a new query for each user to count his/her forum posts via mysql_num_rows.

It'll be faster to do the former, "count the forum post via COUNT(*)". You can group the results as follows:

SELECT userid, COUNT(*) FROM my_table GROUP BY userid

It'll be even faster still if your table has an index on the userid column.

answered Jul 22, 2013 at 16:19

eggyal

127k18 gold badges221 silver badges245 bronze badges

Collectives™ on Stack Overflow

Mysql COUNT VS num rows performance

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related