+----+-------------+-------+-------+---------------+-------------
--+---------+------+----------+-------+

| 1 | SIMPLE | temp | index | NULL | ind_temp_c123 | 71
| NULL | 40000004 | |

+----+-------------+-------+ www.2cto.com -------+---------------+--------
-------+---------+------+----------+-------+

1 row in set (0.05 sec)

mysql> insert into c2kw select c1,c2,c3,max(c4) from temp
FORCE INDEX (ind_temp_c123) group by c1,c2,c3 ;

Query OK, 20000004 rows affected (2 min 0.85 sec)

Records: 20000004 Duplicates: 0 Warnings: 0

实际大约花费实际为：6 min

4、删除中间表

mysql> drop table temp;

Query OK, 0 rows affected (0.99 sec)

实际大约花费实际为：1 sec

5、建立c索引

mysql> create index ind_c2kw_c1 on c2kw(c1);

Query OK, 20000004 rows affected (49.74 sec)

Records: 20000004 Duplicates: 0 Warnings: 0

mysql> create index ind_c2kw_c2 on c2kw(c2);

Query OK, 20000004 rows affected (1 min 47.20 sec)

Records: 20000004 Duplicates: 0 Warnings: 0

mysql> create index ind_c2kw_c3 on c2kw(c3);

Query OK, 20000004 rows affected (2 min 42.02 sec)

Records: 20000004 Duplicates: 0 Warnings: 0

实际大约花费实际为：5分钟 www.2cto.com

6、清空a、b表

mysql> truncate table a2kw;

Query OK, 0 rows affected (1.15 sec)

mysql> truncate table b2kw;

Query OK, 0 rows affected (1.34 sec)

实际大约花费实际为：3sec

一共花费的时间大概在15分钟左右

作者 RuleV5