How to make faster group by in mysql very big table?

Question

I have got tables in MySQL like thoose:

troll_id     troll_family_id    troll_name    troll_birth_timestamp
--------------------------------------------------------------------
1            1                  Kamil         100000
2            1                  Bartek        200000
3            2                  Maciek        100000
4            2                  Andrzej       200000

troll_family_id    troll_family_name
------------------------------------------
1                  Trollowski
2                  Trollowicz

I want get only one the youngest Troll from every famillies

so I want to get rows like that:

troll.troll_name
-----------------    
Bartek
Andrzej

I have got few millions of trolls in DB, I tried query like that:

SELECT troll.troll_name 
FROM troll 
GROUP BY troll.troll_family_id 
ORDER BY troll.troll_birth_timestamp DESC
LIMIT 0, 50

But it freezes my response for http. So I do not even know, is this query correct.

Answer 1

Try this:

SELECT a.troll_name 
FROM troll a 
INNER JOIN (SELECT troll_family_id, MAX(troll_birth_timestamp) AS troll_birth_timestamp 
            FROM troll 
            GROUP BY troll_family_id
           ) AS b on a.troll_family_id = b.troll_family_id AND a.troll_birth_timestamp = b.troll_birth_timestamp
LIMIT 0, 50