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)
解决方案 »
- 一个用户表、一个好友表,怎么得到好友最多的前三个用户??
- 将excel表的数据导入数据库格式错误为什么?
- SQL性能(REPLACE/SELECT+INSERT+UPDATE/INSERT...ON DUPLICATE KEY)
- JProgressBar 进度显示问题
- shell中mysql重启问题
- 触发器性能通常比较低????
- MySql数据库 查询问题
- 关于MYSQL的插入中文与检索中文!急!
- 怎样在NT下以SERVICE方式启动MYSQL的时候能够带参数,在哪个文件里配置?
- 只看过SQL的基本语法,问个基本的问题?
- 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 这样这一步中什么索引都无法使用,会比较耗时。