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

[phoenix_pj]

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

[phoenix_pj]

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

[ACMAIN_CHM]

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

[ACMAIN_CHM]

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

[phoenix_pj]

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

[phoenix_pj]

建过了，已经，效率最高的是这个


(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

[ACMAIN_CHM]

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

[phoenix_pj]

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

[ACMAIN_CHM]

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;
贴出来看一下结果是什么？

[phoenix_pj]

你是指查一个类别的吗？

[phoenix_pj]

47ms

[ACMAIN_CHM]

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;
的结果

[phoenix_pj]

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

[nianzhang747]

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引擎

[ACMAIN_CHM]

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; 吗？好象并不一样啊！

[phoenix_pj]

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

[phoenix_pj]

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

[ACMAIN_CHM]

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


你说过这个索引已经建了，不过从你的show index 上看不来出啊！

建议楼主尽可能提供真实的信息，否则别人很难仅靠猜测或不准确的信息来分析。

[phoenix_pj]

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

[ACMAIN_CHM]

create index idx_table_test on `table` ('type','update')

你的索引中根本没有这个复合索引，只有两个单独的索引！
a_t 1 active_time
active_h_type 1 active_type

[phoenix_pj]

为了，表示谢意，特贴出其他几种方案以供参考，再次表示感谢
方案 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的数量较少，而表中的纪录数较多