Increase the speed of loading the opencart sites

I am working as the opencart programmer for 6 months and found out that opencart as the best open source for the e-commerce site.
Yet some of the things to be taken into consideration which obviously increases the speed of the opencart sites.

We all know that indexing the table increases the performance of the database so creating the following index really increase the speed in the opencart sites. Run the query below and enjoy some little speed on loading your sites.

ALTER TABLE `category` ADD INDEX ( `parent_id` ) ;
ALTER TABLE `category` ADD INDEX ( `top` ) ;
ALTER TABLE `category` ADD INDEX ( `sort_order` ) ;
ALTER TABLE `category` ADD INDEX ( `status` ) ;
ALTER TABLE `option` ADD INDEX ( `sort_order` ) ;
ALTER TABLE `option_description` ADD INDEX ( `name` ) ;
ALTER TABLE `option_value` ADD INDEX ( `option_id` ) ;
ALTER TABLE `option_value_description` ADD INDEX ( `option_id` ) ;
ALTER TABLE `order` ADD INDEX ( `customer_id` ) ;
ALTER TABLE `product` ADD INDEX ( `model` ) ;
ALTER TABLE `product` ADD INDEX ( `sku` ) ;
ALTER TABLE `product` ADD INDEX ( `upc` ) ;
ALTER TABLE `product` ADD INDEX ( `manufacturer_id` ) ;
ALTER TABLE `product` ADD INDEX ( `sort_order` ) ;
ALTER TABLE `product` ADD INDEX ( `status` ) ;
ALTER TABLE `product_option` ADD INDEX ( `option_id` ) ;
ALTER TABLE `product_option_value` ADD INDEX ( `product_option_id` ) ;
ALTER TABLE `product_option_value` ADD INDEX ( `product_id` ) ;
ALTER TABLE `product_option_value` ADD INDEX ( `option_id` ) ;
ALTER TABLE `product_option_value` ADD INDEX ( `option_value_id` ) ;
ALTER TABLE `product_tag` ADD INDEX ( `product_id` ) ;
ALTER TABLE `product_tag` ADD INDEX ( `tag` ) ;
ALTER TABLE `url_alias` ADD INDEX ( `query` ) ;
ALTER TABLE `url_alias` ADD INDEX ( `keyword` ) ;
ALTER TABLE `user` ADD INDEX ( `username` ) ;
ALTER TABLE `user` ADD INDEX ( `password` ) ;
ALTER TABLE `user` ADD INDEX ( `email` ) ;

Likewise, we have to remove unnecessary queries which obviously increase the speed of the site.

Queries that can be removed are the category count in the opencart

Use the steps below to remove the category count:

Go to the file:
/catalog/controller/module/category.php
1.Search CODE:

‘name’ => $child[‘name’] . ‘ (‘ . $product_total . ‘)’,

Replace with CODE:

‘name’ => $child[‘name’],

2.Search CODE:

‘name’ => $category[‘name’] . ‘ (‘ . $product_total . ‘)’,

Replace with CODE:

‘name’ => $category[‘name’],

3. Search CODE:

‘name’=> $result[‘name’] . ‘ (‘ . $product_total . ‘)’,

Replace with CODE:

‘name’  => $result[‘name’],

This will remove the product count in the category module.

If there is product count in the top menu bar then you can remove them as well.

For the top bar go to /catalog/controller/common/header.php

Search CODE:

‘name’  => $child[‘name’] . ‘ (‘ . $product_total . ‘)’,

And Replace with CODE:

‘name’ => $child[‘name’],