如何进行最快的每组最大n查询? [英] How to make a faster greatest-n-per-group 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

大约花了35秒.这是解释:

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 

有没有办法使其更快?

推荐答案

尝试一下:

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 

根据定义，Joins比subqueries 更快.

这篇关于如何进行最快的每组最大n查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！