show rows in category with minimal execution time

You’ve all seen or made by now an e-commerce shop. If you look at big shops, they use to show right next to the category name the number of products available in that category – ie: Computers (190). Wel, once again you can do that in more that one way.

First way (and the wrong one if you ask me) is when you select the categories from your database to count the number of products from the database that belong to that category. But the problem with this is that you use much too much resources and when your database grows, you script will slow, sometimes even die. As I said before, this might look easy but is wrong.

The way I prefere is by actually storing the number of products in your database. Let’s assume you have the following categories table in your database:

id | category_name
——————–
1 | computers
2 | some other thing
…

What you could do is add a new column that will store the number of products. Make it INT NOT NULL DEFAULT 0 so that when you add a new category it will automatically be set on 0. And when you add a new product to any category, you simply increase the number already stored in your table for that specific categoy.

INSERT INTO my_product_table SET
column1='$variable1' ...

and aditionally add one more update sql:

UPDATE my_category_table SET
product_number=product_number+1
WHERE category_id='$the_category_you_added_product_to';

And that will be it. Now, when you select you categories to show the menu to your users, all you have to do is simply show the value from the column you stored the number of products and you’re done. Simple like that. Ofcourse, you must remember to decrement the value from this column when you delete any products.

Once again, keep safe and fast.

Was this useful? Show your support.