So i'm in the middle of an issue right now where the page loading speed is not up to par to what we're looking for and i'm honestly out of ideas for now and i hoped maybe someone smarter can lead me in the right direction.
First of all, the environment:
The site is hosted on a cloud nginx server with 32GB of RAM.
The site uses a Porto theme with WP Bakery builder. I know i know, page builders aren't famous for speed but testing the same site with storefront and without bakery shows no difference.
The site is multilingual and for translation we use WPML. We've thought about using seperate language files instead but it is quite an hassle to get those strings out of wpml.
Now the situation:
Without any caching plugins / optimizers we get a 3-5s load speed. With WP Rocket + Redis we get the load speed less than a second. Now when the user visits a page and it is isnt yet cached by Redis (recently flushed) we'd like to prevent the user from waiting 3-5s to initally load the pages.
What i've tried (without caching):
Using QM to monitor and find plugins/scripts affecting the performance.
Database indexing and cleanup tips found online.
Image optimization.
Query monitor shows me that on the shop page ( slowest ), wp posts main query takes more than 1 second to complete then adding additional 2-4s to display the data.
Using Scalability PRO plugin i tried limiting the time it takes to execute the query but honestly it didn't help much. None of the sql changes really helped alot nor did any other settings inside the plugin. Maybe a 1s difference.
Only real difference is seen when wp rocket and redis are active but thats not an ideal solution. Is there a way to make the site run any faster or to make it seem like it does?
Here's all the data i can get for /shop page
Query
Caller: WP_Query->get_posts()
Rows: 12
Time (s): 1.4356
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
JOIN wp_icl_translations wpml_translations ON wp_posts.ID = wpml_translations.element_id
AND wpml_translations.element_type = CONCAT('post_', wp_posts.post_type)
WHERE 1=1
AND (wp_posts.ID NOT IN
(SELECT object_id
FROM wp_term_relationships
WHERE term_taxonomy_id IN (7,
9) ))
AND ((wp_postmeta.meta_key = '_price'
AND wp_postmeta.meta_value > '0'))
AND ((wp_posts.post_type = 'product'
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'private')))
AND (((wpml_translations.language_code = 'en'
OR (wpml_translations.language_code = 'en'
AND wp_posts.post_type IN ('post',
'page',
'custom_css',
'customize_changeset',
'oembed_cache',
'user_request',
'product',
'product_variation',
'shop_order_refund')
AND ((
(SELECT COUNT(element_id)
FROM wp_icl_translations
WHERE trid = wpml_translations.trid
AND language_code = 'en' ) = 0)
OR (
(SELECT COUNT(element_id)
FROM wp_icl_translations t2
JOIN wp_posts p ON p.id = t2.element_id
WHERE t2.trid = wpml_translations.trid
AND t2.language_code = 'en'
AND (p.post_status = 'publish'
OR p.post_type='attachment'
AND p.post_status = 'inherit') ) = 0))))
AND wp_posts.post_type IN ('post',
'page',
'attachment',
'custom_css',
'customize_changeset',
'oembed_cache',
'user_request',
'wp_block',
'wp_template',
'wp_template_part',
'wp_navigation',
'product',
'product_variation',
'shop_order_refund',
'faq',
'porto_builder'))
OR wp_posts.post_type NOT IN ('post',
'page',
'attachment',
'custom_css',
'customize_changeset',
'oembed_cache',
'user_request',
'wp_block',
'wp_template',
'wp_template_part',
'wp_navigation',
'product',
'product_variation',
'shop_order_refund',
'faq',
'porto_builder'))
GROUP BY wp_posts.ID
ORDER BY wp_posts.menu_order ASC,
wp_posts.post_title ASC
LIMIT 0,
12
Second slow query
Caller: _pad_term_counts()
Rows: 247746
Time (s): 1.0853
SELECT object_id, term_taxonomy_id
FROM wp_term_relationships
INNER JOIN wp_posts
ON object_id = ID
WHERE term_taxonomy_id IN (180862,181291,181328,181362,181541,182258,182825,180902,181307,181341,181373,181552,182698,181281,181318,181352,181384,181563,182752,180863,181292,181329,181363,181542,182259,182826,180927,181308,181342,181374,181553,182699,180853,181282,181319,181353,181385,181564,182753,180864,181293,181330,181364,181543,182260,182829,180928,181309,181343,181375,181554,182700,180854,181283,181320,181354,181386,181565,182754,180865,181294,181331,181365,181544,182261,183389,180929,181310,181344,181376,181555,182701,180855,181284,181321,181355,181387,181566,182778,180866,181295,181332,181366,181545,182645,183390,180930,181311,181345,181377,181556,182743,180856,181285,181322,181356,181388,181784,182779,180867,181301,181335,181367,181546,182646,183637,181243,181312,181346,181378,181557,182746,180857,181286,181323,181357,181389,181807,182780,180868,181302,181336,181368,181547,182647,184049,181275,181313,181347,181379,181558,182747,180858,181287,181324,181358,181390,182190,182781,180872,181303,181337,181369,181548,182648,208562,181276,181314,181348,181380,181559,182748,180859,181288,181325,181359,181391,182208,182782,180889,181304,181338,181370,181549,182649,208563,181277,181315,181349,181381,181560,182749,180860,181289,181326,181360,181539,182248,182783,180890,181305,181339,181371,181550,182696,181278,181316,181350,181382,181561,182750,180861,181290,181327,181361,181540,182249,182784,180896,181306,181340,181372,181551,182697,181279,181317,181351,181383,181562,182751)
AND post_type IN ('product')
AND post_status = 'publish'