Why is Select 1 faster than Select count(*)?

Question

In Oracle, when querying for row existence, why is Select 1 fast than Select count(*)?

Answer 1

It is better still to use EXISTS where the RDBMS supports it or an equivalent, as this will stop processing rows as soon as it finds a match.

Answer 2

Since Oracle doesn't support IF EXISTS in PL/SQL, CodeByMidnight's suggestion to use EXISTS would normally be done with something like

SELECT 1 
  INTO l_local_variable 
  FROM dual 
 WHERE EXISTS( 
    SELECT 1 
      FROM some_table 
     WHERE some_column = some_condition ); 

Oracle knows that it can stop processing the WHERE EXISTS clause as soon as one row is found, so it doesn't have to potentially count a large number of rows that match the criteria. This is less of a concern, of course, if you are checking to see whether a row with a particular key exists than if you are checking a condition involving unindexed columns or checking a condition that might result in a large number of rows being returned.

(Note: I wish I could post this as a comment on CodeByMidnight's post, but comments can't include formatted code).

UPDATE: Given the clarification the original poster made in their comment, the short, definitive answer is that a SELECT 1 or SELECT COUNT(1) is no faster than a SELECT COUNT(*). Contrary to whatever coding guidelines you are looking at, COUNT(*) is the preferred way of counting all the rows. There was an old myth that a COUNT(1) was faster. At a minimum, that hasn't been true in any version of Oracle released in the past decade and it is unlikely that it was ever true. It was a widely held belief, however. Today, code that does a COUNT(1) rather than a COUNT(*) generally makes me suspect that the author is prone to believe various Oracle myths which is why I would suggest using COUNT(*).

Answer 3

I'd be suprised if select count(*) wasn't properly optimised, there is no need to load in all the columns as there will be no column related processing.

Answer 4

http://www.dbasupport.com/forums/archive/index.php/t-28741.html

For Oracle at least.

Answer 5

Because a star takes all cols into the count, "1" is a native datatype.

In MySQL "SELECT COUNT(name_of_the_primary_key)" should be as fast as your SELECT 1. Its the index that counts. A count() on an index should be quite fast ;)

Answer 6

I don't think this is true for Oracle. http://justoracle.blogspot.com/2006/12/count-vs-count1.html

But, in some databases the reason is because '*' has to visit the tables meta-data. This tends to add an un-needed overhead. Where as 1 is just a literal.

Answer 7

All other things being equal, "select 1 from my_table" will return the first result quicker than "select count(*) from my_table", but if you retrieve all the results from the query, the count(*) one will be quicker because it involves much less data (1 integer, as opposed to 1 integer per each row in the table).

Answer 8

If you are using postgresql, than count(1) is infact slower than count(*)

Per: https://www.citusdata.com/blog/2016/10/12/count-performance/ :

A note about count(1) vs count(*). One might think that count(1) would be faster because count(*) appears to consult the data for a whole row. However the opposite is true. The star symbol is meaningless here, unlike its use in SELECT *. PostgreSQL parses The expression count(*) as a special case taking no arguments. (Historically the expression ought to have been defined as count().) On the other hand count(1) takes an argument and PostgreSQL has to check at every row to see that its argument, 1, is indeed still not NULL.