[SQL ] mysql greatest N per group

[cklonger (22)]

Mysql版本 5.1

table rows 2億8千萬筆
table name :log

schema
`time`: datetime
`dom`: vchar
`src`: vchar
`dep`:vchar
`acc`:vchar

需求:想要找到 dep='a' 下 group by dom ,src

每個group中 time最新的十筆


sql:

SELECT time, dom, src,dep,acc
   FROM

(SELECT time, dom, src,dep,acc ,

                  @country_rank := IF(@current_dom = dom
                  and @current_src = src, @country_rank + 1, 1) AS country_rank,

                  @current_dom := dom ,

                  @current_src :=  src

                  FROM log where dep = 'a' and time >date('2013-01-10')


                 ORDER BY dom asc, time DESC
     ) ranked

                 WHERE country_rank <= 10


現在的問題是SQL跑不完 直接time out

請問各位先進有什麼地方可以修改的嗎

--
※ 發信站: 批踢踢實業坊(ptt.cc) 
◆ From: 60.251.42.235