Slow query on media repository during plugin installation

[wmouwen]

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.

        $criteria = new Criteria();
        $criteria->addFilter(new EqualsFilter('fileName', $fileName));

        /** @var MediaCollection $icons */
        $icons = $this->mediaRepository->search($criteria, $context);

        if ($icons->count() && $icons->first() !== null) {
            return $icons->first()->getId();
        }

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.

[boxblinkracer]

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?

[wmouwen]

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.