MySql on what cols should I put indexes?

Question

I have this query:

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' )

My problem is very specific. It is regarding the line:

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

When I use EXPLAIN, it seems that this query is bad. I get for this table (c = contacts): 38307 rows. On what columns should I put the index according to the query? I tried mobile1 - but no improvement (BTW - family_id is indexed in the contacts table).

I attach the image of the explain result: EXPLAIN

Or maybe someone can help me optimize the query...

On the id's. If they are keys, they are already indexed. Your image is too small to read. — mawburn, Mar 11 '13 at 15:13
@coderseven i wouldn't say it's too small to read, but it is painful on the eyes — Lloyd Banks, Mar 11 '13 at 15:14
@LloydBanks http://i.stack.imgur.com/8bSXn.png It's bigger actually. — mawburn, Mar 11 '13 at 15:15
@CoderSeven - so if they are already indexed and they are, I don't understand from your answer where to put more... — Noam B., Mar 11 '13 at 15:15
You put indexes on things you're looking for. You're looking for them based on their IDs, therefore the IDs should be indexed. For instance, indexing a person's hair color isn't going to do anything if you're looking for people based on their name. But if you want to find everyone who has brown hair, indexing their hair color would be a great help. — mawburn, Mar 11 '13 at 15:17
@CoderSeven - But as you said - they ARE indexed. All the ids ARE! — Noam B., Mar 11 '13 at 15:20
Right.... since they are keys and already indexed, indexing something else isn't going to help you. They aren't magic solutions to speed up queries. — mawburn, Mar 11 '13 at 15:21

score 0 · Answer 1 · answered Mar 11 '13 at 15:21

0

Any column you'll be searching on, to speed up the process. Keep in mind that keys are already indexed.

answered Mar 11 '13 at 15:21

ktm5124

11,861
21
74
119

score 0 · Answer 2 · answered Mar 11 '13 at 15:39

0

Well, it seems that using the GROUP_CONCAT is the problem. I just seperated the wife and husband mobile to be 2 different columns. First, I thought that using the GROUP_CONCAT will be faster, but it proved to be VERY WRONG.

answered Mar 11 '13 at 15:39

Noam B.

3,120
5
25
38

score 0 · Answer 3 · answered Mar 11 '13 at 18:00

0

Just out of my curiosity, what is the performance of the query

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

answered Mar 11 '13 at 18:00

georgecj11

1,600
15
22

MySql on what cols should I put indexes?

3 Answers3