MySql我应该把索引放在什么cols上？

Question

我有这样的疑问：

SELECT Concat(f.name, ' ', f.parent_names) AS FullName, 
       stts.name                           AS 'Status', 
       u.name                              AS Unit, 
       city.name                           AS City, 
       (SELECT Group_concat(c.mobile1) 
        FROM   contacts c 
        WHERE  c.id = f.husband_id 
                OR c.id = f.wife_id)       AS MobilePhones, 
       f.phone                             AS HomePhone, 
       f.contact_initiation_date           AS InitDate, 
       f.status_change_date                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 comments cmt 
              ON f.last_comment_id = cmt.id 
WHERE  1 = 0 
        OR ( u.is_busy = 1 ) 
        OR ( f.family_status_cat_id = 1423 ) 
        OR ( f.family_status_cat_id = 1422 
             AND f.status_change_date BETWEEN '2011-03-21' AND '2012-03-13' ) 

我的问题非常具体。它是关于这条线的：

 SELECT GROUP_CONCAT( c.mobile1 ) 
    FROM contacts c
    WHERE c.id = f.husband_id
    OR c.id = f.wife_id
    ) AS MobilePhones

当我使用EXPLAIN时，这个查询似乎是错误的。我得到这个表(c =联系人)：38307行。根据查询，我应该将索引放在哪些列上？我尝试了mobile1 -但没有任何改进(BTW - family_id在contacts表中被索引)。

我附上解释结果的图像：

​

或者也许有人可以帮我优化查询。

Answer 1

您要搜索的任何列，以加快搜索过程。请记住，键已经建立了索引。

Answer 2

嗯，看起来使用GROUP_CONCAT是个问题。我刚把妻子和丈夫的手机分成两个不同的栏目。首先，我认为使用GROUP_CONCAT会更快，但事实证明这是非常错误的。

Answer 3

出于我的好奇，查询的性能如何？

SELECT GROUP_CONCAT( c.mobile1 ) 
    FROM contacts c
    WHERE c.id IN(f.husband_id, f.wife_id)
    ) AS MobilePhones