如何按计数或其他表的一些条件排序

我有一个项目，创建一个网站。
我想通过这些条件来提出问题。

1. 根据最新的问题显示，是的，我知道只是按created desc订购。
2. 根据大多数答案显示和排序问题。
3.显示和排序的问题，大多数投票。(和大多数答案一样)例子。
4. 提出没有回答的问题。示例

这是我在数据库中的表结构。

TABLE question
COLUMNS
q_id (primary key)
userid
title
content
created
TABLE answer
COLUMNS
a_id (primary key)
userid
q_id
content
created
TABLE vote
COLUMNS
userid
q_id
created

每个表可以有一百万行

对于我上面的4个问题，我正在尝试这些SQL (s)。1按最近的问题显示。(解决)

select * from question order by created desc

2按大多数答案排序。

SELECT q.*, COUNT(a.id) as answerCount
FROM question q
LEFT JOIN answer a
        ON (q.q_id = a.q_id)
ORDER BY answerCount DESC

3显示和排序的大多数投票。(似乎慢了)。

SELECT q.*, COUNT(v.id) as voteCount
FROM question q
LEFT JOIN vote v
        ON (q.q_id = v.q_id)
ORDER BY voteCount DESC

4显示未回答的问题。

SELECT q.* 
    FROM question q
    LEFT JOIN answer a 
        ON p.q_id = a.q_id
    WHERE a.q_id IS NULL ORDER BY q.created DESC

注意:如果我使用INNER JOIN，计数= 0的行将不被选中。

据我所知，其他网站一般已经有answers和votes的统计领域了吗?为了使它更快，我应该改变这个，或者他们有一些不需要在question表中计算答案和投票的算法?

TABLE question
COLUMNS
q_id (primary key)
userid
title
content
created
answer_count
votes_count

帮助或建议将是真正感激。

SELECT q.*, COUNT(*) as answerCount
FROM question q
LEFT JOIN answer a ON a.q_id = q.q_id
GROUP BY q.q_id
ORDER BY answerCount DESC;

SELECT q.*, (select count(*) from answers a where a.q_id = q.q_id) as answerCount
FROM question q
ORDER BY answerCount DESC;

SELECT q.*, a.answerCount
FROM question q
LEFT JOIN (select q_id, count(*) as answerCount from answers group by q_id) a
  ON a.q_id = q.q_id
ORDER BY a.answerCount DESC;

SELECT q.*
FROM question q
WHERE NOT EXISTS (select * from answer a where a.q_id = q.q_id)
ORDER BY q.created DESC;