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

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 2

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

For Oracle at least.

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

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 5

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 6

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 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).