Friday, June 01, 2007

TO COUNT(*) or NOT TO COUNT(*)

Counts, we all love to show counts in our applications. Under a high traffic website, it's visually appealing to show a big number. But the cost of generating that big number realtime can be very expensive on our mySQL backend.

For example:


$sql = SELECT COUNT(*) FROM VERY_BIG_TABLE WHERE key_part1=NUM AND no_key IN (1,2,3) AND key_part2=NUM



Would you think this is fast? In a dev environment yes, but this query has to get the exact row count that meets the requirements of the WHERE clause.

In many cases we use a count to solve a BOOLEAN term in our code, for example

$count_result = db_fetch($sql)

if ($count_result) {
// do something
} else {
$SHOW_ERROR = 1;
}


Don't use a count for this purpose. Just do something like



$sql = SELECT PRIMARY_KEY FROM VERY_BIG_TABLE WHERE key_part1=NUM AND no_key IN (1,2,3) AND key_part2=NUM LIMIT 1;



This will tell mysql to return the 1st row found and that's it. In comparison this is about 10 times faster then doing a COUNT(*) under high concurrency.


But what if you need the actual number of rows:

Well, I suggest that what ever triggers an INSERT/DELETE/UPDATE into VERY_BIG_TABLE, do the count(*) at the end of the query and store the result in a meta table or summary table that is small and queried fast.

The draw back is, this makes your application a bit slower on writes, but if you do a lot of reads like most sites do, then caching the count will be a huge win, and this cache account tied to a transaction, makes the count extremely accurate.

16 comments:

Anonymous said...

If it is a MyISAM table COUNT(*) is for free and taken from SHOW TABLE STATUS (rows). You can take the same values for InnoDB, but it would be guessed and might be high or too load, but its faster than really counting all rows.

Anonymous said...

again with the write performance penility, you can also keep a count table, and use triggers off the normal table to keep them accurate.

Dathan said...

Just to clarify this is not a COUNT(*) on a table it's a COUNT(*) for an index column.

Sergi said...

and what's the diference between COUNT(*) amd COUNT(primary_key)

Dathan said...

@sergi

http://www.mysqlperformanceblog.com/2007/04/10/count-vs-countcol/#more-186

Here is a good explanation on counting a col. For PRIMARY keys it should be fine, but I tend to steer away from it, to be consistent.

Ed D said...

Your idea to perform a count(*) after any write is terrible performance-wise and will not be thread-safe. The best way to provide these type of counts is to use Innodb tables and have a trigger that updates a summary table. Since the trigger action as well as the original insert/update will be done in an implicit transaction, you can guarantee that the counts will always be correct regardless of thread access.

With MyIsam, to guarantee a correct , thread-safe count you would have to do something like:

update summary_table set count = (select count(*) from table where key = some_value) where primary_key = a_value;


This would lock both tables for as long as the update and subselect took. You could also do explicit locking of the table. Obviously, these methods do not scale.


Also, the cardinality of the index traversed to answer the count(*) will affect the performance of the count. Including criteria that is outside the index will obviously drastically slow down the query since mysql will have to look at data outside the index node/file.

Ed D said...

Wow, no coffee and I leave a wrong comment. :(

I meant to say that the summary table should be incrementally updated, not fully. You never have to run a count(*) with a summary table. Thus, the update should be (in a trigger or not):

update summary_table set count = count + 1 where primary_key = a_value;


Just with MyISAM or (Innodb and no transaction/trigger), you can't guarantee the accuracy of the counts.

Anonymous said...

MyISAM table COUNT(*) is only free when there is no search predicate. Introducing a search predicate causes the rows to actually get counted one by one.

Dathan said...

UPDATE TABLE SET col=col+1 WHERE id = ?

fails, if one update is missed then your count is invalid.

Anonymous said...

if count(*) is evil, then you should cache these values, maybe using memcached or file cache

trigger count(*) on insert/update/delete can't solve the problem, and remember, write operations are difficult to scale

cruzdelsur said...

Great article Dathan!

any way, your method is fine if you
want to show only total counts, but
is not useful if you need use it with
group by

like:

select name, count(*)
from table_name
group by name

exists some kind of optimization for this?

Anonymous said...

I have seen people that would insert a counting col containing nothing but "1" in each row. To get the actual number of rows you would do a sum() which was supposedly faster than count().

DGermang said...

Some applications use a "soft" delete.
This requires actually summing the delete flag column.

I would like to hear a comment regarding mention that sum() is faster than count()

Anonymous said...

What about using mysql function SQL_CALC_FOUND_ROWS in a previously query, making count use obsolete ?

sexy said...

情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣,情趣,情趣,情趣,情趣,情趣,情趣,情趣,按摩棒,跳蛋,充氣娃娃,情境坊歡愉用品,情趣用品,情人節禮物,情惑用品性易購

免費A片,AV女優,美女視訊,情色交友,免費AV,色情網站,辣妹視訊,美女交友,色情影片,成人影片,成人網站,A片,H漫,18成人,成人圖片,成人漫畫,情色網,日本A片,免費A片下載,性愛

A片,色情,成人,做愛,情色文學,A片下載,色情遊戲,色情影片,色情聊天室,情色電影,免費視訊,免費視訊聊天,免費視訊聊天室,一葉情貼圖片區,情色,情色視訊,免費成人影片,視訊交友,視訊聊天,視訊聊天室,言情小說,愛情小說,AIO,AV片,A漫,av dvd,聊天室,自拍,情色論壇,視訊美女,AV成人網,色情A片,SEX

情趣用品,A片,免費A片,AV女優,美女視訊,情色交友,色情網站,免費AV,辣妹視訊,美女交友,色情影片,成人網站,H漫,18成人,成人圖片,成人漫畫,成人影片,情色網


情趣用品,A片,免費A片,日本A片,A片下載,線上A片,成人電影,嘟嘟成人網,成人,成人貼圖,成人交友,成人圖片,18成人,成人小說,成人圖片區,微風成人區,成人文章,成人影城,情色,情色貼圖,色情聊天室,情色視訊,情色文學,色情小說,情色小說,臺灣情色網,色情,情色電影,色情遊戲,嘟嘟情人色網,麗的色遊戲,情色論壇,色情網站,一葉情貼圖片區,做愛,性愛,美女視訊,辣妹視訊,視訊聊天室,視訊交友網,免費視訊聊天,美女交友,做愛影片

av,情趣用品,a片,成人電影,微風成人,嘟嘟成人網,成人,成人貼圖,成人交友,成人圖片,18成人,成人小說,成人圖片區,成人文章,成人影城,愛情公寓,情色,情色貼圖,色情聊天室,情色視訊,情色文學,色情小說,情色小說,色情,寄情築園小遊戲,情色電影,aio,av女優,AV,免費A片,日本a片,美女視訊,辣妹視訊,聊天室,美女交友,成人光碟

情趣用品.A片,情色,情色貼圖,色情聊天室,情色視訊,情色文學,色情小說,情色小說,色情,寄情築園小遊戲,情色電影,色情遊戲,色情網站,聊天室,ut聊天室,豆豆聊天室,美女視訊,辣妹視訊,視訊聊天室,視訊交友網,免費視訊聊天,免費A片,日本a片,a片下載,線上a片,av女優,av,成人電影,成人,成人貼圖,成人交友,成人圖片,18成人,成人小說,成人圖片區,成人文章,成人影城,成人網站,自拍,尋夢園聊天室

sex said...

徵信社
情趣用品
情趣用品
情趣用品
情趣
情趣


SM
充氣娃娃


SM
性感睡衣


免費視訊聊天室
aio交友愛情館
愛情公寓
情色貼圖
情色文學
情色小說
情色電影
情色論壇
成人論壇
辣妹視訊
視訊聊天室
情色視訊
免費視訊

免費視訊聊天
視訊交友網
視訊聊天室
視訊美女
視訊交友
ut聊天室
聊天室
豆豆聊天室
尋夢園聊天室
聊天室尋夢園
080聊天室
080苗栗人聊天室
女同志聊天室

上班族聊天室
小高聊天室






免費視訊聊天
免費視訊聊天室
免費視訊
ut聊天室
聊天室
豆豆聊天室 聊天室尋夢園
影音視訊聊天室


色情遊戲
寄情築園小遊戲
情人視訊網
辣妹視訊
情色交友

成人論壇
情色論壇
愛情公寓
情色
色情聊天室
色情小說
做愛
做愛影片
性愛


一葉情貼圖片區
情趣用品


辣妹視訊
美女視訊
視訊交友網
視訊聊天室
視訊交友
視訊美女