explain (select 1 as fid, t.*, p.`userid` as ruserid, p.`username` as rusername, p.`ip` as rip from `thread_5422` as t left join `post_5422` as p on p.`id` = t.`pid` where t.`notify`=1 order by t.`top` desc, t.`mtime` desc limit 50000)
-> union all
-> (select 2 as fid, t.*, p.`userid` as ruserid, p.`username` as rusername, p.`ip` as rip from `thread_5428` as t left join `post_5428` as p on p.`id` = t.`pid` where t.`notify`=1 order by t.`top` desc, t.`mtime` desc limit 50000)
-> union all
-> (select 3 as fid, t.*, p.`userid` as ruserid, p.`username` as rusername, p.`ip` as rip from `thread_5440` as t left join `post_5440` as p on p.`id` = t.`pid` where t.`notify`=1 order by t.`top` desc, t.`mtime` desc limit 50000)
-> union all
-> (select 4 as fid, t.*, p.`userid` as ruserid, p.`username` as rusername, p.`ip` as rip from `thread_5423` as t left join `post_5423` as p on p.`id` = t.`pid` where t.`notify`=1 order by t.`top` desc, t.`mtime` desc limit 50000)
-> union all
-> (select 5 as fid, t.*, p.`userid` as ruserid, p.`username` as rusername, p.`ip` as rip from `thread_6265` as t left join `post_6265` as p on p.`id` = t.`pid` where t.`notify`=1 order by t.`top` desc, t.`mtime` desc limit 50000)
-> order by `top` desc,`mtime` desc limit 50000, 50 ;
+----+--------------+------------------+--------+---------------+---------+---------+-----------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------------+--------+---------------+---------+---------+-----------+--------+-----------------------------+
| 1 | PRIMARY | t | ALL | NULL | NULL | NULL | NULL | 132145 | Using where; Using filesort |
| 1 | PRIMARY | p | eq_ref | PRIMARY | PRIMARY | 4 | bbs.t.pid | 1 | |
| 2 | UNION | t | ALL | NULL | NULL | NULL | NULL | 70358 | Using where; Using filesort |
| 2 | UNION | p | eq_ref | PRIMARY | PRIMARY | 4 | bbs.t.pid | 1 | |
| 3 | UNION | t | ALL | NULL | NULL | NULL | NULL | 83826 | Using where; Using filesort |
| 3 | UNION | p | eq_ref | PRIMARY | PRIMARY | 4 | bbs.t.pid | 1 | |
| 4 | UNION | t | ALL | NULL | NULL | NULL | NULL | 4331 | Using where; Using filesort |
| 4 | UNION | p | eq_ref | PRIMARY | PRIMARY | 4 | bbs.t.pid | 1 | |
| 5 | UNION | t | ALL | NULL | NULL | NULL | NULL | 27649 | Using where; Using filesort |
| 5 | UNION | p | eq_ref | PRIMARY | PRIMARY | 4 | bbs.t.pid | 1 | |
|NULL | UNION RESULT | <union1,2,3,4,5> | ALL | NULL | NULL | NULL | NULL | NULL | Using filesort |
+----+--------------+------------------+--------+---------------+---------+---------+-----------+--------+-----------------------------+
11 rows in set (0.00 sec)
-> union all
-> (select 2 as fid, t.*, p.`userid` as ruserid, p.`username` as rusername, p.`ip` as rip from `thread_5428` as t left join `post_5428` as p on p.`id` = t.`pid` where t.`notify`=1 order by t.`top` desc, t.`mtime` desc limit 50000)
-> union all
-> (select 3 as fid, t.*, p.`userid` as ruserid, p.`username` as rusername, p.`ip` as rip from `thread_5440` as t left join `post_5440` as p on p.`id` = t.`pid` where t.`notify`=1 order by t.`top` desc, t.`mtime` desc limit 50000)
-> union all
-> (select 4 as fid, t.*, p.`userid` as ruserid, p.`username` as rusername, p.`ip` as rip from `thread_5423` as t left join `post_5423` as p on p.`id` = t.`pid` where t.`notify`=1 order by t.`top` desc, t.`mtime` desc limit 50000)
-> union all
-> (select 5 as fid, t.*, p.`userid` as ruserid, p.`username` as rusername, p.`ip` as rip from `thread_6265` as t left join `post_6265` as p on p.`id` = t.`pid` where t.`notify`=1 order by t.`top` desc, t.`mtime` desc limit 50000)
-> order by `top` desc,`mtime` desc limit 50000, 50 ;
+----+--------------+------------------+--------+---------------+---------+---------+-----------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------------+--------+---------------+---------+---------+-----------+--------+-----------------------------+
| 1 | PRIMARY | t | ALL | NULL | NULL | NULL | NULL | 132145 | Using where; Using filesort |
| 1 | PRIMARY | p | eq_ref | PRIMARY | PRIMARY | 4 | bbs.t.pid | 1 | |
| 2 | UNION | t | ALL | NULL | NULL | NULL | NULL | 70358 | Using where; Using filesort |
| 2 | UNION | p | eq_ref | PRIMARY | PRIMARY | 4 | bbs.t.pid | 1 | |
| 3 | UNION | t | ALL | NULL | NULL | NULL | NULL | 83826 | Using where; Using filesort |
| 3 | UNION | p | eq_ref | PRIMARY | PRIMARY | 4 | bbs.t.pid | 1 | |
| 4 | UNION | t | ALL | NULL | NULL | NULL | NULL | 4331 | Using where; Using filesort |
| 4 | UNION | p | eq_ref | PRIMARY | PRIMARY | 4 | bbs.t.pid | 1 | |
| 5 | UNION | t | ALL | NULL | NULL | NULL | NULL | 27649 | Using where; Using filesort |
| 5 | UNION | p | eq_ref | PRIMARY | PRIMARY | 4 | bbs.t.pid | 1 | |
|NULL | UNION RESULT | <union1,2,3,4,5> | ALL | NULL | NULL | NULL | NULL | NULL | Using filesort |
+----+--------------+------------------+--------+---------------+---------+---------+-----------+--------+-----------------------------+
11 rows in set (0.00 sec)
解决方案 »
- 有关事务控制的问题
- MySQL utf8存储 GBK读取
- 数据库结合webservice
- MySQL中怎么查询一张表的列数??
- 请问各位老大:在MS SQL Server中能正确运行的Update语句,到My SQL中后要作何修改?
- 请问SQL和MYSQL有什么区别呢?学哪个难点,学哪个吃香点呢?谢谢回答啊
- 请问高手,一个向mysql中导入sqlserver数据时的报错.
- 俺WINNT上安装了MySql3.23用NET START MySql启动后,键入mysqlshow,显示错误。
- MYSQL有Server端和Client端之分吗?谢谢!
- 使用同一条件字段,不同条件值,速度慢了很多,求解!
- linux如何加例外
- 哪位大虾知道怎么把Oracle数据库的数据导出然后导入到Mysql数据库中?
select t.*, p.`userid` as ruserid, p.`username` as rusername, p.`ip` as rip from `thread_5422` as t left join `post_5422` as p on p.`id` = t.`pid` where t.`notify`=1 order by t.`top` desc, t.`mtime` desc limit 50000
存为 VIEW1
SELECT * FROM (
select *,1 as fid FROM VIEW1
UNIUON ALL
select *,2 as fid FROM VIEW1
UNIUON ALL
select *,3 as fid FROM VIEW1
UNIUON ALL
select *,4 as fid FROM VIEW1
UNIUON ALL
select *,5 as fid FROM VIEW1) AA
order by `top` desc,`mtime` desc limit 50000, 50 ;
select 1 as fid, t.*, p.`userid` as ruserid, p.`username` as rusername, p.`ip` as rip
from `thread_5422` as t left join `post_5422` as p on p.`id` = t.`pid`
where t.`notify`=1
order by t.`top` desc, t.`mtime` desc limit 50000
进行索引的添加以提高单个查询的速度。 需要的索引 thread_5422(notify) 及 post_5422(pid)
你每个子查询中都取了50000记录然后再排序取50000, 50 这样这一步中什么索引都无法使用,会比较耗时。