优化sql语句

zhengdl126

表结构如上，就是书的章节表，我现在想实现的效果是找出每本书最后更新的一个章节（不需要内容字段content），并且按id倒序，只需要操作一这个表，不需要关联其它表，

以下是本人写的sql，效率似乎都很低。

select id, b_id, b_name, title, subtitle, chapter, sort, c_time, u_time from maos_book_ext where id in (SELECT max( id ) FROM maos_book_ext GROUP BY b_id order by 1 desc )

SELECT id, b_id, b_name, title, subtitle, chapter, sort, c_time, u_time  FROM (SELECT id, b_id, b_name, title, subtitle, chapter, sort, c_time, u_time FROM `maos_book_ext` ORDER BY id DESC ) T GROUP BY  b_id ORDER BY id DESC


还想请教，如何比较两条sql 执行的时间更快

cenalulu

别用in，用表连接试试？

1. 
   
2. select id, b_id, b_name, title, subtitle, chapter, sort, c_time, u_time from maos_book_ext a
   
3. join
   
4. (SELECT max( id ) FROM maos_book_ext GROUP BY b_id order by 1 desc ) b
   
5. where a.id=b.id
   

复制代码

顺便贴一下explain的结果

zhengdl126

这个表有30多万数据，效率还是有点低

zhengdl126

下面三条语句在页面执行的时候，都很慢，页面有时还会卡死，请问还有别的ｓｑｌ吗？



mysql> explain select id, b_id, b_name, title, subtitle, chapter, sort, c_time,u
_time from maos_book_ext where id in (SELECT max( id ) FROM maos_book_ext where
chapter=0 GROUP BY b_id  order by 1 desc ) limit 0,30;
+----+--------------------+---------------+-------+---------------+-----------+-
--------+------+-------+-----------------------------+
| id | select_type        | table         | type  | possible_keys | key       |
key_len | ref  | rows  | Extra                       |
+----+--------------------+---------------+-------+---------------+-----------+-
--------+------+-------+-----------------------------+
|  1 | PRIMARY            | maos_book_ext | ALL   | NULL          | NULL      |
NULL    | NULL | 91636 | Using where                 |
|  2 | DEPENDENT SUBQUERY | maos_book_ext | index | NULL          | Index_bid |
4       | NULL | 91636 | Using where; Using filesort |
+----+--------------------+---------------+-------+---------------+-----------+-
--------+------+-------+-----------------------------+
2 rows in set (0.48 sec)



mysql> explain select id, b_id, b_name, title, subtitle, chapter, sort, c_time,u
_time from `maos_book_ext` a join (SELECT max( id ) as ssid FROM `maos_book_ext`
where chapter=0  GROUP BY b_id order by 1 desc ) b where a.id=b.ssid LIMIT 0 ,3
0;
+----+-------------+---------------+--------+---------------+---------+---------
+--------+-------+----------------------------------------------+
| id | select_type | table         | type   | possible_keys | key     | key_len
| ref    | rows  | Extra                                        |
+----+-------------+---------------+--------+---------------+---------+---------
+--------+-------+----------------------------------------------+
|  1 | PRIMARY     | <derived2>    | ALL    | NULL          | NULL    | NULL
| NULL   |   345 |                                              |
|  1 | PRIMARY     | a             | eq_ref | PRIMARY       | PRIMARY | 4
| b.ssid |     1 |                                              |
|  2 | DERIVED     | maos_book_ext | ALL    | NULL          | NULL    | NULL
| NULL   | 91636 | Using where; Using temporary; Using filesort |
+----+-------------+---------------+--------+---------------+---------+---------
+--------+-------+----------------------------------------------+
3 rows in set (3.45 sec)


mysql> explain select id, b_id, b_name, title, subtitle, chapter, sort, c_time,u
_time from (select id, b_id, b_name, title, subtitle, chapter, sort, c_time,u_ti
me   from   `maos_book_ext`   ORDER BY `b_id` DESC, `sort` DESC  ) T where chapt
er=0   group   by   `b_id` limit 0,30;
+----+-------------+---------------+------+---------------+------+---------+----
--+-------+----------------------------------------------+
| id | select_type | table         | type | possible_keys | key  | key_len | ref
  | rows  | Extra                                        |
+----+-------------+---------------+------+---------------+------+---------+----
--+-------+----------------------------------------------+
|  1 | PRIMARY     | <derived2>    | ALL  | NULL          | NULL | NULL    | NUL
L | 91636 | Using where; Using temporary; Using filesort |
|  2 | DERIVED     | maos_book_ext | ALL  | NULL          | NULL | NULL    | NUL
L | 91636 | Using filesort                               |
+----+-------------+---------------+------+---------------+------+---------+----
--+-------+----------------------------------------------+
2 rows in set (4.13 sec)

liyulong1982

回复 1# zhengdl126


    id 和 b_id建索引了吗？应该不会这么慢啊
   
   先看看执行那个子查询用了多长时间

vitas333_cu

没看懂。。。  

有点迷糊。。。

给的数据，最好能详细些