MySQL uses the sum index to Increase the efficiency by at least 100%.

There are two tables, Table

 CREATE TABLE `a` (
`id` mediumint(8) unsigned NOT NULL AUTO_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

Table B

 CREATE TABLE `b` 
(`fid` smallint(6) unsigned NOT NULL AUTO_INCREMENT,
`name` char(50) NOT NULL DEFAULT '',
...
...
...
PRIMARY KEY (`fid`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8

The SQL statement is as follows:

 SELECT COUNT(*) AS num1, SUM(a.cnt)+COUNT(*) AS num2
FROM a, b
WHERE b.fid='10913' AND a.fid=b.fid

Let's take a look at the execution plan:

We can see that the number of scanned rows is 229049, And the execution time is:

Terrible. Run set profiling = 1. Let's see where the time is consumed?

It takes a long time to sending data. What does sending data do during this time? Let's take a look at this: http://renxijun.blog.sohu.com/82906360.html

It is preparing data for the SELECT statement. solution:

Index creation:

 create index idx_fid_cnt on a (fid,cnt);

 

Let's look at the execution plan and time:

Conclusion: Using an appropriate index is a doubling of SQL efficiency. functions like sum include min () and max (), which all need to be indexed on fields.