Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
Answered on SO already: stackoverflow.com/questions/1221559/count-vs-count1/… –  gbn May 8 '12 at 16:45
1  
And for MySQL specifically (and only AFAIK), COUNT(*) and COUNT(1) has been implemented a bit faster than COUNT(a_not_null_column) –  ypercube May 8 '12 at 16:50
add comment

marked as duplicate by gbn, JNK, Leigh Riffel, ConcernedOfTunbridgeWells, Nick Chammas May 8 '12 at 19:16

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1 Answer

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)
share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.