group by 优化

[去圣西罗-为尤文喝彩]

我有一个InnoDB表，~44,905,525行，7.3 GiB。

CREATE TABLE IF NOT EXISTS `m_t` (
  `name` varchar(15) NOT NULL,
  `col` smallint(2) NOT NULL,
  `index` varchar(30) NOT NULL,
  `date` int(10) NOT NULL,
  PRIMARY KEY (`index`,`name`,`col`),
  KEY `date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#结构大致如下
name | col | index | date
1234 | 0 | word1 |1360230780
1234 | 1 | word1 |1360230780
1234 | 1 | word2 |1360230780
1235 | 0 | word3 |1360250840
1236 | 2 | word2 |1360250740
.....[/code]

name是文章编号，col是每篇文章的段落号，index是关键字（每个一列），date是文章的时间。

现在要查询符合包含某2个关键字的文章，按时间排列。

SELECT * FROM 
m_t a JOIN m_t b 
ON a.name = b.name 
WHERE 
a.index='word1'
AND
b.index='word2'  
Group by a.name 
ORDER BY a.date 
DESC LIMIT 10

没有Group by的话，只要0.05秒。
加group by 超慢，是索引关系还是查询语句关系？如何优化？谢谢。

[healer_kx]

首先你可以先试着 show processlist;

[Wentasy]

1.语句优化： SELECT * 是大忌，查询所有字段列出来也比*效率高； 2.索引优化： name字段是复合主键字段之一，可以尝试单独在name字段上加索引。 暂时想到那么多。

[wwwwb]

在A的NAME、INDEX、DATE上建立索引 B的NAME、INDEX上建立索引

[去圣西罗-为尤文喝彩]

引用 3 楼 wwwwb 的回复:

在A的NAME、INDEX、DATE上建立索引 B的NAME、INDEX上建立索引

只有一个表，m_t你的意思是再建立两个普通索引？ NAME、INDEX、DATE和NAME、INDEX？

[去圣西罗-为尤文喝彩]

引用 1 楼 healer_kx 的回复:

首先你可以先试着 show processlist;

show processlist 可以分析出什么？我只知道explain……

[wwwwb]

引用 4 楼 changjay 的回复:

[quote=引用 3 楼 wwwwb 的回复:] 在A的NAME、INDEX、DATE上建立索引 B的NAME、INDEX上建立索引

只有一个表，m_t你的意思是再建立两个普通索引？ NAME、INDEX、DATE和NAME、INDEX？[/quote] 在m_t上NAME、INDEX、DATE上建立索引，并强制使用索引试试

[benluobo]

可以explain贴出建立索引前和建立索引后的查询计划 供讨论

[benluobo]

可以explain贴出建立索引前和建立索引后的查询计划 供讨论

[benluobo]

可以explain贴出建立索引前和建立索引后的查询计划 供讨论

[WWWWA]

呵呵，没有，只有在空闲时间建立索引 OR 建立1个新表，建立复合索引，再插入工作表的数据

[去圣西罗-为尤文喝彩]

引用 6 楼 wwwwb 的回复:

在m_t上NAME、INDEX、DATE上建立索引，并强制使用索引试试

表体积比较大，而且cron脚本还在定时的插入新的记录，有什么办法可以快速建立一个索引，但不影响服务器正常运行吗？

[去圣西罗-为尤文喝彩]

引用 11 楼 WWWWA 的回复:

呵呵，没有，只有在空闲时间建立索引 OR 建立1个新表，建立复合索引，再插入工作表的数据

如果锁表，强行建立索引的话，根据你的经验，大概需要多少时间?

[去圣西罗-为尤文喝彩]

锁表后，强行建立了索引，平均查询大概优化到0.5秒左右。 基本达到要求。