Viewing 13 posts - 1 through 13 (of 13 total)
  • Author
    Posts
  • #1461670

    We have several websites running Enfold 6.0. On some large websites (with a large postmeta table), the website (frontend and backend) is really slow due to the database query, introduced in the latest version of Enfold:

    SELECT DISTINCT meta_key FROM wp_postmeta WHERE meta_key NOT LIKE “\_%” ORDER BY meta_key

    See:
    Slow query

    Some of our websites are taking up to 10-20 seconds to load with Enfold 6.0. Only way to fix this is to revert to an older Enfold version. This is a real issue for some of our websites.

    #1461671

    Hey roelvanleuken,

    Thank you for pointing at this – I already answered your contact form request yesterday:

    First I checked the database structure of WP post meta table.

    meta_key field is an index in my install (6.5.3). This means the query you show is a simple query against an index and this means that is a very fast query – even in a database that contains million of entries in a table.

    Please check with your hoster (datatbase hoster) why this query is so slow in your case. Could be your database is broken somehow or allocated memory is too small.

    Nevertheless I improved the query logic to remove it from frontend for next release 6.0.1.

    If you want to test the fixes please replace the 2 following files:

    ..\config-acf\class-avia-acf.php
    ..\config-templatebuilder\avia-template-builder\php\class-dynamic-content.php

    with

    https://github.com/KriesiMedia/enfold-library/blob/master/temp_fixes/enfold_6_0/config-acf/class-avia-acf.php
    https://github.com/KriesiMedia/enfold-library/blob/master/temp_fixes/enfold_6_0/config-templatebuilder/avia-template-builder/php/class-dynamic-content.php

    Best regards,
    Günter

    #1461672

    Thanks for your quick response. Our table contains 11 million records (1GB in size).

    The ‘NOT LIKE’ part skips the index, see the attached screenshot:
    DB index

    You should optimize this somehow (using WP transients? using a more optimized database query?).

    Disabling it on the frontend still gives us issues on the backend off course.

    #1461694

    Hi,

    I checked the query on my localhost:

    Can you check that meta_key is an index on your install and maybe also try to reindex it. Sounds a bit strange that an SQL Server does not use an index.

    If you do not need the custom field feature added with 6.0 disable it in theme option Enfold->Template Builder-> Custom Layout And Dynamic Content.

    Our query is based on the WP query in ..\wp-admin\includes\template.php function meta_form() which fills the custom fields when editing a post. This query is limited to 30 entries by default.

    There are also several filters for our query (see ..\config-templatebuilder\avia-template-builder\php\class-dynamic-content.php function get_default_custom_fields() ) – even to shortcut it or to limit:

    
    $limit = apply_filters( 'avf_query_default_custom_fields_limit', 0 );
    

    But we offer the custom fields in dropdowns to be selected by users – not showing all might result in being not user friendly. There is much depending on your application.

    If you know exactly which custom fields are needed you can return this set hardcoded with filter:

    
    			/**
    			 * Allow to short circuit the query against post meta table.
    			 * Return an array filled with the keys.
    			 * Query for _ only might get much longer than other queries
    			 *
    			 * @since 6.0
    			 * @param array|null $keys
    			 * @param bool $filter
    			 * @param int $limit
    			 * @return array|null
    			 */
    			$wp_custom_fields = apply_filters( 'avf_before_query_wp_default_custom_fields', null, $filter, $limit );
    

    Best regards,
    Günter

    #1461705

    We will this investigate at our end (why indexes are not used properly), we’re running several servers where this issue all occur.

    You have to keep in mind that not all hostingservers are equally configured. We are probably not the only one running into this.

    I understand the need of retrieving all custom fields, but I’m sure there are better ways (performance wise) to achieve this (using WP transients, retrieving them with a WP Cron and so on..).

    #1461757

    Temporary fix for anyone experiencing the same issue (until the authors of Enfold fix this issue permanently):

    Add this to your child theme’s functions.php:

    // Enfold 6.0 slow query fix
    add_filter('avf_before_query_wp_default_custom_fields', function($custom_fields) { return []; });

    The dynamic custom fields don’t work anymore with this line, but at least your website is working again.

    #1461758

    Hi,

    Thank you for posting the temporary solution.

    As I do not have a test environment with such many posts: could you please check if adding a limit (as WP does) would help?

    e.g. like

    
    add_filter('avf_query_default_custom_fields_limit', function($limit) { return 300; });
    

    Thank you for your help in advance.

    Best regards,
    Günter

    #1461759

    It’s not doing anything unfortunately:

    The database index is not being used when a wildcard is used in the query. As far as I know, that is normal MySQL behavior. I’m not sure why the index is used in your own hosting environment..

    • This reply was modified 3 months, 4 weeks ago by roelvanleuken.
    #1461764

    Hi,

    Thanks for checking and your time.

    Using the index seems to be server depending:

    https://planetscale.com/learn/courses/mysql-for-developers/indexes/indexing-for-wildcard-searches

    I removed the order by clause and the query time dropped dramatically on my install.

    Could you try to remove the following line from ..\enfold\config-templatebuilder\avia-template-builder\php\class-dynamic-content.php

    around line 1410:

    
    $sql .= 'ORDER BY meta_key ';
    

    and can you please check with and without limit ?

    Thank you very much in advance.

    Best regards,
    Günter

    #1461766

    Removing the ORDER BY and adding a LIMIT does the trick indeed:

    Without ‘LIMIT’ the query is still slow.

    • This reply was modified 3 months, 4 weeks ago by roelvanleuken.
    #1461770

    Hi,

    Thank you very much for checking and your help.

    I will add a fix to 6.0.1 and we will release it very soon.

    Have a great day – and thank you for using Enfold.

    Best regards,
    Günter

    #1461771

    Thanks!

    #1461819
Viewing 13 posts - 1 through 13 (of 13 total)
  • You must be logged in to reply to this topic.