admintest20
也关于mysql group by max 优化
我有这样的一个表
node
nid uid title created
1 1 aa 201
2 2 bb 202
3 2 aa 203
4 2 cca 204
5 3 dd 205
6 2 cc 206
7 3 aaadd 207
8 3 eeeee 208
我的目的就是找出最近 某三个人发表的文章
nid uid title created
8 3 eeeee 208
6 2 cc 206
1 1 aa 201
我写的这样
SELECT n.* FROM node AS n WHERE n.nid = (SELECT MAX(nid) FROM node WHERE uid = n.uid) ORDER BY n.nid DESC LIMIT 3;
但是我在explain的时候结果是这样的
+----+--------------------+-------+-------+---------------+---------+---------+-------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+-------+---------------+---------+---------+-------+-------+-------------+
| 1 | PRIMARY | n | index | NULL | PRIMARY | 8 | NULL | 18536 | Using where |
| 2 | DEPENDENT SUBQUERY | node | ref | uid | uid | 4 | n.uid | 31 | Using where |
+----+--------------------+-------+-------+---------------+---------+---------+-------+-------+-------------+
rows值很大
这样还可以优化吗 或者还有其它写法吗？
Edengundam
SELECT node.*
FROM node,
(SELECT MAX(nid) AS nid FROM node GROUP BY uid ORDER BY nid LIMIT 3) AS tmp
WHERE node.nid = tmp.nid;
确保表在nid有索引.
排序代价比你原来查询小很多. 而连接时候, 只有3条. 如果nid有索引, JOIN的效率非常高.
前提MySQL的优化器保证按照意图执行. 呵呵
你的代码是相关子查询, 优化器很可能优化不了. 而且最后进行ORDER BY, 中间结果集也比一上来GROUP BY的大. :mrgreen:
admintest20
谢谢两位
EXPLAIN SELECT node.*
FROM node ，
(SELECT MAX(nid) AS nid FROM node GROUP BY uid ORDER BY nid LIMIT 3) AS tmp
where node.nid = tmp.nid;
结果是
+----+-------------+------------+------+---------------+------+---------+---------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+---------+-------+---------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 3 | |
| 1 | PRIMARY | node | ref | PRIMARY,nid | nid | 4 | tmp.nid | 1 | |
| 2 | DERIVED | node | ALL | NULL | NULL | NULL | NULL | 30364 | Using temporary; Using filesort |
+----+-------------+------------+------+---------------+------+---------+---------+-------+---------------------------------+
这个结果比我以前那个好多了。
我的node表中nid是索引。
你们是join 是怎么个写法了，是这样吗 SELECT node.*
FROM node INNER JOIN
(SELECT MAX(nid) AS nid FROM node GROUP BY uid ORDER BY nid LIMIT 3) AS tmp
ON node.nid = tmp.nid; 但是我explain结果是一样的。