查询按类别分组后每组的最新20条数据

Question

表结构如下
CREATE TABLE `table` (                                             
            `id` bigint(20) NOT NULL auto_increment,                           
             'type' int(11),
             'update' date 
            ）            
查询按type分组后，每组的最新20条数据              

Answer 1

这个我已经有2个实现了的sql了，但是效率太差，数据库里10w条数据，跑了400多ms
所以，希望找到其他效率较高的sql

Answer 2

select *
from `table` a
where 20> (select count(*) from `table` where 'type'=a.'type' and 'update'>a.'update')

Answer 3

创建一下索引 
create index idx_table_test on `table` ('type','update')

Answer 4

这个的效率是最低的，我得mysql直接卡在那里不动了

Answer 5

建过了，已经，效率最高的是这个
(select * from table  where type = 1 order by update desc limit 20)
union all
(select * from table  where type = 2 order by update desc limit 20)
union all
(select * from table  where type = 3 order by update desc limit 20)
这个花了400多ms

Answer 6

#2楼 的时间复杂度是 O^2
而你  #5楼 的时间复杂度是 n*O, 所以#5楼 的显然快。 但前提是你需要事先知道distinct type 的 ，这样就不是通过一条SQL语句来实现。 你可以在你的程序中或者在存储过程中来通过分步来实现。

Answer 7

type是已知的，而且就是固定10个type，这两个sql我都跑了，10w条数据，时间就是5#的快些，版主救救我呀，小女子感激不尽~~

Answer 8

select * from table  where type = 1 order by update desc limit 20这个花了多久？ 40ms ?explain select * from table  where type = 1 order by update desc limit 20;
贴出来看一下结果是什么？

Answer 9

select * from table  where type = 1 order by update desc limit 20这个已经需要 47ms 了，你查10个自然需要 400 ms 左右。如果还想看看有没有继续优化的可能，则需要看一下你的 
explain select * from table  where type = 1 order by update desc limit 20;
的结果

Answer 10

id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra
1 |SIMPLE |active_h |ref |active_h_type| active_h_type |5 |const |17616 |Using where; Using filesort

Answer 11

Condition pushdown capability is not used by default. To enable it, you can start mysqld with the --engine-condition-pushdown option, or you can execute either of the following statements at runtime: 
SET engine_condition_pushdown=ON;
SET engine_condition_pushdown=1;前提是你把type和update索引去掉如此设置试试看
但是在5.1版本的mysql 只支持DNBCLUSTER引擎

Answer 12

id|select_type|table    |type|possible_keys|key            |key_len|ref   |rows     |Extra
1 |SIMPLE     |active_h |ref |active_h_type| active_h_type |5      |const |17616 |Using where; Using filesort有 Using filesort ?show index from `table` 看一下，另外你的这个 explain 是 explain select * from table  where type = 1 order by update desc limit 20; 吗？好象并不一样啊！

Answer 13

感谢13楼，我得mysql 版本是5.0的，而且我用的是InnoDB引擎

Answer 14

就是这样子的，帖子里发的，只是个例子表，但是，我用的表，也是这样的，很简单的表，只是数据多，
Key_name       Seq_in_index  Column_name  
--------  ------  ------  ----------  -------
PRIMARY                1  id          
a_t                    1  active_time  
active_h_type          1  active_type  

Answer 15

你说过这个索引已经建了，不过从你的show index 上看不来出啊！建议楼主尽可能提供真实的信息，否则别人很难仅靠猜测或不准确的信息来分析。

Answer 16

哦，呵呵，我这个 active_time 就是对应 update，active_type  对应 type
谢谢，版主辛苦了~
我又加了个限制条件，让他少差了些数据，就快了，100多ms搞定

Answer 17

create index idx_table_test on `table` ('type','update')你的索引中根本没有这个复合索引，只有两个单独的索引！
a_t          1  active_time  
active_h_type      1  active_type  

Answer 18

为了，表示谢意，特贴出其他几种方案以供参考，再次表示感谢
方案 1：（于版主同）
select * from `table` a where 20> (select count(*) from `table` where 'type'=a.'type' and 'update'>a.'update')方案 2：
select * ,rank from (
select b.*,@rownum:=@rownum+1 ,
if(@btype=b.ype,@rank:=@rank+1,@rank:=1) as rank,
@btype:=b.type
from (
select * from table order by type asc, update desc)
b,(select @rownum :=0 ,@btype:=null,@rank:=0)a ) result where rank<20方案3：
就是我在15楼说的(select * from table  where type = 1 order by update desc limit 20)
union all
(select * from table  where type = 2 order by update desc limit 20)
union all
(select * from table  where type = 3 order by update desc limit 20) 
方案3 的限制是要知道type ，而且type的数量较少，而表中的纪录数较多