mysql sum提高效率_MySQL巧建sum索引帮我们提高至少100%的效率

TABLE`a` (

`id` mediumint(8) unsigned NOT NULLAUTO_INCREMENT,

`fid`smallint(6) unsigned NOT NULL DEFAULT ‘0‘,

`cnt`smallint(6) unsigned NOT NULL DEFAULT ‘0‘,

...

...

...PRIMARY KEY(`id`),KEY`idx_fid` (`fid`),

) ENGINE=MyISAM DEFAULT CHARSET=utf8

表b

CREATE TABLE`b`

(`fid`smallint(6) unsigned NOT NULLAUTO_INCREMENT,

`name`char(50) NOT NULL DEFAULT ‘‘,

...

...

...PRIMARY KEY(`fid`),

) ENGINE=MyISAM DEFAULT CHARSET=utf8

操作SQL如下：

SELECT COUNT(*) AS num1, SUM(a.cnt)+COUNT(*) ASnum2FROMa, bWHERE b.fid=‘10913‘ AND a.fid=b.fid

我们先看下执行计划：