Mysql optimization select count efficiency

1. SELECT COUNT(*) FROM tablename is the best choice under any circumstances;

2. Try to reduce queries like SELECT COUNT(*) FROM tablename WHERE COL = 'value';

3. Avoid SELECT COUNT(COL ) FROM tablename occurrence.

COUNT(*) and COUNT(COL)

After searching online, I found various opinions:

For example, it is believed that COUNT(COL) is faster than COUNT(*);

It is believed that COUNT(*) It’s faster than COUNT(COL);

A friend also said very funny that this actually depends on character.

Without WHERE restrictions, COUNT(*) and COUNT(COL) can be basically considered equivalent;

But with WHERE restrictions, COUNT(*) will be better than COUNT(COL) ) is very fast;

The specific data reference is as follows:

mysql> SELECT COUNT(*) FROM cdb_posts where fid = 604;

+————+

| COUNT(fid) |

+—— ——+

| 79000 |

+————+

1 row in set (0.03 sec)

mysql> SELECT COUNT(tid) FROM cdb_posts where fid = 604;

+————+

| COUNT(tid) |

+————+

| 79000 |

+————+

1 row in set (0.33 sec)

mysql> SELECT COUNT(pid) FROM cdb_posts where fid = 604;

+————+

| COUNT(pid) |

+————+

| 79000 |

+————+

1 row in set (0.33 sec)

COUNT(*) usually performs an index scan on the primary key, but COUNT(COL) is not necessarily the case. In addition, the former counts the total number of matching records in the table, while the latter counts all matching COLs in the table. Number of records. There are differences.

WHERE during COUNT

To put it simply, when there is COUNT, if there is no WHERE restriction, MySQL directly returns the total number of rows saved

But when there is a WHERE restriction, MySQL always needs to be fully Table traversal.

Optimization summary:

1. SELECT COUNT(*) FROM tablename is the best choice under any circumstances;

2. Try to reduce queries like SELECT COUNT(*) FROM tablename WHERE COL = 'value';

3 .Prevent the occurrence of SELECT COUNT(COL) FROM tablename.