SQL talk : count (1) and count (*)

 This post last edited by the xj90314 on 2012-11-28 09:53:46

everyone in the program when the total number of more or less will encounter statistics sql, then . We count this function is frequently used it.
ago I remember reading a post that , count (1) than the count (*) fast, the reason is simple . I am only the first column statistics. Saying in theory, should be right. But the reality is not the case .
Let me give an example , my own writing sql. to data confidentiality , I changed the name of the table , you will forgive me .

select 
  --* 
  count(1)
  from tt where change_date>='2012-05-28' and change_date<='2012-08-28'

Data volume: 28,753
The result is : 1 row selected in 0.26 secs.

second SQL

select 
  --* 
  count(*)
  from tt where change_date>='2012-05-28' and change_date<='2012-08-28'

Data volume: 28,753
The result is : 1 row selected in 0.25 secs.
count (*) than the count (1) fast 0.01secs.


Well, I try to increase the amount of data . Time raised the year before.
sql:

select 
  --* 
  count(*)
  from tt where change_date>='2011-05-28' and change_date<='2012-08-28'

Data volume: 82,805
Time Results : 1 row selected in 0.31 secs.

sql:

select 
  --* 
  count(1)
  from tt where change_date>='2011-05-28' and change_date<='2012-08-28'

Data volume: 82,805
Time : 1 row selected in 0.26 secs.

It seems when a large quantity of data count (1) performance will be a good point , well, continue to increase the amount of data
SQL:

select 
  --* 
  count(1)
  from tt 
  

The result: 243,511
Time : 1 row selected in 2.20 secs.

select 
  --* 
  count(*)
  from tt

The result: 243,511
Time : 1 row selected in 0.12 secs.

MB, the evil it. . . . I test environment : VM4xp db2
welcome everyone refers to three four .
------ Solution - -------------------------------------------
1, saying that forum posts should go to the database made ​​;
2, different databases and different versions of different results ;
3, in fact, a lot of knowledge is constantly updated , and now most of the databases are already count (*) is optimized , so unless it is antique , otherwise basically direct count (*).
------ Solution ---------------------------------------- ----
really quickly · · ·
------ Solution ------------------------ --------------------
like Mice said , count (*) have been optimized !
count (*) time, if the table has PK, then take the PK, PK to find not null no index !
count (1) If you are one of the field has an index then take the index , PK left PK, if not , it can not speed up !
------ Solution ---------------------------------------- ----
count (*)
count (1)
count ( field )
There should be three , meaning I have already made ​​it very clear , I will not say more
------ Solution --------------- -----------------------------
theoretically , count (*) statistical results of rows and multiple columns single row the number of rows , the result is the same , it must be selected in the realization of a simple way .
------ For reference only -------------------------------------- -
share your couch really fast . .
------ For reference only -------------------------------------- -


Today, just open CSDN, see the first post is for you. . .
------ For reference only -------------------------------------- -
not send execution plan what use is made ​​of time .

even where conditions are not the same as the SQL you feel that you just change the amount of data ?

write directly count (*) If you take the index , simply do not take the table .
------ For reference only -------------------------------------- -

brother, how where it is not the same ? I said that is not clear or you can not bear to look carefully I write sql
------ For reference only ---------------------------------------


I do not have the heart to ask your primary language teacher of the current situation .

select
- *
count (*)
from tt where change_date> = '2012-05-28 'and change_date <= '2012-08-28'

select
- *
count (1)
from tt where change_date> = '2012-05-28 'and change_date <= '2012-08-28'

Do you think the database less count (1) fast , right ?

where conditions and then a change of the number of multi-point , do you think count (*) Get up , eh ?

Then you feel select count (1) from tt point this thing increase the amount of data , count (*) should be faster , it's not what you say it ?

did this thing where the conditions , and in front of you have anything ?
To compare you have to compare is select count (1) from tt where change_date> = '1900-01-01 'and change_date <= '3999-01-01'


------ For reference only ---------------------------------- -----
landlord , this error estimates are within the scope of any statement execution

Also a sql, execute n times, the time between each error range .

------ For reference only ---------------------------------- -----

brother, you see Haha, I speak only for data , the speed did not say anything , says
------ For reference only --- ------------------------------------
I did not publish any of my conclusions. . .
------ For reference only -------------------------------------- -




ah , that you think that the result is normal , is that you in the evil , not the database evil , right .

That I did not watch carefully .
------ For reference only -------------------------------------- -
select (1) faster , and I was using select (1), because the project is written like this , with everyone , or else select (*) did not level
------ for reference only ---------------------------------------
for most commercial data speaking
with a primary key or index , count (*) faster
these databases , count (1) is not to use the index , while the count (*) will automatically scan the column index ( primary key or index )

database or some files may not have this small database optimization strategies
------ For reference only ---------------------- -----------------
've always heard people say to use count (*) said
------ For reference only ----- ----------------------------------

this is what you said in the antique and of the ...
------ For reference only --------------------------------- ------
learning can not be blind , who are required to do dialectical thinking .
support the landlord issues of this right to test and share test results with a good attitude and practices.
contempt hearsay convinced, and some even show off yet unassuming man.
The above represents only personal views .
ps: I usually have all count (*), because I found in my database * or one or both fields, query plans are the same ...

------ For reference only ---------------------------------- -----
upstairs well said ! count (0) count (1) count (*) which in the end what is the difference ah , all of them were under what conditions to use ah ?