Open wmouwen opened 10 months ago
Hi
oh...phu....fileName has no index....ähm thats a problem indeed the thing is, I need to fetch that data somehow
what might be possible is this i could only do this in the create-payment IF condition that means its only for new payments, then I could in theory remove the repo fetching and just insert it into the payment method with that approach, it would be a bit more risky in theory, but in reality it shouldnt cause any troubles
what do you think?
We noticed another Shopware plugin suffering from the exact same issue, so we ended up creating an index on the file_name
column manually to solve the problem for both plugins at the same time.
Note that the file_name
column in Shopware 6 is a LONGTEXT
so we need to explicitly state a key length.
create index `idx.file_name_index`
on media (file_name(8))
algorithm = inplace
lock = none;
With the 400k entities in the table, creating the index took MySQL about 5 seconds after which the image queries executed near instantly.
Note that this is a solution for our usecase, having this plugin create the index on a core table might be overstepping some (unwritten) shopware rules.
During the installation process of the plugin, it tries to download images and add them to the media repository (link). Before downloading the images, it checks the repository to see if they don't already exist.
https://github.com/mollie/Shopware6/blob/5c83ec9617ab7e8fddf7b628f2562b07004f2a8d/src/Service/PaymentMethodService.php#L411C8-L415C28
The
fileName
field isn't indexed in MySQL by Shopware. As a result, the query it executes in the background takes 20 minutes per payment method on our test environment, which has about 400k entities in the media repository.