我用explain SELECT sum(num) num, grp_id, user_id, type
FROM (SELECT r.grp_id, m.message_type type, count(*) num, r.user_id
FROM sms_send s, sms_recv r, message_info m
WHERE r.recv_time >= '2011-07-01 00:00:00'
and r.recv_time < '2011-07-02 00:00:00'
AND s.message_id = m.message_id
AND m.message_type IN (0, 1, 2, 3, 4, 5)
and s.dest_addr = r.org_addr
group by r.grp_id, m.message_type, r.user_id
union all
select s.grp_id, m.message_type type, count(*) num, s.user_id
from sms_send s, message_info m
where s.message_id = m.message_id
and s.send_time >= '2011-07-01 00:00:00'
and s.send_time < '2011-07-02 00:00:00'
group by s.grp_id, m.message_type, s.user_id) grs
group by grp_id, type, user_id
发现explain了已经300多秒过去了,也没有出结果是啥原因啊,
我单独exlain SELECT r.grp_id, m.message_type type, count(*) num, r.user_id
FROM sms_send s, sms_recv r, message_info m
WHERE r.recv_time >= '2011-07-01 00:00:00'
and r.recv_time < '2011-07-02 00:00:00'
AND s.message_id = m.message_id
AND m.message_type IN (0, 1, 2, 3, 4, 5)
and s.dest_addr = r.org_addr
group by r.grp_id, m.message_type, r.user_id
和exlain SELECT s.grp_id, m.message_type type, count(*) num, s.user_id
from sms_send s, message_info m
where s.message_id = m.message_id
and s.send_time >= '2011-07-01 00:00:00'
and s.send_time < '2011-07-02 00:00:00'
group by s.grp_id, m.message_type, s.user_id
都没问题,而且在另外的一个数据库中,数据量和上面的那个数据库的数据量差不多,explain很快就出来结果,为什么在上面那个数据库就不行呢,而且我还特别执行了analyze 这三张表,都一样的结果。
谁能帮俺解决一下啊。
FROM (SELECT r.grp_id, m.message_type type, count(*) num, r.user_id
FROM sms_send s, sms_recv r, message_info m
WHERE r.recv_time >= '2011-07-01 00:00:00'
and r.recv_time < '2011-07-02 00:00:00'
AND s.message_id = m.message_id
AND m.message_type IN (0, 1, 2, 3, 4, 5)
and s.dest_addr = r.org_addr
group by r.grp_id, m.message_type, r.user_id
union all
select s.grp_id, m.message_type type, count(*) num, s.user_id
from sms_send s, message_info m
where s.message_id = m.message_id
and s.send_time >= '2011-07-01 00:00:00'
and s.send_time < '2011-07-02 00:00:00'
group by s.grp_id, m.message_type, s.user_id) grs
group by grp_id, type, user_id
发现explain了已经300多秒过去了,也没有出结果是啥原因啊,
我单独exlain SELECT r.grp_id, m.message_type type, count(*) num, r.user_id
FROM sms_send s, sms_recv r, message_info m
WHERE r.recv_time >= '2011-07-01 00:00:00'
and r.recv_time < '2011-07-02 00:00:00'
AND s.message_id = m.message_id
AND m.message_type IN (0, 1, 2, 3, 4, 5)
and s.dest_addr = r.org_addr
group by r.grp_id, m.message_type, r.user_id
和exlain SELECT s.grp_id, m.message_type type, count(*) num, s.user_id
from sms_send s, message_info m
where s.message_id = m.message_id
and s.send_time >= '2011-07-01 00:00:00'
and s.send_time < '2011-07-02 00:00:00'
group by s.grp_id, m.message_type, s.user_id
都没问题,而且在另外的一个数据库中,数据量和上面的那个数据库的数据量差不多,explain很快就出来结果,为什么在上面那个数据库就不行呢,而且我还特别执行了analyze 这三张表,都一样的结果。
谁能帮俺解决一下啊。
解决方案 »
- 用Mysql还原.sql文件?.sql文件在其他文件夹
- 难度MYSQL之数据丢失,泥煤啊
- 百万级别limit的优化
- 求一条sql,语句,高分,ACMAIN_CHM,WWWWA进来帮看一下,谢谢
- mysql插入数据为什么很慢?
- Excel 数据导入Mysql数据库中文乱码问题
- 三表group by 急。。。。救解~!!!
- 罕见问题::关于远程连库的问题
- windows 操作系统下如何安装 mysql_devel开发包?这个开发包在哪可以下载?我想用mqsql.h,谢谢
- 关于删除数据库中重复字段的问题
- 为什么MySQL 是小型数据库
- 请各位介绍几本学习数据库的经典教程,国内外的都可以,谢谢啦!!!
sms_send有上千万的数据,sms_recv不太多,只有十万左右,
sms_recv的recv_time和org_addr字段建有索引,sms_send的dest_addr字段没有建立索引,
因为mysql的优化模式基本基于CBO模式,
当没有子查询的时候,对于优化器影响不大,当出现子查询的时候,
exlain select * from (SELECT r.grp_id, m.message_type type, count(*) num, r.user_id
FROM sms_send s, sms_recv r, message_info m
WHERE r.recv_time >= '2011-07-01 00:00:00'
and r.recv_time < '2011-07-02 00:00:00'
AND s.message_id = m.message_id
AND m.message_type IN (0, 1, 2, 3, 4, 5)
and s.dest_addr = r.org_addr
group by r.grp_id, m.message_type, r.user_id) t
这种情况CBO计算的时候估计确实需要很长时间。
FROM sms_send s, sms_recv r, message_info m
WHERE r.recv_time >= '2011-07-01 00:00:00'
and r.recv_time < '2011-07-02 00:00:00'
AND s.message_id = m.message_id
AND m.message_type IN (0, 1, 2, 3, 4, 5)
and s.dest_addr = r.org_addr
group by r.grp_id, m.message_type, r.user_id
union all
select s.grp_id, m.message_type type, count(*) num, s.user_id
from sms_send s, message_info m
where s.message_id = m.message_id
and s.send_time >= '2011-07-01 00:00:00'
and s.send_time < '2011-07-02 00:00:00'
group by s.grp_id, m.message_type, s.user_id