Java开发网 Java开发网
注册 | 登录 | 帮助 | 搜索 | 排行榜 | 发帖统计  

您没有登录

» Java开发网 » Database/JDBC/SQL/JDO/Hibernate » MySQL  

按打印兼容模式打印这个话题 打印话题    把这个话题寄给朋友 寄给朋友    该主题的所有更新都将Email到你的邮箱 订阅主题
flat modethreaded modego to previous topicgo to next topicgo to back
作者 Mysql explain的Using where; Using temporary; Using filesort问题
syvin





发贴: 41
积分: 1
于 2007-12-25 20:11 user profilesend a private message to usersearch all posts byselect and copy to clipboard. ie only, sorry for netscape users:-)add this post to my favorite list
同样一个sql语句，在本地explain和网上explain结果不一致，使用网上数据过程中，开始非常快，没有filesort问题，说明网上也是按照本地的explain方式解析的，20分钟后变得很慢了。（整个过程是连续的查询，一次50条）

不知道哪位大虾知道为什么么？

测试环境——
mysql> explain SELECT ssi.catid,ssi.dateline,ssi.subject,ssi.type,ssi.uid,ssi.username,ssb.* FROM supe_spaceitems as ssi,supe_spaceblogs as ssb WHERE ssi.itemid>427091 AND ssi.itemid=ssb.itemid ORDER BY ssi.itemid LIMIT 50;
+----+-------------+-------+--------+---------------+---------+---------+-------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+-------+-------------+
| 1 | SIMPLE | ssi | range | PRIMARY | PRIMARY | 3 | NULL | 89664 | Using where |
| 1 | SIMPLE | ssb | eq_ref | PRIMARY | PRIMARY | 3 | blogdb.ssi.itemid | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+-------+-------------+
mysql> select countStart from supe_spaceblogs;
+----------+
| countStart |
+----------+
| 372948 |
+----------+
1 row in set (0.00 sec)

mysql> select countStart from supe_spaceitems;
+----------+
| countStart |
+----------+
| 496222 |
+----------+
1 row in set (0.00 sec)

网上环境——
mysql> explain SELECT ssi.catid,ssi.dateline,ssi.subject,ssi.type,ssi.uid,ssi.username,ssb.* FROM supe_spaceitems as ssi,supe_spaceblogs as ssb WHERE ssi.itemid>426945 AND ssi.itemid=ssb.itemid ORDER BY ssi.itemid LIMIT 50;
+----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+----------------------------------------------+
| 1 | SIMPLE | ssb | range | PRIMARY | PRIMARY | 3 | NULL | 237193 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | ssi | eq_ref | PRIMARY | PRIMARY | 3 | blogdb.ssb.itemid | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+----------------------------------------------+
2 rows in set (0.68 sec)

mysql> select countStart from supe_spaceblogs;
+----------+
| countStart |
+----------+
| 489008 |
+----------+
1 row in set (0.00 sec)

mysql> select countStart from supe_spaceitems;
+----------+
| countStart |
+----------+
| 640462 |
+----------+
1 row in set (0.33 sec)

syvin edited on 2007-12-25 20:19

作者 Re:Mysql explain的Using where; Using temporary; Using filesort问题 [Re:syvin]
syvin





发贴: 41
积分: 1
于 2007-12-25 20:42 user profilesend a private message to usersearch all posts byselect and copy to clipboard. ie only, sorry for netscape users:-)add this post to my favorite list
太奇怪了，改成：ORDER BY ssb.itemid 就可以了。
太没有道理了。



flat modethreaded modego to previous topicgo to next topicgo to back
  已读帖子
  新的帖子
  被删除的帖子
Jump to the top of page

   Powered by Jute Powerful Forum® Version Jute 1.5.6 Ent
Copyright © 2002-2021 Cjsdn Team. All Righits Reserved. 闽ICP备05005120号-1
客服电话 18559299278    客服信箱 714923@qq.com    客服QQ 714923