select b.sessionkey
from dbaudit_fd0005864b8e3214_20100919 b ,
dbauditalarm_fd0005864b8e3214_20100919 c
where b.alarmkey>0
and c.ruleid in (1,-1) and b.alarmkey=c.alarmkey
and b.dt>=1284825600 and b.dt<=1284907022 and 1=1
order by b.dt limit 1000
以上是我的查询语句,说明下:
1.我不使用order by 可以实现我需要的结果,但是如果我需要倒序怎么办,那就非常慢了
2.由于记录表中我需要查询的记录是在表最后的记录,所以直接用倒序很蛮,但如果记录在刚开始的记录中的话,我使用倒序就很慢了。
大家帮忙想想办法
from dbaudit_fd0005864b8e3214_20100919 b ,
dbauditalarm_fd0005864b8e3214_20100919 c
where b.alarmkey>0
and c.ruleid in (1,-1) and b.alarmkey=c.alarmkey
and b.dt>=1284825600 and b.dt<=1284907022 and 1=1
order by b.dt limit 1000
以上是我的查询语句,说明下:
1.我不使用order by 可以实现我需要的结果,但是如果我需要倒序怎么办,那就非常慢了
2.由于记录表中我需要查询的记录是在表最后的记录,所以直接用倒序很蛮,但如果记录在刚开始的记录中的话,我使用倒序就很慢了。
大家帮忙想想办法
show index from dbaudit_fd0005864b8e3214_20100919;
show index from dbauditalarm_fd0005864b8e3214_20100919;
explain select b.sessionkey
from dbaudit_fd0005864b8e3214_20100919 b ,
dbauditalarm_fd0005864b8e3214_20100919 c
where b.alarmkey>0
and c.ruleid in (1,-1) and b.alarmkey=c.alarmkey
and b.dt>=1284825600 and b.dt<=1284907022 and 1=1
order by b.dt limit 1000;三个语句的结果。
explain结果如下:
1 SIMPLE b range dt_dbaudit_fd0005864b8e3214_20100919,alarmkey_dbaudit_fd0005864b8e3214_20100919 dt_dbaudit_fd0005864b8e3214_20100919 9 null 14979235 Using where1 SIMPLE c ref alarmkey_dbauditalarm_fd0005864b8e3214_20100919 alarmkey_dbauditalarm_fd0005864b8e3214_20100919 9 heidunlog.b.alarmkey 1 Using where
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
dbauditalarm_fd0005864b8e3214_20100919 0 PRIMARY 1 id A 262964 BTREE
dbauditalarm_fd0005864b8e3214_20100919 1 alarmkey_dbauditalarm_fd0005864b8e3214_20100919 1 alarmkey A 262964 YES BTREE
dbaudit_fd0005864b8e3214_20100919 0 PRIMARY 1 id A 27209342 BTREE
dbaudit_fd0005864b8e3214_20100919 1 dt_dbaudit_fd0005864b8e3214_20100919 1 dt A 52025 YES BTREE
dbaudit_fd0005864b8e3214_20100919 1 alarmkey_dbaudit_fd0005864b8e3214_20100919 1 alarmkey A 21 YES BTREE