Slow query performance!!

  • Vimal Roy

    (@vimalroy08)


    3 months ago

    Hello,

    We have a multilingual WordPress website using WPML with over 52,000 posts across 8+ languages. We are encountering performance issues and site slowness, which seem to be related to the size of several database tables.

    Here is an overview of the large database tables:

    • wp_icl_translate: 1859.59 MB
    • wp_icl_translation_status: 1196.06 MB
    • wp_postmeta: 1095.48 MB
    • wp_posts: 570.16 MB
    • wp_icl_string_translations: 260.89 MB

    To address these issues, we have optimized the database using the WP Rocket plugin, clearing out unnecessary data like post revisions, transients, and auto drafts. Additionally, we have set WP_POST_REVISIONS to 3.

    I came across the suggestion to split large tables into multiple tables as a potential optimization. Could anyone share their experience with this approach?

    Moreover, I would appreciate any recommendations on further improving site performance, especially with regard to the database, queries, and overall request handling.

    Thank you in advance for your assistance.

    Best regards,
    Vimal Roy

    The page I need help with: [log in to see the link]

Viewing 4 replies - 1 through 4 (of 4 total)
  • Anton Vlasenko

    (@antonvlasenko)

    I came across the suggestion to split large tables into multiple tables as a potential optimization. Could anyone share their experience with this approach?

    I wouldn’t split tables, as I don’t think WordPress is quite optimized for that scenario. This approach is complex and can introduce significant risks and maintenance challenges. Instead, I would consider archiving some of the old data that’s no longer needed, such as old posts.

    Moreover, I would appreciate any recommendations on further improving site performance, especially with regard to the database, queries, and overall request handling.

    I risk sounding obvious, but I would ensure that commonly queried table rows have indexes and that these indexes are actually being used in database queries. A balanced approach is needed here, as adding too many indexes could slow down inserts and updates.

    I would also look into caching the most frequently used data.

    Thread Starter Vimal Roy

    (@vimalroy08)

    Hi @antonvlasenko ,

    Thank you for your response and for sharing your insights.

    Regarding the suggestion to remove old articles, I understand the rationale behind it, but as a news agency, retaining old articles is crucial for our SEO strategy. These articles are already indexed by search engines and have accumulated backlinks, which significantly contribute to our traffic. Removing them could result in broken URLs, a loss of traffic, and a negative impact on our SEO and rankings. However, we will consider archiving or removing articles that do not have any backlinks or do not contribute to our SEO strategy. Your advice on this is greatly appreciated.

    Regarding the indexing of database tables, I came across a plugin called Index WP MySQL For Speed that specifically aims to optimize MySQL indexes for WordPress. I wanted to confirm if this is the type of indexing you were referring to. If not, could you please provide more details on the indexing approach you mentioned?

    We are already utilizing WP Rocket for caching and preloading, and we have Redis Object Cache enabled on our server to cache database queries. Despite these optimizations, we are still experiencing performance issues, so any further recommendations you might have, particularly in terms of database optimization, would be greatly appreciated.

    Thank you once again for your time and assistance.

    Best regards,
    Vimal Roy

    • This reply was modified 3 months ago by Vimal Roy.
    Anton Vlasenko

    (@antonvlasenko)

    but as a news agency, retaining old articles is crucial for our SEO strategy. 

    I agree. Thanks for explaining.

    I wanted to confirm if this is the type of indexing you were referring to.

    Yes, I can confirm that this is the type of indexing I was referring to.

    While I haven’t personally used that specific plugin, I did review its source code out of curiosity. One concern I noted is that it removes some existing database indexes, which could potentially lead to issues, particularly with future WordPress updates and backward compatibility.

    Instead, a more tailored approach might be beneficial—one that involves analyzing slow SQL queries and selectively adding indexes to optimize performance. This way, you’re addressing the specific needs of your site rather than relying on a plugin that may add unnecessary indexes.

    If you decide to use the plugin, I strongly recommend(!) backing up your database first. It’s also wise to test the plugin on a copy of your website first to ensure it delivers the desired results without causing any issues. My concern, as mentioned, is the potential backward compatibility risks associated with removing existing WordPress indexes. This could lead to problems when updating your WordPress website to newer versions in the future.

    To clarify, I’m not offering my services, but you can try https://jobs.wordpress.net for finding qualified help.

    Thread Starter Vimal Roy

    (@vimalroy08)

    Hi @antonvlasenko ,

    Thank you for your response and for confirming the indexing type.

    I appreciate your caution about the plugin and the potential risks with removing existing indexes. We’ll make sure to back up the database and test any changes in a staging environment before applying them to the live site. Your suggestion to analyze slow queries and add indexes selectively makes sense, and we’ll consider that approach.

    Thanks for your help and the recommendation for finding qualified support. We’ll keep it in mind if needed.

    Best regards,
    Vimal Roy

Viewing 4 replies - 1 through 4 (of 4 total)
  • You must be logged in to reply to this topic.