mysql查询需要花费很多时间

This query in rails does not come to an end. Why? Query is:

rails中的此查询不会结束。为什么?查询是:

    SELECT CONCAT(f.name, ' ', f.parent_names) AS FullName,
       stts.name AS 'Status',
       u.name AS Unit,
       city.name AS City,
       hus.mobile1 AS HusbandPhone,
       wife.mobile1 AS WifePhone,
       f.phone AS HomePhone,
       f.contact_initiation_date AS InitDate,
       fh.created_at AS StatusChangeDate,
       cmt.created_at AS CommentDate,
       cmt.comment AS LastComment,
       f.reconnection_date AS ReconnectionDate,
  (SELECT GROUP_CONCAT(t.name, ' ')
   FROM taggings tgs
   JOIN tags t ON tgs.tag_id = t.id
   WHERE tgs.taggable_type = 'family'
     AND tgs.taggable_id = f.id) AS HandlingStatus
FROM families f
JOIN categories stts ON f.family_status_cat_id = stts.id
JOIN units u ON f.unit_id = u.id
JOIN categories city ON f.main_city_cat_id = city.id
LEFT JOIN contacts hus ON f.husband_id = hus.id
LEFT JOIN contacts wife ON f.wife_id = wife.id
LEFT JOIN comments cmt ON f.id = cmt.commentable_id
AND cmt.created_at =
  (SELECT MAX(created_at)
   FROM comments
   WHERE commentable_id = f.id)
LEFT JOIN family_histories fh ON f.id = fh.family_id
AND fh.created_at =
  (SELECT MAX(created_at)
   FROM family_histories
   WHERE family_id = f.id
     AND family_history_cat_id = 1422)
AND fh.family_history_cat_id = 1422
WHERE 1=0
  OR (f.family_status_cat_id = 1422
      AND (  (SELECT MAX(_fh.created_at)
              FROM family_histories _fh
              WHERE _fh.family_history_cat_id = 1422
                AND _fh.family_id = f.id
                AND _fh.new_val LIKE '%1422%') BETWEEN '2010-03-10' AND '2011-03-02'))

Am I missing something? Synatx is correct, so what is the problem?

我错过了什么吗? Synatx是正确的,那么问题是什么?

1 个解决方案

#1

I think the problem is that you have to much subqueries. It has to take a lot of time.

我认为问题是你必须有很多子查询。它需要花费很多时间。

My recommendation would be to store some of the information you get by subqueries in the main table.

我的建议是将子查询中获得的一些信息存储在主表中。

In alternative you can always try to make a store procedure to speed up the process.

或者,您可以尝试制作商店程序以加快流程。

Best regards

#1