0

We have been working on a Magento 2.4 multi store. On Dedicated server, 24 Cores, 192GB RAM, MySQL 5.7, PHP 7.4

One of the stores has 300k SKU. When loading the homepage and product pages, the speed is not so bad however when loading a brand page or category page, the load time is 30+ seconds first page load and 2 seconds on second page page load.

We had a specialist investigate, and his reply is to do the following:

......................................

*Ask Magento to consider in their base code for the 3 queries identifiable from the slow query log content (Magento may find there are one, two or three queries involved in this sequence)

A) changing any

website_id = '2'

to

website_id = 2

This will:

  1. avoid overhead of datatype conversions for this int datatype field to conserve CPU cycles
  2. allow the optimizer to choose the correct index to use for the table.

and B) changing any

ORDER BY rand() ASC

to

ORDER BY e.entity_id

to avoid rand() overhead.*

EXPLAIN SELECT ..... before and after will go from ~ 1,225,644 rows examined to less than 100 rows examined which take more than 20 seconds

......................................

My Question, Do these suggestions have validation and is it possible to make these changes without breaking Magento?

Looking forward to any advice on this matter.

Thank you

Regards, Mick O

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.