OpenCart Community

nodric wrote:If it helps anyone finding this, our search, and general site speed was slowing down now we had grown to +300 categories, and over 1000 products, with over 300 options.

I added most of what Simon suggested (my suggestions are below) and the site is now like lighting! Near instantaneous, and here I was bitching to my host I should have remembered to be a good DBA!

Code: Select all

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` ) ;

possible to get this to work on the version 1.4.9.3?? experience my page real slow and do not know why or what happened. Is there anyone who can look at my page and see if this module may be able to fix the problem.
www.webshop.barnbutikenvildamatilda.se
appreciate all the help I can get

You need one of the OC experts to help you. I just followed Simon's suggested route. However, these are SQL indexes, so they can be applied to any SQL DB, but I don't know the structure of 1.4.x OC. Hopefully someone will answer who does.

does V1.5.2.1 need to do so ? cause i try run it but it show
ALTER TABLE `category` ADD INDEX ( `parent_id` ) ;# 1907 rows affected.
ALTER TABLE `category` ADD INDEX ( `top` ) ;# 1907 rows affected.
ALTER TABLE `category` ADD INDEX ( `sort_order` ) ;# 1907 rows affected.
ALTER TABLE `category` ADD INDEX ( `status` ) ;# 1907 rows affected.

does any one know what is it mean?

Those updates are correct, that just means it's added the index to the current categories

thank you JAY.

what does it mean for this ? it happen last night when category is loading
Notice: Undefined index: parent_id in catalog/controller/module/category_superfish.php on line 81

I first ran the two lines recommended by you and ran a search and the time was still awful and then I ran the rest of the commands as recommended by uksitebuilder and the search was very fast.

What about a 1.5.2.x release? Can we use the same 1.5.1.3 script?

The above should pretty much solve it again Madimar yes

the new v1.5.4 version include new searchable boxes... they become with mysql index already? (or something like...)

I have Version 1.5.0. Will the code work for this version? I tried running the query and there was an error. Please help.

Is there any updated script for V. 1.5.4? I had tried to run the script but it gave me so much errors and bug on my site.

Hi,

I really need some help. I ran indexes, checked on MyPHPAdmin for duplicate indexes and cleared them, and checked everything. My site runs searches to include sub-categories and it's not loading the products for newer sub-categories for some reason.

Thanks in advance

Would this be useful for v 1.5.3 or is this already integrated in this version? Someone please reply if you know.

Thanks!

uksitebuilder wrote:Run these commands in PHPMyAdmin

Code: Select all

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` ) ;

This works for v1.5.4.1?

How about v1.5.5.1? Is this something I should do?

Tried the Database modifications by adding INDEX, this is helping the first 2-3 times I'm requesting the page. After 2-3 pageviews of the Category pages I'm getting back to the old 35 second from the 1-2 seconds execution.

I'm using VQmod and Page Cache, can this affect ?

Loaded a sample 1000 products to the site i'm converting and it slowed down to a crawl.

These indexes have solved that problem.

Saved me having to start fiddling around working out what indexes to create.

Thanks

uksitebuilder wrote: ↑

Sat Mar 03, 2012 5:13 pm

If your tables are prefixed, you should add the prefix to each table name in the code posted above

Is this rule can be applied on OC 2.3.0.2 as well i know this is a very old topic

uksitebuilder wrote: ↑

Tue Aug 30, 2011 2:54 pm

Run these commands in PHPMyAdmin

Code: Select all

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` ) ;

can i use the same method to solve my issue on Version 2.1.0.2? my search results are extremely and take several minutes to show results. pls check charjishop.com and suggest any solution pls. thanks for your valuable advice and support.

The indexing as shown would not work, a DB-Prefix (i.E.: `oc_category`) needs to be part
of each line, if the DB Prefix in your DB is named oc_, otherwise change it to match your setting.
You could also try the Extensions below:
https://www.opencart.com/index.php?rout ... n_id=35767
https://www.opencart.com/index.php?rout ... n_id=26678
https://www.opencart.com/index.php?rout ... n_id=24978
But you also seem to have other problems too, since your Site is loading endless ....
Ernie