Day 1: The road to achieve Optimization seems deep but the first step this time is easy to achieve and with great effects to performance. Opencart has by default a counter enabled for categories that displays the number of products per category, these counters (when you have a combination of many categories + products) really slows down your website.
The reason of the slowdown is the usage of the MySQL function COUNT(*) although Opencart database tables are ISAM thus MySQL thus they do not have to to scan the table indexes the slowdown still occurs. Opencart’s structure and methodology in MySQL queries does not allow this query to be efficient as you have to JOIN many tables and then COUNT the results meaning that the scan will still occur regardless of the usage of the database engine ISAM.
The only thing that this code offers is a not that much-needed number of products per category in menus, when you are using pagination this number is required to have the results appear correctly. So what we need to do is go on and change the code in the Controller as shown in the example below for each occurrence except from the cases they are used for pagination. Some custom themes or extensions may use code of their own so you have to do an extensive check, but i guarantee you it is worth it.
For Example in Catalog/Controller/module/category.php :
=> You need to find this part of the code
$product_total = $this->model_catalog_product->getTotalProducts($data); $children_data = array( 'name' => $child['name'] . ' (' . $product_total . ')', 'href' => $this->url->link('product/category', 'path=' . $category['category_id'] . '_' . $child['category_id']) );
=> And replace it with the code below
//$product_total = $this->model_catalog_product->getTotalProducts($data); $children_data = array( 'name' => $child['name'], 'href' => $this->url->link('product/category', 'path=' . $category['category_id'] . '_' . $child['category_id']) );
PS. There is a configuration in the back-end of Opencart in System > Settings > your_store in the Options tab that you may disable it is named “Category Product Count” and it claims it disables the COUNT so better be sure and keep it disabled.
guide intro : Opencart Optimization Guide
Second day : Excessive Code Maintenance