带有子查询和连接的 MYSQL 查询优化

Question

嗨，我有 800 万行数据，需要优化 Mysql 查询以从该数据中获取一行。我正在使用以下查询，但其服务器响应时间太长，以至于在页面加载速度中产生问题

SELECT q.id
, q.title
, q.question
, q.price
, q.created
, q.userid
, q.duedate
, q.tags
, s.id subjectid
, sc.id subcategoryid
, s.title subject
, sc.title subcategory
, q.statusid
, (SELECT  COUNT(id) FROM tbl_answers a WHERE a.questionid = q.id AND a.statusid = 1 AND a.deleted = 'N') q_num_answers
, u.username
, u.image
, u.gender
, (SELECT  COUNT(id) FROM tbl_answers a WHERE a.userid = q.userid AND a.statusid = 1 AND a.deleted = 'N') num_answers
, (SELECT  COUNT(id) FROM tbl_questions WHERE userid = q.userid AND statusid = 1 AND deleted = 'N') AS num_questions
, 0 amt_earned
, 0 amt_spent
, 0 num_sold
, (SELECT  COUNT(ur.id) FROM tbl_users_ratings ur WHERE ur.userid = q.userid AND ur.deleted = 'N') u_num_ratings
, (SELECT  COALESCE(SUM(ur.rating), 0) FROM tbl_users_ratings ur WHERE ur.userid = q.userid AND ur.deleted = 'N') u_score
FROM tbl_questions q
JOIN tbl_subjects s 
ON q.subject = s.id
JOIN tbl_subjects sc 
ON q.subcategory = sc.id
LEFT 
JOIN tbl_users u 
ON u.id = q.userid
WHERE q.deleted = '$show_deleted' 
AND q.id = ? 
LIMIT 1

Answer 1

这些索引可能会有所帮助。 (我假设id是每个表的PRIMARY KEY。

ur:  (deleted, userid, rating)
a:  (deleted, statusid, userid)
a:  (deleted, statusid, questionid)

请提供EXPLAIN SELECT ...。

不要使用COUNT(id)，除非您需要检查id是否未NULL。 通常的写法是COUNT(*)。

在一个地方，您正在检查为deleted提供的值。 在另一个中，你硬编码它。 也许错了？

AND  ur.deleted = 'N'

如果q的PRIMARY KEY是id，那么这将导致 1 行或无行。 我错过了什么？

WHERE  q.deleted = '$show_deleted'
AND  q.id = ?

(可能还有更多提示。 请更改我建议的内容并遵循其他人的建议。 那我们再来看看。