select count(0) as num from
(select b.departid,b.hostname,a.id, a.ip, a.deviceid, a.result,a.log,a.events,a.czsj, a.czsj1
from ufilter_log a ,ufilter_info b,sys_org_info c
where a.deviceid = b.id
and b.departid = c.id
and c.orgcode like '00%'
and a.czsj1 >='2000-09-01 00:00:00'
and a.czsj1 <='2010-09-09 23:59:59'
order by a.czsj1 desc ) counttab
ufilter_log表有60W条记录,ufilter_info和sys_org_info都只有200多条记录,
只运行括号里面的语句执行时间大约5秒,整个执行要20秒,有什么办法优化吗,太慢了啊三个表都有主键,下面是我建的索引,看看有没问题?
CREATE INDEX in_ufilter_log ON ufilter_log (czsj1,id,ip,deviceid, events,result,log,czsj);
CREATE INDEX in_ufilter_info ON ufilter_info (id,departid, hostname);
CREATE INDEX in_sys_org_info ON sys_org_info (id,orgcode);
谢谢大家。
(select b.departid,b.hostname,a.id, a.ip, a.deviceid, a.result,a.log,a.events,a.czsj, a.czsj1
from ufilter_log a ,ufilter_info b,sys_org_info c
where a.deviceid = b.id
and b.departid = c.id
and c.orgcode like '00%'
and a.czsj1 >='2000-09-01 00:00:00'
and a.czsj1 <='2010-09-09 23:59:59'
order by a.czsj1 desc ) counttab
ufilter_log表有60W条记录,ufilter_info和sys_org_info都只有200多条记录,
只运行括号里面的语句执行时间大约5秒,整个执行要20秒,有什么办法优化吗,太慢了啊三个表都有主键,下面是我建的索引,看看有没问题?
CREATE INDEX in_ufilter_log ON ufilter_log (czsj1,id,ip,deviceid, events,result,log,czsj);
CREATE INDEX in_ufilter_info ON ufilter_info (id,departid, hostname);
CREATE INDEX in_sys_org_info ON sys_org_info (id,orgcode);
谢谢大家。
from ufilter_info b,sys_org_info c ,ufilter_log a
小表放前面,大表放后面另外增加你的mysql> show variables like '%tmp%';
+-------------------+----------+
| Variable_name | Value |
+-------------------+----------+
| max_tmp_tables | 32 |
| slave_load_tmpdir | /tmp |
| tmp_table_size | 16777216 |
| tmpdir | /tmp |
+-------------------+----------+
4 rows in set (0.08 sec)
tmp_table_size 这个的值。使临时表不要到磁盘建立,在内存建立。
max_tmp_tables 32
slave_load_tmpdir C:\\WINDOWS\\TEMP\\
tmp_table_size 108003328
tmpdir
我的好大啊
tmp_table_size 108003328108003328/1024/1024=103M,其实也不大。
很可能你的临时表超过了,
from ufilter_info b,sys_org_info c,ufilter_log a
where a.czsj1 >='2000-09-01 00:00:00'
and a.czsj1 <='2010-09-09 23:59:59'
and c.orgcode like '00%'
and a.deviceid = b.id
and b.departid = c.id