Monday, September 22, 2008

Common bad practice in database calculations

create table x ( i int, j varchar(10))
insert into x values (1,'a')
insert into `x`(`i`,`j`) values ( '2',NULL)

select count(*) from x
Select count(j) from x

Note the difference when the count(col_name) is used.

count(col_name) is used under the impression that it is faster than count(*) , however quite the opposite is true.

MYISAM table MySQL has cached number of rows in the table. Thats the reason why MYISM is able to instantly answer COUNT(*) query, but not COUNT(col_name).

Why ? Because say if col_name column is not defined as NOT NULL there can be some NULL values in it and so MySQL have to perform table scan to find out. This is also why result is different for the second query.

Using count(*) instead of count(col_name) falls in the best practice category.

No comments: