Skip to main content
added 36 characters in body
Source Link
RolandoMySQLDBA
  • 184.3k
  • 33
  • 323
  • 531

In anthe book [Oracle8i Certified Professional DBA Certification Exam Guide (ISBN 0072130601)] old Oracle 8i Certification book1 I read years ago, page 78 says COUNT(1) will actually run faster that COUNT(*) because certain mechanisms are called into play for checking the data dictionary for the every column's nullability (or at least the first column with non-nullability) when using COUNT(*). COUNT(1) bypasses those mechanisms.

MySQL cheats for 'SELECT COUNT(1) on tblname;' on MyISAM tables by reading the table header for the table count. InnoDB counts every time.

To test whether COUNT(1) will run faster than COUNT(*) in a database agnostic way, just run the following and judge the running time for yourself:

SELECT COUNT(1) FROM tblname WHERE 1 = 1;
SELECT COUNT(*) FROM tblname WHERE 1 = 1;
SELECT COUNT(column-name) FROM tblname WHERE 1 = 1;

This makes the COUNT function operate on the same level playing field regardless of storage engine or RDBMS.

In an old Oracle 8i Certification book I read years ago, page 78 says COUNT(1) will actually run faster that COUNT(*) because certain mechanisms are called into play for checking the data dictionary for the every column's nullability (or at least the first column with non-nullability) when using COUNT(*). COUNT(1) bypasses those mechanisms.

MySQL cheats for 'SELECT COUNT(1) on tblname;' on MyISAM tables by reading the table header for the table count. InnoDB counts every time.

To test whether COUNT(1) will run faster than COUNT(*) in a database agnostic way, just run the following and judge the running time for yourself:

SELECT COUNT(1) FROM tblname WHERE 1 = 1;
SELECT COUNT(*) FROM tblname WHERE 1 = 1;
SELECT COUNT(column-name) FROM tblname WHERE 1 = 1;

This makes the COUNT function operate on the same level playing field regardless of storage engine or RDBMS.

In the book [Oracle8i Certified Professional DBA Certification Exam Guide (ISBN 0072130601)] 1, page 78 says COUNT(1) will actually run faster that COUNT(*) because certain mechanisms are called into play for checking the data dictionary for the every column's nullability (or at least the first column with non-nullability) when using COUNT(*). COUNT(1) bypasses those mechanisms.

MySQL cheats for 'SELECT COUNT(1) on tblname;' on MyISAM tables by reading the table header for the table count. InnoDB counts every time.

To test whether COUNT(1) will run faster than COUNT(*) in a database agnostic way, just run the following and judge the running time for yourself:

SELECT COUNT(1) FROM tblname WHERE 1 = 1;
SELECT COUNT(*) FROM tblname WHERE 1 = 1;
SELECT COUNT(column-name) FROM tblname WHERE 1 = 1;

This makes the COUNT function operate on the same level playing field regardless of storage engine or RDBMS.

Source Link
RolandoMySQLDBA
  • 184.3k
  • 33
  • 323
  • 531

In an old Oracle 8i Certification book I read years ago, page 78 says COUNT(1) will actually run faster that COUNT(*) because certain mechanisms are called into play for checking the data dictionary for the every column's nullability (or at least the first column with non-nullability) when using COUNT(*). COUNT(1) bypasses those mechanisms.

MySQL cheats for 'SELECT COUNT(1) on tblname;' on MyISAM tables by reading the table header for the table count. InnoDB counts every time.

To test whether COUNT(1) will run faster than COUNT(*) in a database agnostic way, just run the following and judge the running time for yourself:

SELECT COUNT(1) FROM tblname WHERE 1 = 1;
SELECT COUNT(*) FROM tblname WHERE 1 = 1;
SELECT COUNT(column-name) FROM tblname WHERE 1 = 1;

This makes the COUNT function operate on the same level playing field regardless of storage engine or RDBMS.