<p>I have the following code:</p>

<pre><code>SELECT &lt;column&gt;, count(*)
FROM &lt;table&gt;
GROUP BY &lt;column&gt; HAVING COUNT(*) &gt; 1;
</code></pre>

<p>Is there any difference to the results or performance if I replace the COUNT(*) with COUNT('x')?</p>

<p>(This question is related to a <a href="https://stackoverflow.com/questions/59294/in-sql-whats-the-difference-between-countcolumn-and-count">previous one</a>)</p>

[{'answer_id': 59385, 'author': 'Brannon', 'author_id': 5745, 'author_profile': 'https://Stackoverflow.com/users/5745', 'pm_score': 2, 'selected': False, 'text': "<p>The major performance difference is that COUNT(*) can be satisfied by examining the primary key on the table.</p>\n\n<p>i.e. in the simple case below, the query will return immediately, without needing to examine any rows.</p>\n\n<pre><code>select count(*) from table\n</code></pre>\n\n<p>I'm not sure if the query optimizer in SQL Server will do so, but in the example above, if the column you are grouping on has an index the server should be able to satisfy the query without hitting the actual table at all.</p>\n\n<p>To clarify: this answer refers specifically to SQL Server.  I don't know how other DBMS products handle this.</p>\n"}, {'answer_id': 59391, 'author': 'NateSchneider', 'author_id': 5129, 'author_profile': 'https://Stackoverflow.com/users/5129', 'pm_score': 2, 'selected': False, 'text': '<p>This question is slightly different that the other referenced.  In the referenced question, it was asked what the difference was when using count(*) and count(SomeColumnName), and <a href="https://stackoverflow.com/questions/59294/in-sql-whats-the-difference-between-countcolumn-and-count#59302">SQLMenace\'s answer</a> was spot on.</p>\n\n<p>To address this question, essentially there is no difference in the result.  Both count(*) and count(\'x\') and say count(1) will return the same number.  The difference is that when using " * " just like in a SELECT all columns are returned, then counted.  When a constant is used (e.g. \'x\' or 1) then a row with one column is returned and then counted.  The performance difference would be seen when " * " returns many columns.</p>\n\n<p><strong>Update</strong>:  The above statement about performance is probably not quite right as discussed in other answers, but does apply to subselect queries when using EXISTS and NOT EXISTS</p>\n'}, {'answer_id': 59412, 'author': 'Matt Rogish', 'author_id': 2590, 'author_profile': 'https://Stackoverflow.com/users/2590', 'pm_score': 5, 'selected': True, 'text': '<p>To say that <code>SELECT COUNT(*) vs COUNT(1)</code> results in your DBMS returning "columns" is pure bunk. That <em>may</em> have been the case long, long ago but any self-respecting query optimizer will choose some fast method to count the rows in the table - there is <strong>NO</strong> performance difference between <code>SELECT COUNT(*), COUNT(1), COUNT(\'this is a silly conversation\')</code></p>\n\n<p>Moreover, <code>SELECT(1) vs SELECT(*)</code> will NOT have any difference in INDEX usage -- most DBMS will actually optimize <code>SELECT( n ) into SELECT(*)</code> anyway. See the ASK TOM: Oracle has been optimizing <code>SELECT(n) into SELECT(*)</code> for the better part of a decade, if not longer:\n<a href="http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1156151916789" rel="noreferrer">http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1156151916789</a></p>\n\n<blockquote>\n  <p>problem is in count(col) to count(<em>)\n  conversion\n  <strong></em> **03/23/00</strong> 05:46 pm *** one workaround is to set event 10122 to\n  turn off count(col) ->count(<em>)\n  optimization. Another work around is\n  to change the count(col) to count(</em>),\n  it means the same, when the col has a\n  NOT NULL constraint. The bug number is\n  1215372.</p>\n</blockquote>\n\n<p>One thing to note - if you are using COUNT(col) (don\'t!) and col is marked NULL, then it will actually have to count the number of occurrences in the table (either via index scan, histogram, etc. if they exist, or a full table scan otherwise). </p>\n\n<p>Bottom line: if what you want is the count of rows in a table, use COUNT(*)</p>\n'}, {'answer_id': 59858, 'author': 'Darryl Hein', 'author_id': 5441, 'author_profile': 'https://Stackoverflow.com/users/5441', 'pm_score': 1, 'selected': False, 'text': '<p>MySQL: According to the MySQL website, <code>COUNT(*)</code> is faster for single table queries when using MyISAM:</p>\n\n<p><a href="http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_count" rel="nofollow noreferrer">http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_count</a></p>\n\n<p>I\'m guessing with a having clause with a count in it may change things.</p>\n'}]
['https://Stackoverflow.com/questions/59322', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/5662/']