How to sort fields in 1 table by values ​​in 2?

Question

MySQL

Good afternoon.
I'm trying to attach the ability to sort by the number of comments in a post to the article module in opencart.
The first table of this module contains data about, in fact, records, the
Second table contains comments indicating the id of the record (from the first table), in which it is placed.
Those.
1) oc_blog (blog_id, text, title )
2) oc_blog_comment (blog_id, comment)
You need to sort posts based on the number of comments in each.
Please tell me the correct sql query.

Answer 1

option 1 (with window functions):
select distinct p.blog_id, p.text, count(w.blog_id) over (partition by w.blog_id) from oc_blog p, oc_blog_comment w where w.blog_id = p.blog_id order by 3 desc limit 5;
option 2 (pre-counted):
select p.blog_id, p.text, t.count from oc_blog p, (select blog_id, count(blog_id) from oc_blog_comment group by blog_id ) t where p.blog_id=t.blog_id order by t.count desc limit 5;
The first option on my sample is 3 times slower, in it the window function is executed for each row.
The second option is also more convenient in the future to limit, for example, articles no older than a month, to push into the internal select.
PySy, the limit is purely for convenience.