Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Ecommerce Performance | CartId in OnlineShopOrder is not indexed in query table #5477

Open
andreas-gruenwald opened this issue Dec 18, 2019 · 1 comment
Assignees
Labels

Comments

@andreas-gruenwald
Copy link
Contributor

@andreas-gruenwald andreas-gruenwald commented Dec 18, 2019

Performance Improvement

Problem Summary

On loading carts, in Pimcore 5.8 there are several verifications, if an order already is assigned to the cart. A slow query log analysis showed that the associated SQL query does not utilize any SQL index. This may cause one (or several) full scans of the OnlineShopOrder query table per cart retrieval.

Problem Details

The following query does not use an index:

SELECT object_EF_OSO.o_id as o_id, `object_EF_OSO`.`o_type` FROM `object_EF_OSO` WHERE ((cartId = 'AppBundle\\Ecommerce\\CartManager\\Cart_2596' AND  object_EF_OSO.o_type IN ('object','folder')) AND object_EF_OSO.o_published = 1);

The source of the problem is the declaration of the field cartId in the OnlineShopOrder data model:
image

The field is marked as indexed. But due to the column length saving the data model results in an exception:

 An exception occurred while executing 'ALTER TABLE `object_query_EF_OSO` ADD INDEX `p_index_cartId` (`cartId`);': SQLSTATE[HY000]: General error: 1709 Index column size too large. The maximum column size is 767 bytes. 

Pimcore does not throw this error, as exceptions on index updates are suppressed.
As a result, the data model can be saved, but no index is set onto the field.

Solution Concept

Setting the column length to "75" resolves the issue and will suffice in most cases. Typically namespaces to not exceed this length. Probably it is sufficient to update the class definition in Pimcore and add an alter statement to the migrations?

A long term solution may include that only the actual cartId (numeric value) is saved in the OnlineShopOrder object, not the entire namespace.

@brusch

This comment has been minimized.

Copy link
Member

@brusch brusch commented Dec 18, 2019

Yep, I'd say it should be sufficient to update the class definition and set the length to 75, that should be by far enough. + a migration script for updating OnlineShopOrder if exists already

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
3 participants
You can’t perform that action at this time.