SQL查询速度超级慢，怎样改进？

[KagamineLenKai2]

有一个contacts表，表中有一列为relation_type，relation_type有'spouse','lineal','otherRelation'三个值，我想根据这三个值把contacts分成三个组，然后join到主表里。我写的SQL如下，但是查询速度特别特别慢，十多分钟都没出结果。请问该如何改进？
SELECT
apply.client_id,
c1.contacts_name AS 联系人1姓名,
c1.relation AS 联系人1关系,
c2.contacts_name AS 联系人2姓名,
c2.relation AS 联系人2关系,
c3.contacts_name AS 联系人3姓名,
c3.relation AS 联系人3关系
from apply
LEFT JOIN (
SELECT
contacts.client_id,
contacts.contacts_name,
contacts.relation,
contacts.relation_type
FROM
contacts
WHERE
contacts.relation_type = 'spouse'
) c1 ON c1.client_id = apply.client_id
LEFT JOIN (
SELECT
contacts.client_id,
contacts.contacts_name,
contacts.relation,
contacts.relation_type
FROM
contacts
WHERE
contacts.relation_type = 'lineal'
) c2 ON c2.client_id = apply.client_id
LEFT JOIN (
SELECT
contacts.client_id,
contacts.contacts_name,
contacts.relation,
contacts.relation_type
FROM
contacts
WHERE
contacts.relation_type = 'otherRelation'
) c3 ON c3.client_id = apply.apply_id

[二月十六]

有索引么？把索引情况也说一下吧

[KagamineLenKai2]

引用 1 楼 sinat_28984567 的回复:

有索引么？把索引情况也说一下吧

不清楚，我不是DBA……

[AAACCCEEEFFFA]

group by + case

[0与1之间]

看看你的执行计划吧