[ACCEPTED]-Why is Select 1 faster than Select count(*)?-rdbms
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.
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
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 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
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.
For Oracle at least.
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 ;)
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.
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).
If you are using postgresql, than
count(1) is infact 12 slower than
A note about
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