[ACCEPTED]-Why is Select 1 faster than Select count(*)?-rdbms

Accepted answer
Score: 16

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

Score: 14

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

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

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

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

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

Score: 2

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

Score: 0

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

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

Score: 0

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

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

Score: 0

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

Score: 0

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

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

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

More Related questions