How to make a faster greatest-n-per-group query?

Question

I am using this query:

SELECT district, id
FROM adverts ls
GROUP BY district, id
HAVING (

SELECT count( * )
FROM adverts
WHERE district = ls.district
AND id > ls.id
) <5
ORDER BY district, id DESC ;

LIMIT 0 , 30

It tooks about 35 seconds. Here is the explanation:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   PRIMARY     ls  range   NULL    i_id    5   NULL    16166   Using index for group-by; Using temporary; Using f...
2   DEPENDENT SUBQUERY  ilan_genel  ref     PRIMARY,i_id,i_ozellik_id,i_tip_fiyat,i_tip_id,i_d...   i_durum_id  2   func    25  Using where; Using index 

Is there a way to make it faster ?

Answer 1

try this:

SELECT    district, id, COUNT(b.district)
FROM      adverts a INNER JOIN adverts b
              ON a.district = b.district
WHERE     b.id > a.id
GROUP BY  district, id 
HAVING    COUNT(b.district) < 5
ORDER BY  district, id DESC 

by definition, Joins are faster than subqueries.