Difference between count (*) and count (1) and count (column name)

Time:2021-9-11

Execution effect:

  • Count () includes all columns, which is equivalent to the number of rows. When calculating the results,Column values that are null are not ignored
  • Count (1) includes ignoring all columns and using 1 to represent the code line. In the statistical results,Column values that are null are not ignored  
  • The count (column name) only includes the column name. In the statistical results, the count of the column value that is empty (the empty here is not only an empty string or 0, but represents null) will be ignored,That is, when the value of a field is null, no statistics will be made

Execution efficiency:

  • If the column name is the primary key, count (column name) will be faster than count (1)
  • If the column name is not a primary key, count (1) will be faster than count (column name)
  • If the table has multiple columns and no primary key, the execution efficiency of count (1) is better than count (*)
  • If there is a primary key, the execution efficiency of select count is the best
  • If the table has only one field, select count (*) is the best.

This work adoptsCC agreement, reprint must indicate the author and the link to this article

Happiness is to solve one problem after another!