看最后一个SQL还有优化的可能吗 [ 2.0 版本 ]

2015-08-21 15:54:11 2691次浏览 2条回答 0 悬赏 100 金钱 
mysql>SELECT count(*) FROM dynamic;
+--------------------+
| count(*)           |
+--------------------+
| 1000000            |
+--------------------+
共返回 1 行记录,花费 360.01 ms.
mysql>SELECT count(*) FROM follow;
+--------------------+
| count(*)           |
+--------------------+
| 2219448            |
+--------------------+
共返回 1 行记录,花费 554.29 ms.
mysql>SELECT count(*) FROM user;
+--------------------+
| count(*)           |
+--------------------+
| 1000000            |
+--------------------+
共返回 1 行记录,花费 527.86 ms.
mysql>SELECT id,uid FROM dynamic WHERE uid IN(SELECT followuid from follow where uid=1230) AND status=0 ORDER BY ID DESC LIMIT 10,10;
+--------------+---------------+
| id           | uid           |
+--------------+---------------+
|       875452 |        404599 |
|       875115 |        789665 |
|       870557 |        667409 |
|       867318 |        104589 |
|       867267 |        677584 |
|       859238 |        181793 |
|       858402 |        606231 |
|       857183 |        384240 |
|       806320 |        224603 |
|       803995 |         22575 |
+--------------+---------------+
共返回 10 行记录,花费 191.20 ms.

共 2 个回答

默认排序
  • 回答于 2015-08-21 16:10 举报 
    select D.id,D.uid from dynamic D inner join follow F on F.followuid = D.uid where F.uid=1230 and D.status=0 order by D.id desc limit 10,10

    试试看我也不清楚效率会不会更高。

    共 1 条回复
    回复于 2015-08-21 16:11 回复 
    mysql>select D.id,F.uid from dynamic D inner join follow F on F.followuid = D.uid where F.uid=1230 and D.status=0 order by D.id desc limit 10,10
+--------------+---------------+
| id           | uid           |
+--------------+---------------+
|       875452 |          1230 |
|       875115 |          1230 |
|       870557 |          1230 |
|       867318 |          1230 |
|       867267 |          1230 |
|       859238 |          1230 |
|       858402 |          1230 |
|       857183 |          1230 |
|       806320 |          1230 |
|       803995 |          1230 |
+--------------+---------------+
共返回 10 行记录,花费 192.21 ms.
    回复 0 0
  • 回答于 2015-08-21 21:21 举报

    试试用left join看看

    回复 0 0

回答问题

您需要登录后才可以回答。登录 | 立即注册
trylife
总监

trylife Beijing

注册时间：2015-03-26
最后登录：2020-12-07
在线时长：51小时55分
  • 粉丝39
  • 金钱1626
  • 威望40
  • 积分2536
私信

热门问题