如何提高这个查询的性能？

Question

select m.ID,m.cell_number,cm.id 
from corporate_main_member m
   , corporate_main_corporate_membership cm 
WHERE m.status = 'active' 
   AND m.ID IN (
      select cm.FK_Member_ID 
      from corporate_main_corporate_membership cm 
      WHERE cm.status = 'active' 
        AND cm.ID IN (
           select gm.FK_Corporate_Membership_ID 
           from corporate_main_group_membership gm 
           WHERE gm.status = 'active' 
             AND gm.FK_Group_id IN (168,169,170)
             Group BY gm.FK_Corporate_Membership_ID
           )
      ) 
   AND cm.fk_corporation_id = 5 
   AND cm.FK_Member_ID = m.id

在以下位置：

1 Million records of corporate_main_member 
2 Million records of corporate_main_corporate_membership
3 Million records of corporate_main_group_membership

上面的查询停止了mysql！它一直工作到前天，我使用脚本向corporate_main_member和corporate_main_corporate_membership表添加了数千条记录。不确定是否相关或数据库大小超过了某个阈值，从而导致严重的性能问题。

有什么帮助吗？我对索引或其他东西一无所知。模式是显而易见的。

Answer 1

试试这个，

SELECT  m.ID,
        m.cell_number,
        cm.id
FROM    corporate_main_member m
        INNER JOIN corporate_main_corporate_membership cm
            ON  cm.FK_Member_ID = m.id  AND
                cm.fk_corporation_id = 5
        INNER JOIN
        (
            SELECT  ccm.FK_Member_ID
            FROM    corporate_main_corporate_membership ccm
                    INNER JOIN corporate_main_group_membership gm
                        ON ccm.ID = gm.FK_Corporate_Membership_ID
            WHERE   ccm.STATUS = 'active' AND
                    gm.STATUS = 'active' AND
                    gm.FK_Group_id IN ( 168, 169, 170 )
            GROUP   BY ccm.FK_Member_ID
        ) s ON  m.ID = s.FK_Member_ID
WHERE   m.STATUS = 'active'