I was told this above code would be more efficient once I had a lot of news entries.
Yes, if (a) SID was *NOT* your primary key and (b) if "lot of news entries" means you have millions of them (or at least hundreds of thousands).
As it is, you'll never see the performance difference, if there even is one. If anything, I'd guess your version could be slower.
So you *DO* have three tables.
No problem. Just slip that other table into the single query:
SELECT N.sid, N.title, N.time, N.bodytext, N.author, N.url, COUNT(C.page_id) AS commentCount
FROM news.news AS N
LEFT JOIN comments.pages AS P ON N.sid = P.page_id
LEFT JOIN comments.comments AS C ON P.id = C.page_id AND C.is_approved = 1
GROUP BY N.sid, N.title, N.time, N.bodytext, N.author, N.url
ORDER BY N.sid DESC
LIMIT $start, $limit
That looks really strange to me.
You are saying that comment.pages.page_id is the FOREIGN KEY to match up with news.news.sid?
And yet comment.pages.id matches up with comments.comments.page_id?
Why would you not name matching columns in the different tables the same???
I would like to comment that it's not a really good idea to have the comments in a separate database from the news. You will suffer a performance penalty for doing so. Again, unless you have hundreds of thousands of records you'll probably never see the difference, so it likely doesn't matter.