mysql查询需要很多时间
问题描述:
在rails中的这个查询不会结束。为什么? 查询是:mysql查询需要很多时间
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'))
我是否错过了什么? Synatx是正确的,那么问题是什么?
答
我认为问题是你必须进行很多子查询。它需要花费很多时间。
我的建议是将你通过子查询得到的一些信息存储在主表中。
另外,您可以随时尝试创建存储过程以加快过程。
致以问候
你的'EXPLAIN'说什么? – 2013-03-06 17:21:55
表索引?每个表有多少条记录?我们不能仅仅基于粘贴的查询(甚至没有格式化)来回答为什么表现不如您期望的那么好。 – 2013-03-06 17:23:44
您的个人查询是否成功运行?我会把这个调整到最低限度,然后从那里开始工作 – 2013-03-06 17:24:06