The difference between select COUNT (*) and select COUNT (1)

A

In general, select COUNT (*) and select COUNT (1) both return the same result

If the table does not have a primary key (Primary key), then count (1) is faster than COUNT (*),

If there is a primary key, the primary key is the fastest when the condition of Count is the count (primary key)

If your table has only one field, then count (*) is the fastest

Count (*), like the result of Count (1), includes statistics for NULL, and Count (column) is a statistic that does not include null

1. The difference between Select 1 and select *
Selelct Constants from ... For all rows, the return is always only one value, constant. So normal will only be used to determine if there is or not (such as the EXISTS clause). and select * From ... is all columns that return all rows.
The difference in performance, the key to see your from and WHERE clauses. For example, if your where condition can be indexed, then the Select 1 obviously from ... Performance than select * From ... Good.
2. Use of select SUM (1)
Select COUNT (*) returns the number of records that meet the criteria at this time with the select SUM (1)
But sum () can pass arbitrary numbers, negative numbers, floating-point numbers, the returned value is the passed-in value N # satisfies the condition record m

B

Select COUNT (1) from Test
- in the absence of aggregation, the efficiency of this query may be a little higher
Select COUNT (*) from Test
--Index Walk Index, no index full table scan, can take advantage of table statistics
Select count (a) from test
--Scans the number of data records for column A, which is the least efficient if there is no index on a, and does not count if column A contains null

C

COUNT (1) is absolutely fast, it only counts the number of record bars, and scans the table under conditions.
COUNT (*) is possible fast, with primary key only sweep primary key, number of primary key equals record number
COUNT (primary key) =count (1)

The difference between select COUNT (*) and select COUNT (1) (RPM)