Prestashop & MySQL Indexes

[cunni]

Hey all,

I hope someone can shine some light on this...

Prestashop is awesome! It loads fast on the front end and im very pleased with its performance with regards to the customers point of view, but im in a situation where if i start to use the back office the front end slows down massively, and the back office can and does comes to a stand still...

Now It only happens when I want to edit or update a product,

just so you get an idea on the size of the products attributes that im currently running for all of our product together.

Table name : product_attribute
Keyname Type Cardinality (records)
PRIMARY PRIMARY 121,012

Table name : product_attribute_combination
Keyname Type Cardinality (records)
PRIMARY PRIMARY 543,890

Now I've not had any issues with this until this week, and now what happens is the product admin pages load but some how get into a loop and cause the MySQL server to stall with processes running for ages.

Is there anyway I can get some sort of indexing working on the product_attribute_combination table to increase the speed of the Back Office, The combinations are causing the entine product page to now not load at all in the back office.

Any advice, even if its only basic is appreciated, as i need to be able to update these products even if it means the combinations are updated seperately.

Kind Regards
Dan.

[cunni]

Ok a bit more info, Upon the "Save and stay" button click the MySQL Query runs for 300 seconds, (5 minutes) then it starts over again. As if its in some kind of loop. This in tern is actually killing my SQL server as its like a some sort of memory leak.

Can anyone help at all? I can only think that the product_attribute_combination table needs some sort or better Indexes to help improve the performance and stop the loop.

[tomerg3]

This is a known prestashop problem, it's not a question of indexing, but rather design.

The current attribute structure (creating a physical combination) is not scalable at all, if you create more than 1000-3000 combinations, you will run into memory or db issues.

The only way to overcome this is to bypass the default attribute combination structure, you can either customize it yourself / hire someone for it, or you can try out my Attribute Wizard Pro module http://www.prestashop.com/forums/viewthread/47363/ which does just that and much more.

[cunni]

Hi tomerg3

Ah right, ok. Cheers for the info.

Have you got a demo version of your module I can test on my site? Im not sure this will work along side my current setup. If thats the case, it will probably be quicker to write something or have someone write something into the admin area where the combinations are seperate from the product edit page, as its the combinations are are actually stopping the products from being updated in the back office.

Cheers
Dan.

[cunni]

Just thinking aloud now...

But to over come this issue, would increasing the memory limits on PHP and MySQL services help at all? Like i said it loops at 300 seconds which to me looks like a timeout from either php or mysql.

E.G the MySQL config
myisam_sort_buffer_size
max_heap_table_size
These default values are usually very low.

So surely you "could" increase the memory allowance and get past the issue of having 500,000+ records in the combinations table. That surely would speed up the Selects and updates etc?

Just an idea. Anyone have any thoughts?

Dan.

[tomerg3]

It's more complicated than that, the combination for each product need to be loaded into memory on the product page, also, there's a javascript line for each one and javascript code that loops though all of them, which will take a long time and may hang the page altogether (in the front end).

This is more than just a database issue, actually, the database is the least of the problems here, since Mysql can handle tables with millions of records.

I have a front + back end demo of this module (address is available on my site), I do not offer any trial versions of modules, since once you have it, I have no way to control whether you use it or pay for it.

[cunni]

Cheers tomerg3, I sent you a PM with a screen shot attached of my issue. The front end is fast, It literally hangs only in the admin area when you go to edit a product due to the shear amount of combinations, it just hanfs.

Im still unsure that you're code modification will fix this issue i have, and parting with $120 to later find out that your combination module doesnt work is a risk i can't afford to take unfortunatly... If it does work then $120 isn't a problem and im happy to pay that! But I can't risk that sort of money right now on this project.

Can you please have a read of the PM i sent you as it has some details in which i dont want to share publically on the forum e.g the data etc.

Thanks for your help so far.

Kind Regards
Dan.