select distinct(a.circulationno) circulationno, a.userid userid, c.empname username, a.subject subject, a.content content, a.savingperiod savingperiod, a.attachflag attachflag, a.status status, a.createtime createtime, '1' timepass, '0' candelete from tb_circulation a, tb_circulationlabel b, tb_empbaseinfo c where a.circulationno=b.circulationno and a.status = '1' and a.userid=c.id and (a.userid=509 or (b.labeledflag = '1' and readerid=509)) order by a.createtime desctb_circulation表有8000条记录
tb_circulationlabel表有220000条记录
tb_empbaseinfo表有500条记录
已在a,b中circulationno字段建立索引
现在执行上面这条sql语句需要5秒钟,系统响应显得很慢,以前大概是不到1秒,最近变慢了。大伙帮看看有没有办法来优化使它快一点。sql语句是固定在程序中了,不能动,现在只能从数据库着手。 另外,听说此版有位狼头大哥是高手,能否抽空赐教?谢谢!
tb_circulationlabel表有220000条记录
tb_empbaseinfo表有500条记录
已在a,b中circulationno字段建立索引
现在执行上面这条sql语句需要5秒钟,系统响应显得很慢,以前大概是不到1秒,最近变慢了。大伙帮看看有没有办法来优化使它快一点。sql语句是固定在程序中了,不能动,现在只能从数据库着手。 另外,听说此版有位狼头大哥是高手,能否抽空赐教?谢谢!
解决方案 »
- FreeBSD下面安装mysql问题***急在线等。
- mysql AUTO_INCREMENT 字段 转成字符型输出,我用的asp
- postgresql 如何批量更新数据库,有这么难吗!!!
- mysql已经装好,但是PHP无法连接!望高手赐教!
- 请看一下这个sql语句错在哪里
- 这个问题怎么解决
- mysql中Blob字段怎么存取?????
- 往数据库text类型字段 加数据出现问题com.mysql.jdbc.exceptions.MySQLSyntaxErrorException:
- 求助MYSQL查询2张表
- 关于mysql字段的非null的不熟悉 虚心请教大婶们 NOT NULL ==?NOT NULL DEFAULT ''
- 能否一条语句实现?
- 初学mysql,买了个虚拟主机空间,用什么客户端管理最好呢?
readerid是表tb_circulationlabel中的mysql> explain select distinct(a.circulationno) circulationno, a.userid userid, c.empname username, a.subject subject, a.content content, a.savingperiod savingperiod, a.attachflag attachflag, a.status status, a.createtime createtime, '1' timepass, '0' candelete from tb_circulation a, tb_circulationlabel b, tb_empbaseinfo c where a.circulationno=b.circulationno and a.status = '1' and a.userid=c.id and (a.userid=509 or (b.labeledflag = '1' and readerid=509)) order by a.createtime desc;
+-------+------+-------------------------------------------------------------------------------------------------+--------------------------------------------------------------+---------+-----------------+------+------------------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+-------------------------------------------------------------------------------------------------+--------------------------------------------------------------+---------+-----------------+------+------------------------------------+
| c | ALL | PRIMARY | NULL | NULL | NULL | 624 | Using temporary; Using filesort |
| a | ref | PRIMARY,tb_circulation_userid_status_createtime_index,circulationno | tb_circulation_userid_status_createtime_index | 5 | c.id,const | 8 | Using where |
| b | ref | tb_circulationlabel_readerid_index,tb_circulationlabel_circulationno_readerid_labeledflag_index | tb_circulationlabel_circulationno_readerid_labeledflag_index | 4 | a.circulationno | 17 | Using where; Using index; Distinct |
+-------+------+-------------------------------------------------------------------------------------------------+--------------------------------------------------------------+---------+-----------------+------+------------------------------------+
3 rows in set
分别是int,char
已建索引
show variables like ''1,增加临时表大小
2,增加排序缓存。--查看临时表大小,byte单位
show variables like '%tmp_table_size%'
--查看排序缓存大小
show variables like '%sort_buffer_size%'楼主看看这2个参数是多少?
;
+----------------+----------+
| Variable_name | Value |
+----------------+----------+
| tmp_table_size | 33554432 |
+----------------+----------+
1 row in setmysql> show variables like '%sort_buffer_size%'
;
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| myisam_sort_buffer_size | 8388608 |
| sort_buffer_size | 2097144 |
+-------------------------+---------+
2 rows in setmysql>
恭喜,恭喜,你会调整mysql参数了,表明你已经初步窥探了mysql。