OpenCart 3 – TTFB is longer than 16seconds

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 index.

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:

https://drive.google.com/file/d/1xyXJqwGNModWt7WevtsD0gXVPxX9p2Zl/view

2,000+ SQL queries and queries like

SELECT * 
FROM `oc_setting` 
WHERE store_id = '0' OR store_id = '0' 
ORDER BY store_id ASC

Costing me 5-19seconds where the same query if I run in phpmyadmin costs me less than 0.0011 seconds

Need help in optimizing the website load speed. TIA

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s