I have been working on a OpenCart 3 project, initially the speed was good and it didn’t make any difference. All of a sudden the first byte has exceeded to 30sec -40sec or more.
I did some research to understand why, which is when I end up with this blog post, which describes why the speed issue https://bloke.org/php/opencart-is-slow-with-many-categories/
I followed the same hoping it will help me solve the problem. The solution that was suggested is to add
Here’s what I did,
ALTER TABLE `oc_category` ADD INDEX `parent_id` (`parent_id`); ALTER TABLE `oc_product_to_category` ADD INDEX `category_id` (`category_id`); ALTER TABLE `oc_category_description` ADD INDEX `lanuguage_id` (`language_id`); ALTER TABLE `oc_category_to_store` ADD INDEX `store_id` (`store_id`); ALTER TABLE `oc_product_attribute` ADD INDEX `attribute_id` (`attribute_id`), ADD INDEX `language_id` (`language_id`); ALTER TABLE `oc_product` ADD INDEX `manufacturer_id` (`manufacturer_id`); ALTER TABLE `oc_product_description` ADD INDEX `language_id` (`language_id`); ALTER TABLE `oc_product_to_store` ADD INDEX `store_id` (`store_id`); ALTER TABLE `oc_category_path` ADD INDEX `path_id` (`path_id`); ALTER TABLE `oc_url_alias` ADD INDEX ` url_alias_id` (`url_alias_id`);
I was in an assumption that this will do the magic that it did to other fellow mates. But unfortunately, it didn’t. Time is important for any website especially the
TTFB, hence I wanted to understand what’s causing the issue.
I have other Opencart installation which is both multivendor and single vendor but none gives this kind of worst performance, and all have the same modules except for the theme. Which lead me to see what are the SQL queries being used by Opencart to populate the web page content. I used this module Debug Database Queries
Here’s what I saw next:
2,000+ SQL queries and queries like
SELECT * FROM `oc_setting` WHERE store_id = '0' OR store_id = '0' ORDER BY store_id ASC
5-19seconds where the same query if I run in
phpmyadmin costs me less than
Need help in optimizing the website load speed. TIA