[SOLVED] Category creating/removing is very slow

[japeter]

Hello, I'm using Prestashop 1.4.8.2. My problem is, that adding category into catalog, or it's removing in backoffice is very slow. Also changing its position takes too much time. For example in my catalog there is few hundereds of categories. Adding next category can take few MINUTES. When watching database I can see, that there is lot of queries like this

 

UPDATE ps_category SET nleft =126,

nright =127 WHERE id_category =751 LIMIT 1

 

 

They doesn't take large amount of time itself, but when adding single category, there are thousands of these queries.

 

 

Is this behaviour normal?

 

Thank you very much.

Edited August 11, 2012 by japeter (see edit history)

[shacker]

make a backup of your database, and try this module

 

http://catalogo-onlinersi.net/en/back-office-prestashop-modules/217-delete-connections.html

 

 

this speed up your database

[japeter]

Thank you very much for your advice. Unfortunatelly, after installing this module, I cant get (or dont know how) it to work. In Modules>Administration>Delete connections..... Settings. Here is only submit button with delete all fields text. When clicking it, I'cant see, any change in DB or performance.

 

 

How should I use this module ?

 

Thank you very much.

[shacker]

but show nothing? or show the results?

[japeter]

It doesn't show nothing, after clicking on delete all fields button, Modules page is opened with message - removing of modules has been successfull showed on top of the page and next to delete connections module entry in table.

 

Thank you very much for your time

[japeter]

I have noticed, that in module there is a bug in query "SHOW TABLE STATUS FROM `presta-shop`" problem is quoting of database name... but, after repairing this and running script from commandline I cant still get no performance speedup. (I didn't see output for first time, but now it shows everywhere zero rows,... and successes)

 

Deleting single category with 1000 products takes few minutes. (Server isn't such a poor device, It copes very well with load 160php-reqs in second daily without problem, so I think this wont be bottleneck)

 

Even setting image for category invokes many of like this queries...

 

UPDATE ps_category SET nleft =1349,

nright =1350 WHERE id_category =1042 LIMIT 1

 

and also it takes very long time.

 

Any suggestions ?

 

Thank you very much.

Edited August 15, 2012 by japeter (see edit history)

[japeter]

I have commented out function Category::_subTree(,,) and performance speedup is great. I dont know what it exactly does, but I think that here will be problem with category adding. But this is not very frequently operation so I can let it on some cron job.

 

I think that this function is real bottleneck of prestashop, because when have few hundreds categories, even image change of category is too slow. May it can be optimized in next version ?

 

Thank you very much.

[benjamin utterback]

Thank you japeter for you updates. I'm happy you were able to solve your problem. I will let the dev department know about this issue that you had. For now I will mark this issue as solved. Thank you for the message on how you solved this issue. Thank you for choosing PrestaShop!

[shacker]

thanks for post the solution