如何读取点击量排前十的博客文章,要求每个人最多只有一篇id userid title num_click updated
1 11 文章1 100 1272519012
2 12 文章2 200 1272519012
3 13 文章3 300 1272519012
4 13 文章4 400 1272519012应该读出 id为 1,2,4 对应的文章以上是举例子,因为我的数据有10万多条数据
请问sql 语句怎么写速度更好,最好不出现慢查询
1 11 文章1 100 1272519012
2 12 文章2 200 1272519012
3 13 文章3 300 1272519012
4 13 文章4 400 1272519012应该读出 id为 1,2,4 对应的文章以上是举例子,因为我的数据有10万多条数据
请问sql 语句怎么写速度更好,最好不出现慢查询
from tb k
where id=(select max(num_click) from tb where k.userid=userid)
order by num_click desc limit 10
from table1 a inner join (select userid,max(num_click) as num_click from table1 group by userid) b
on a.userid=b.userid and a.num_click=b.num_click
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 462
1 PRIMARY a ref aid,num_click num_click 2 b.num_click 4 Using where
2 DERIVED space_article ALL NULL NULL NULL NULL 15824 Using temporary; Using filesort如何建索引呢?
FROM article a
INNER JOIN (SELECT userid, max( num_click ) AS num_click
FROM article
GROUP BY userid order by num_click desc limit 10
)b ON a.userid = b.userid
AND a.num_click = b.num_click limit 10
得到的结果如下:唯一的遗憾是下面的最后一行,仍要扫描全表。id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 10
1 PRIMARY a ref num_click num_click 5 b.userid,b.num_click 1
2 DERIVED article index NULL num_click 5 NULL 100000 Using index; Using temporary; Using filesort 索引如下:
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
article 0 PRIMARY 1 id A 100000 NULL NULL BTREE
article 1 num_click 1 userid A 33333 NULL NULL BTREE
article 1 num_click 2 num_click A 100000 NULL NULL BTREE
请问还有好的优化方法吗?