Combining MySQL queries for less server load

[Jakezz]

Ok so I have table1 holding all my posts, then I have table2 holding all my comments and inside table2 I have a field where the matching post ID is inserted so it knows what comments goto what post. When the script goes to print out all the posts it has to run a query for each post to count how many comments there are for it. So for example if I have 500 posts stored I have to run about 501 queries on the main page (the first query to gather all the posts and then a query in each while() loop to count the comments). I don't really like this because on sites that get upwards of 70,000 uniques daily it is stressing the server (so I hear). Is there any way possible to lessen the amount of queries?

[altexis]

Yes there is a lightening fast SQL command.. COUNT
Read http://dev.mysql.com/doc/refman/5.0/en/counting-rows.html

Then if you want a specific part of a MySQL table use LIMIT
Open http://dev.mysql.com/doc/refman/5.0/en/select.html
Press Ctrl+F and look for LIMIT

[Jakezz]

Yes COUNT is what I am using to count the comments for each post, still though it has to query the database once for every post which is a lot...

[altexis]

Use the GROUP BY command..

SELECT post_id, COUNT(*) FROM table2 GROUP BY post_id