Post by vildamatilda » Wed Mar 07, 2012 6:04 am

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 ;D 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

http://www.barnbutikenvildamatilda.se
Version: 1.5.1.3


New member

Posts

Joined
Sun Aug 21, 2011 5:55 pm

Post by nodric » Fri Mar 09, 2012 11:53 am

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.

The Best 4x4xfar


Active Member

Posts

Joined
Wed Jul 06, 2011 10:39 pm

Post by annelim » Fri May 18, 2012 7:56 pm

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?

Active Member

Posts

Joined
Fri Oct 28, 2011 5:13 pm

Post by JAY6390 » Fri May 18, 2012 8:56 pm

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

Image


User avatar
Guru Member

Posts

Joined
Wed May 26, 2010 11:47 pm
Location - United Kingdom

Post by annelim » Sat May 19, 2012 12:19 pm

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

Active Member

Posts

Joined
Fri Oct 28, 2011 5:13 pm

Post by Aimeeing » Sat May 19, 2012 2:09 pm

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.

Newbie

Posts

Joined
Sat May 19, 2012 2:08 pm


Post by madimar » Tue May 22, 2012 5:59 pm

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

-----------------------------------------------------------------------
My last mods: Partita IVA e CF | Pro EU VAT Number | Sales Agents | Pricelist Pro
-----------------------------------------------------------------------


User avatar
Active Member

Posts

Joined
Thu Sep 24, 2009 6:27 pm


Post by JAY6390 » Tue May 22, 2012 7:32 pm

The above should pretty much solve it again Madimar yes

Image


User avatar
Guru Member

Posts

Joined
Wed May 26, 2010 11:47 pm
Location - United Kingdom

Post by bluelayer » Mon Aug 20, 2012 6:08 am

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

v1.5.5.1


New member

Posts

Joined
Sun Oct 17, 2010 8:11 pm

Post by JoNails » Wed Aug 22, 2012 10:17 am

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.

Newbie

Posts

Joined
Sun May 13, 2012 4:49 am

Post by JoNails » Tue Oct 30, 2012 3:40 am

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.

Newbie

Posts

Joined
Sun May 13, 2012 4:49 am

Post by jsmoove » Thu May 02, 2013 4:33 am

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

New member

Posts

Joined
Fri Oct 09, 2009 10:33 am

Post by therugworld » Wed Aug 07, 2013 9:33 pm

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

Thanks!

http://www.TheRugWorld.co.uk - Rugs, Carpet, Vinyl & Laminates! FREE UK Delivery - Worldwide Shipping!


New member

Posts

Joined
Sat Mar 10, 2012 1:12 am
Location - London

Post by rodricrzz » Tue Sep 10, 2013 12:48 pm

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?

User avatar
Active Member

Posts

Joined
Fri Jun 03, 2011 4:05 pm
Location - Avellaneda, Buenos Aires

Post by Spiritcraft » Mon Sep 30, 2013 6:39 am

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

Newbie

Posts

Joined
Sun Jun 23, 2013 12:30 pm

Post by barelius » Tue Oct 22, 2013 7:15 pm

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 ?

Newbie

Posts

Joined
Tue Oct 22, 2013 7:12 pm

Post by thegurge » Wed Jun 25, 2014 1:17 pm

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 :)

Newbie

Posts

Joined
Fri Jun 13, 2014 7:30 am

Post by khnaz35 » Wed Oct 24, 2018 5:50 pm

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

Urgent Questions shoot here: khnaz35@gmail.com
Enjoy nature ;) :) :-*


User avatar
Active Member

Posts

Joined
Mon Aug 27, 2018 11:30 pm
Location - Malaysia

Post by khurrum » Sun Feb 09, 2020 10:58 pm

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.

Newbie

Posts

Joined
Wed Oct 23, 2013 10:36 pm

Post by IP_CAM » Sun Feb 09, 2020 11:13 pm

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 .... :choke:
Ernie

My Github OC Site: https://github.com/IP-CAM
5'200 + FREE OC Extensions, on the World's largest private Github OC Repository Archive Site.


User avatar
Legendary Member

Posts

Joined
Tue Mar 04, 2014 1:37 am
Location - Switzerland
Who is online

Users browsing this forum: Baidu [Spider] and 141 guests