CODEWITHSUNDEEP
mysqlsqlselectgroup-bygreatest-n-per-group
user3766478
user3766478

Reputation: 691

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

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.

Upvotes: 3

Views: 1188

Answers (3)

Ahmad
Ahmad

Reputation: 9658

You can use MAX function on groups

SELECT troll.troll_name, MAX(troll_birth_timestamp) AS troll_birth_timestamp
FROM troll 
GROUP BY troll.troll_family_id 
LIMIT 0, 50

Upvotes: 0

avisheks
avisheks

Reputation: 1180

Slight Changes to your existing query. ORDER BY works after GROUP BY:

SELECT troll_family_name, t.troll_name 
FROM
(
  SELECT troll_family_name, t.troll_name 
  FROM troll t
  JOIN troll_family tf ON tf.troll_family_id=t.troll_family_id
  ORDER BY t.troll_birth_timestamp DESC      
)temp
GROUP BY troll_family_name
LIMIT 0, 50

More over add indexes to tf.troll_family_id, t.troll_family_id

Upvotes: 0

Saharsh Shah
Saharsh Shah

Reputation: 29051

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

Upvotes: 3

Related Questions