The query is choking our database server

Anonymous

#253617

+———————————————–+———-+——————————+———-+
| name | status | update | version |
+———————————————–+———-+——————————+———-+
| acf-to-rest-api | active | available | 3.2.0 |
| aryo-activity-log | active | none | 2.5.2 |
| advanced-custom-fields | active | none | 5.8.12 |
| agile-store-locator-google-maps-for-wordpress | active | version higher than expected | 4.5.5 |
| booster-plus-for-woocommerce | active | available | 5.0.0 |
| wp-defender | active | none | 2.3 |
| duracelltomi-google-tag-manager | active | none | 1.11.4 |
| gravityforms | active | available | 2.4.18 |
| groups | active | none | 2.10.0 |
| redirection | active | none | 4.8 |
| regenerate-thumbnails | active | none | 3.1.3 |
| search-filter-pro | active | none | 2.5.1 |
| simple-sitemap | active | none | 3.5 |
| wp-smush-pro | active | none | 3.6.3 |
| kahanit-tablerateshipping | active | none | 1.2.1 |
| user-role-editor | active | none | 4.55.1 |
| velvet-blues-update-urls | active | none | 3.2.9 |
| woocommerce | active | available | 4.2.2 |
| woocommerce-catalog-enquiry-pro | active | none | 1.1.5 |
| woocommerce-dynamic-pricing | active | none | 3.1.13 |
| woocommerce-gateway-payment-express | active | none | 3.0 |
| woocommerce-sequential-order-numbers | active | none | 1.9.5 |
| woocommerce-shipping-multiple-addresses | active | none | 3.6.6 |
| woocommerce-tm-extra-product-options | active | available | 5.0.12.2 |
| woosidebars | active | none | 1.4.5 |
| wpmudev-updates | active | none | 4.9.4 |
| wp-rocket | active | available | 3.6.1 |
| wordpress-seo | active | available | 14.4.1 |
+———————————————–+———-+——————————+———-+

Among our plugins installed in our site, search-filter-pro consumes a lot of resources in our server.
Our DevOps is tagging this query that was generated by your plugin.
| 26579 | safesusr | 10.136.184.160:49444 | safes_db | Query | 578 | Sending data | SELECT SQL_CALC_FOUND_ROWS 4hW1Q_posts.ID FROM 4hW1Q_posts INNER JOIN 4hW1Q_postmeta ON ( 4hW1Q_posts.ID = 4hW1Q_postmeta.post_id ) INNER JOIN 4hW1Q_postmeta AS mt1 ON ( 4hW1Q_posts.ID = mt1.post_id ) LEFT JOIN 4hW1Q_wc_product_meta_lookup wc_product_meta_lookup ON 4hW1Q_posts.ID = wc_product_meta_lookup.product_id WHERE 1=1 AND 4hW1Q_posts.ID IN (30079,30080,30081,30084,30085,30086,30087,30088,30128,30129,30130,30145,30161,30164,30165,30984,31286,31287,31299,31303,31413,31862,31865,31866,32634,32635,32642,32643,32674,32684,32689,32690,32704,32705,32706,32707,32720,33465,33479,33480,32664,32665,32672,32673,32688,32700,30163,30104,30105,30106,30107,30108,30109,30110,30111,30112,30113,30114,30115,30116,30118,30119,30120,30121,30122,30123,30125,30126,30127,30133,30139,30140,30141,30142,30143,30144,30986,30146,30147,30148,30149,30150,30987,30988,30989,30990,30151,30152,30153,30991,30154,30155,30156,30157,30158,30159,30160,30162,30167,30168,30101,30102,30103,30131,30132,31300,31301,31302,31417,32652,30096,30097,30082,30083,30089,30090,30091,30092,30094,30095,32656,32657,30099,30100) AND (
4hW1Q_posts.ID NOT IN (
SELECT object_id
FROM 4hW1Q_term_relationships
WHERE term_taxonomy_id IN (28)
)
AND
4hW1Q_posts.ID NOT IN (
SELECT object_id
FROM 4hW1Q_term_relationships
WHERE term_taxonomy_id IN (131)
)

Is there any way that this could be minimized? Or avoid using NOT IN in the query?

Trevor

#253677

The NOT IN parts will be what you have set in the form, possibly as excludes. Our queries are preprocessed so that we send the request in a way which is fastest for the server to be able to respond to. It does this by restricting the request to matching POST IDs (in the example about 130 posts).

When comparing WordPress sites that have:

Simple text search and/or ordering

to ones where the users will be doing complex filtering, there will be a huge difference in CPU and Hard Drive usage.

The server configuration will be key here. If it is not configured for this type of usage, then it will struggle. I have many hosting packages configured for basic WordPress sites that are very fast and have low resource usage. It allows the hosting company to oversell these resources. I have seen some hosts that offer Managed WordPress servers with and without WooCommerce, where there is no difference in configuration.

In my view, all hosting should be configured assuming heavy database use, and be configured in such a way as to keep CPU and Hard Drive usage optimised. Sadly, this is not the case.

Hosts do oversell resources, especially on Shared Hosting and on VPSs. And they frequently do not optimise the server setup (software). Whenever I see a standard Customer Control Panel (like WHM/cPanel, or Plesk), I know this will be so. It is so expensive to maintain high performing server software setups, that very few hosts do it. Even of those that do, such as Cloudways, WP Engine, Flywheel, Siteground, are cost centric compromises, but at their performance isn’t bad. Only one host truly ticks all the boxes though, and that is Kinsta.

Users (ones I have spoken to on this performance topic – it comes up from time to time across the year) who try them are often stunned at the performance difference.

In summary, the queries we generate are as optimized as they can be. Given a good hosting server, they should not be placing high load on server resources.

Trevor

#253686

As an afterthought, in our plugin settings, you will an option to use transients. You might want to try that, unless you are using a translation/language plugin, in which case, don’t do that.

[Author]

Posts