MySQL count(1) count(*) Myth

Sometimes people say that you should use select count(1) from table; instead select count(*) from table; because is faster, if you read some good books on MySQL they never refer this optimisation (strange).

So a little testing:

mysql> explain extended select sql_no_cache count(*) from lc_contact_detail \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: lc_contact_detail
type: index
possible_keys: NULL
key: contact_id
key_len: 4
ref: NULL
rows: 13393816
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)

mysql> explain extended select sql_no_cache count(1) from lc_contact_detail \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: lc_contact_detail
type: index
possible_keys: NULL
key: contact_id
key_len: 4
ref: NULL
rows: 13393849
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)

Looks MySQL will execute the  query in the same way in either case, if didn’t convince you, profile:

mysql> select sql_no_cache count(*) from lc_contact_detail;
+———-+
| count(*) |
+———-+
| 13457066 |
+———-+
1 row in set (5.64 sec)

mysql> show profile for query 1;
+——————–+———-+
| Status             | Duration |
+——————–+———-+
| starting           | 0.033511 |
| Opening tables     | 0.000019 |
| System lock        | 0.000007 |
| Table lock         | 0.000012 |
| init               | 0.000016 |
| optimizing         | 0.000008 |
| statistics         | 0.000015 |
| preparing          | 0.000012 |
| executing          | 0.000007 |
| Sending data       | 5.606788 |
| end                | 0.000018 |
| query end          | 0.000005 |
| freeing items      | 0.000059 |
| logging slow query | 0.000004 |
| cleaning up        | 0.000006 |
+——————–+———-+
15 rows in set (0.02 sec)

mysql> select sql_no_cache count(1) from lc_contact_detail;
+———-+
| count(1) |
+———-+
| 13457088 |
+———-+
1 row in set (5.54 sec)

mysql> show profile for query 2;
+——————–+———-+
| Status             | Duration |
+——————–+———-+
| starting           | 0.000077 |
| Opening tables     | 0.000020 |
| System lock        | 0.000007 |
| Table lock         | 0.000011 |
| init               | 0.000017 |
| optimizing         | 0.000007 |
| statistics         | 0.000013 |
| preparing          | 0.000011 |
| executing          | 0.000008 |
| Sending data       | 5.545308 |
| end                | 0.000019 |
| query end          | 0.000006 |
| freeing items      | 0.000058 |
| logging slow query | 0.000004 |
| cleaning up        | 0.000006 |
+——————–+———-+
15 rows in set (0.00 sec)

Is actually the same. So feel free to use one form or another. count(*) looks better IMO

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>