What does COUNT(1) actually count? [duplicate]

Question

Possible Duplicate:
What is the difference between select count(*) and select count(any_non_null_column)?

I have heard it commonly stated that when counting the number of rows in a query, you should not do a COUNT(*) but you should do a count on an indexed column.

I've seen DBAs, when counting the number of rows, run SELECT COUNT(1) FROM table;.

What is the 1 in the query?

I've tried putting in other numbers (2, 0, -1) and result is always the same as using 1.

Is this just a shortcut rather than having list a specific column to count?

Are there any performance differences to using a constant versus listing a column name?

I'm currently using MySQl 5.1.60.

Answer 1

if you put count(*), count(1) or count("test") it will give you the same result because mysql will count the number of rows, for example:

select count(fieldname) from table;

will display the same result that

select count(*) from table;

or

select count(1) from table

mysql> select * from language;
+-------------+----------+---------------------+
| language_id | name     | last_update         |
+-------------+----------+---------------------+
|           1 | English  | 2006-02-15 05:02:19 |
|           2 | Italian  | 2006-02-15 05:02:19 |
|           3 | Japanese | 2006-02-15 05:02:19 |
|           4 | Mandarin | 2006-02-15 05:02:19 |
|           5 | French   | 2006-02-15 05:02:19 |
|           6 | German   | 2006-02-15 05:02:19 |
+-------------+----------+---------------------+
6 rows in set (0.00 sec)

mysql> select 1 from language;
+---+
| 1 |
+---+
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
+---+
6 rows in set (0.00 sec)

mysql> select 'anything'from language;
+----------+
| anything |
+----------+
| anything |
| anything |
| anything |
| anything |
| anything |
| anything |
+----------+
6 rows in set (0.00 sec)

mysql> select count(1), count(*), count('anything') from language;
+----------+----------+-------------------+
| count(1) | count(*) | count('anything') |
+----------+----------+-------------------+
|        6 |        6 |                 6 |
+----------+----------+-------------------+
1 row in set (0.00 sec)

the result will be a number equal to the number of rows of that table.

PLEASE BE AWARE THAT NULL FIELDS WILL NOT BE COUNTED....

mysql> select original_language_id from film where original_language_id is null;
+----------------------+
| original_language_id |
+----------------------+
|                 NULL |
|                 NULL |
|                 NULL |
|                 NULL |
|                 NULL |
|                 NULL |
|                 NULL |
......
|                 NULL |
+----------------------+
1000 rows in set (0.00 sec)

mysql> select count(original_language_id) from film where original_language_id is null;
+-----------------------------+
| count(original_language_id) |
+-----------------------------+
|                           0 |
+-----------------------------+
1 row in set (0.00 sec)

FOR THAT REASON IF YOU WANT TO COUNT ROWS I THINK count(something) is a good option

mysql> SELECT COUNT(1) FROM film;
+----------+
| COUNT(1) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)