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:
PLAIN TEXT SQL:
- UPDATE counter SET …