Tagged: enfold, enfold 6.0, slow, update. database
-
AuthorPosts
-
July 9, 2024 at 8:49 am #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:
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.
July 9, 2024 at 9:05 am #1461671Hey 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.phpwith
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.phpBest regards,
GünterJuly 9, 2024 at 9:29 am #1461672Thanks for your quick response. Our table contains 11 million records (1GB in size).
The ‘NOT LIKE’ part skips the index, see the attached screenshot:
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.
July 9, 2024 at 2:00 pm #1461694Hi,
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ünterJuly 9, 2024 at 3:53 pm #1461705We 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..).
July 10, 2024 at 8:46 am #1461757Temporary 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.
July 10, 2024 at 9:05 am #1461758Hi,
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ünterJuly 10, 2024 at 9:15 am #1461759It’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.
July 10, 2024 at 11:16 am #1461764Hi,
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ünterJuly 10, 2024 at 11:37 am #1461766Removing 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.
July 10, 2024 at 11:55 am #1461770Hi,
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ünterJuly 10, 2024 at 12:05 pm #1461771Thanks!
July 11, 2024 at 6:49 am #1461819Hi,
Related thread: https://kriesi.at/support/topic/theme-is-querying-all-meta-keys-from-the-postmeta-table-every-time-this-function/
Best regards,
Ismael -
AuthorPosts
- You must be logged in to reply to this topic.