I guess many of you know, that using SELECT count(*) FROM table is problematic and slow when using Innodb tables.
This actually only applies to COUNT(*) queries without WHERE a clause as mentioned in the MySQL Performance Blog.

But if you got some slow count query in your application the best way to increase its performance is to replace / remove it.

So if you are going do to “SELECT count(*) FROM products” the best way, is to have a separated table
that stores the number of products. If you’re inserting a row increment the counter, if you’re deleting a row, decrement it.

Here is some example:

CREATE TABLE counter( number_of_products int(10) DEFAULT '0' NOT NULL);

Increment when you’re adding a new product to the products table:

UPDATE counter SET number_of_products = number_of_products +1;

Decrement when you’re removing a product:

UPDATE counter SET number_of_products = number_of_products -1;

In one of my applications i have the case that i have many reads on a table, but just a few updates / writes to the table. The count was in my case not related to a table, but specific to a row of it.

Example:

CREATE TABLE user (
user_id int(10) DEFAULT '0' NOT NULL,
username int(10) DEFAULT '0' NOT NULL,
...
number_of_purchases int(10) DEFAULT '0' NOT NULL,
);

Scenario:
Just imagine that the number of purchases a user has made is somehow complicated to calculate and query. That’s why we store the number directly in the user table.
We also have a website that produces a lot of reads to this table column, but (unfortunately) its quite seldom that a user purchases something. So using this complicated count query in each website impression would be slow.

What comes handy here is using MySQL 5 triggers.
With triggers we can calculate the number_of_purchases each time a purchase is made by a user and update the users number_of_purchases column.

Example:

delimiter //
create trigger trg_update_user_purchases after update on purchases
 for each row begin
call count_and_update_user_purchases(new.user_id);
 end;
//
delimiter ;

delimiter //
create trigger trg_insert_user_purchases after insert on purchases
 for each row begin
call count_and_update_user_purchases(new.user_id);
 end;
//
delimiter ;

delimiter //
create trigger trg_delete_user_purchases after delete on purchases
 for each row begin
call count_and_update_user_purchases(new.user_id);
 end;
//
delimiter ;

So each time a records in the purchase table is inserted, updated or deleted the user table gets automaticly updated.

Your application gains the performance from selecting a static int field instead of executing complex count queries. Additionally your application is not bloated with unnecessary code to increment and decrement the count column, because the trigger does this automatically for you.

Popularity: 27% [?]