The execution difference between count (1), COUNT (*) and Count (column name)

Execution effect:

1. Count (1) and COUNT (*) when the table has a larger amount of data, using count (1) is more time-consuming than using count (*) when analyzing the table. From the execution plan, the effect of count (1) and COUNT (*) is the same.    However, after the table has been parsed, count (1) will be less time-consuming than count (*) (1w of data), but not much. If Count (1) is a clustered index, ID, that must be count (1) fast.  But the difference is very small. Because Count (*), automatically optimizes the assigned field. So there is no need to go to count (1), with Count (*), SQL will help you complete the optimization therefore:count (1) and COUNT (*) are basically no different。 2. The main difference between count (1) and count (field) is (1) count (1) The number of records in the table,records containing NULL in fields。 (2) count (field) counts the number of times the field appears in the table, ignoring the case of NULL. Thatrecord with no statistics field null。turn from: http://www.cnblogs.com/Dhouse/p/6734837.html

The difference between count (*) and COUNT (1) and Count (column name)
effect on Execution：COUNT (*) includes all columns, equivalent to the number of rows, at the time of the statistical results,Column values are not ignored null
Count (1)This includes ignoring all columns, representing lines of code with 1, and when the results are statisticallycolumn values are not ignored null
Count (column name)Contains only the column names, and when the results are counted, the column values are ignored (null is not an empty string or 0, but null).that is, when a field value is null, it does not count。
efficiency in execution:
The column name is the primary key, and count (column name) is faster than count (1)
The column name is not the primary key, and COUNT (1) is faster than count (column name)
COUNT (1) is more efficient than count (*) If there are multiple columns in the table and there are no primary keys.
Select COUNT (primary key) performs the optimal execution efficiency if there is a primary key
Select COUNT (*) is optimal if the table has only one field.turn from: http://eeeewwwqq.iteye.com/blog/1972576

Example Analysis

Mysql> CREATE TABLE Counttest (name char (1), age char (2)); Query OK, 0 rows affected (0.03 sec) mysql> insert into counttest values-> (' A ', ' '), (' A ', ', '), (' A ', ' 15 ')
),-> (' B ', null), (' B ', ', '),-> (' C ', ', '),-> (' d ', null),-> (' e ', ');
Query OK, 8 rows affected (0.01 sec) records:8 duplicates:0 warnings:0 mysql> select * from Counttest; +------+------+
| name |
Age | +------+------+
| A | 14 | | A | 15 | | A | 15 | | B | NULL | | B | 16 | | C | 17 | | D | NULL | |      e |
| +------+------+ 8 rows in Set (0.00 sec) mysql> Select name, count (name), COUNT (1), COUNT (*), count (age), COUNT (Distin
CT (age))-> to Counttest-> Group by name; +------+-------------+----------+----------+------------+----------------------+
| name | COUNT (name) | COUNT (1) | COUNT (*) | Count (age) |
COUNT (distinct (age)) | +------+-------------+----------+----------+------------+----------------------+
|           A |        3 |        3 |          3 |                    3 | 2 | |           B |        2 |        2 |          2 |                    1 | 1 | |           C |        1 |        1 |          1 |                    1 | 1 | |           D |        1 |        1 |          1 |                    0 | 0 | |           e |        1 |        1 |          1 |                    1 |
1 | +------+-------------+----------+----------+------------+----------------------+ 5 rows in Set (0.00 sec)

Additional references: http://blog.csdn.net/lihuarongaini/article/details/68485838