tag:blogger.com,1999:blog-31421954.post483538462068268904..comments2023-10-30T08:23:12.960-07:00Comments on mySQL DBA, Architecture, Dev, Scale, HA, Code : TO COUNT(*) or NOT TO COUNT(*)Dathan Pattishallhttp://www.blogger.com/profile/00356367514107959723noreply@blogger.comBlogger14125tag:blogger.com,1999:blog-31421954.post-80154066831686027762007-08-20T02:35:00.000-07:002007-08-20T02:35:00.000-07:00What about using mysql function SQL_CALC_FOUND_ROW...What about using mysql function SQL_CALC_FOUND_ROWS in a previously query, making count use obsolete ?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-31421954.post-88390851502095324312007-06-27T15:09:00.000-07:002007-06-27T15:09:00.000-07:00Some applications use a "soft" delete.This require...Some applications use a "soft" delete.<BR/>This requires actually summing the delete flag column.<BR/><BR/>I would like to hear a comment regarding mention that sum() is faster than count()Dennishttps://www.blogger.com/profile/14019621303337970239noreply@blogger.comtag:blogger.com,1999:blog-31421954.post-39405336335869602202007-06-25T07:17:00.000-07:002007-06-25T07:17:00.000-07:00I have seen people that would insert a counting co...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().Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-31421954.post-48784246112464430492007-06-23T09:44:00.000-07:002007-06-23T09:44:00.000-07:00Great article Dathan!any way, your method is fine ...Great article Dathan!<BR/><BR/>any way, your method is fine if you<BR/>want to show only total counts, but<BR/>is not useful if you need use it with <BR/>group by <BR/><BR/>like:<BR/><BR/>select name, count(*)<BR/>from table_name<BR/>group by name<BR/><BR/>exists some kind of optimization for this?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-31421954.post-21581831245497573032007-06-23T03:24:00.000-07:002007-06-23T03:24:00.000-07:00if count(*) is evil, then you should cache these v...if count(*) is evil, then you should cache these values, maybe using memcached or file cache<BR/><BR/>trigger count(*) on insert/update/delete can't solve the problem, and remember, write operations are difficult to scaleAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-31421954.post-35421953598642668012007-06-22T09:31:00.000-07:002007-06-22T09:31:00.000-07:00UPDATE TABLE SET col=col+1 WHERE id = ?fails, if o...UPDATE TABLE SET col=col+1 WHERE id = ?<BR/><BR/>fails, if one update is missed then your count is invalid.Dathan Pattishallhttps://www.blogger.com/profile/00356367514107959723noreply@blogger.comtag:blogger.com,1999:blog-31421954.post-70568683262414336392007-06-22T09:16:00.000-07:002007-06-22T09:16:00.000-07:00MyISAM table COUNT(*) is only free when there is n...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.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-31421954.post-12949484419732043702007-06-22T07:40:00.000-07:002007-06-22T07:40:00.000-07:00Wow, no coffee and I leave a wrong comment. :(I m...Wow, no coffee and I leave a wrong comment. :(<BR/><BR/>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):<BR/><BR/>update summary_table set count = count + 1 where primary_key = a_value;<BR/><BR/><BR/>Just with MyISAM or (Innodb and no transaction/trigger), you can't guarantee the accuracy of the counts.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-31421954.post-60017785999520241172007-06-22T07:29:00.000-07:002007-06-22T07:29:00.000-07:00Your idea to perform a count(*) after any write is...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.<BR/><BR/>With MyIsam, to guarantee a correct , thread-safe count you would have to do something like:<BR/><BR/>update summary_table set count = (select count(*) from table where key = some_value) where primary_key = a_value;<BR/><BR/><BR/>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.<BR/><BR/><BR/>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.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-31421954.post-4727284290481504232007-06-04T12:08:00.000-07:002007-06-04T12:08:00.000-07:00@sergihttp://www.mysqlperformanceblog.com/2007/04/...@sergi<BR/><BR/>http://www.mysqlperformanceblog.com/2007/04/10/count-vs-countcol/#more-186<BR/><BR/>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.Dathan Pattishallhttps://www.blogger.com/profile/00356367514107959723noreply@blogger.comtag:blogger.com,1999:blog-31421954.post-8619906665138420742007-06-03T10:31:00.000-07:002007-06-03T10:31:00.000-07:00and what's the diference between COUNT(*) amd COUN...and what's the diference between COUNT(*) amd COUNT(primary_key)Sergihttps://www.blogger.com/profile/18332135143949346785noreply@blogger.comtag:blogger.com,1999:blog-31421954.post-33299234770630603432007-06-02T14:46:00.000-07:002007-06-02T14:46:00.000-07:00Just to clarify this is not a COUNT(*) on a table ...Just to clarify this is not a COUNT(*) on a table it's a COUNT(*) for an index column.Dathan Pattishallhttps://www.blogger.com/profile/00356367514107959723noreply@blogger.comtag:blogger.com,1999:blog-31421954.post-58934114821405950592007-06-02T12:27:00.000-07:002007-06-02T12:27:00.000-07:00again with the write performance penility, you can...again with the write performance penility, you can also keep a count table, and use triggers off the normal table to keep them accurate.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-31421954.post-82995348011653447022007-06-02T02:12:00.000-07:002007-06-02T02:12:00.000-07:00If it is a MyISAM table COUNT(*) is for free and t...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.Anonymousnoreply@blogger.com