What is better in MYSQL count(*) or count(1)?

by

This is a MySQL answer.

They perform exactly the same – unless you are using MyISAM, then a special case for COUNT(*) exists. I always use COUNT(*) anyway.

https://dev.mysql.com/doc/refman/5.6/en/aggregate-functions.html#function_count

For MyISAM tables, COUNT(*) is optimized to return very quickly if the
SELECT retrieves from one table, no other columns are retrieved, and
there is no WHERE clause. For example:

mysql> SELECT COUNT(*) FROM student;

This optimization only applies to MyISAM
tables, because an exact row count is stored for this storage engine
and can be accessed very quickly. COUNT(1) is only subject to the same
optimization if the first column is defined as NOT NULL.

###EDIT
Some of you may have missed the dark attempt at humour. I prefer to keep this as a non-duplicate question for any such day when MySQL will do something different to SQL Server. So I threw a vote to reopen the question (with a clearly wrong answer).

The above MyISAM optimization applies equally to

COUNT(*)
COUNT(1)
COUNT(pk-column)
COUNT(any-non-nullable-column)

So the real answer is that they are always the same.

More Related Contents:

  1. MYSQL OR vs IN performance
  2. SQL Performance UNION vs OR
  3. MySQL “IN” operator performance on (large?) number of values
  4. MySQL: Inner join vs Where [duplicate]
  5. Does Order of Fields of Multi-Column Index in MySQL Matter
  6. multiple query same table but in different columns mysql
  7. How can I make SQL case sensitive string comparison on MySQL?
  8. 1052: Column ‘id’ in field list is ambiguous
  9. How to do a batch insert in MySQL
  10. Count the occurrences of DISTINCT values
  11. Creating a “Numbers Table” in MySQL
  12. Cumulative sum over a set of rows in mysql
  13. Set NOW() as Default Value for datetime datatype?
  14. Add Auto-Increment ID to existing table?
  15. MySQL SELECT DISTINCT multiple columns
  16. SQL DELETE with JOIN another table for WHERE condition
  17. SQL query of multiple values in one cell
  18. How to skip certain database tables with mysqldump?
  19. MySQL error 1241: Operand should contain 1 column(s)
  20. How to transpose rows to columns with large amount of the data in BigQuery/SQL?
  21. Mysql: Order by like?
  22. Getting first day of the week in MySql using Week No
  23. Is there a way to get the row number in Mysql like the rownum in oracle [duplicate]
  24. How to get a list of months between two dates in mysql
  25. How to format number with “.” as thousands separator and “,” as decimal separator?
  26. Using DISTINCT and COUNT together in a MySQL Query
  27. is php sort better than mysql “order by”?
  28. How can “set timestamp” be a slow query?
  29. SQL (MySQL) vs NoSQL (CouchDB) [closed]
  30. MySQL Multiple Joins in one query?

Leave a Comment