Quote from Alex Dovlatov on January 21, 2026, 13:35

Hello

Here some actions I can suggest you:

• https://share.pluginus.net/image/i20260121133100.png - in tab Advanved ->Options -> "Optimize price filter" -> Yes
  Helps to more quickly find the minimum and maximum values for the filter by price on the site front and minimize server loading.
• Add database indexes:
  ALTER TABLE vnt_postmeta ADD INDEX woof_meta_key_value (meta_key(191), meta_value(20));
  ALTER TABLE vnt_posts ADD INDEX woof_type_status (post_type(20), post_status(20));

  (Note: Your table prefix is vnt_ based on the error log)

  Why this happens: The query joins ALL postmeta rows (millions) before filtering by meta_key = '_price'. With proper indexes, MySQL will filter FIRST, then join only the relevant rows.

• Optimize the query (if indexes alone don't help):

  add_filter('woof_get_filtered_price_query', function($sql) {
      global $wpdb;
      
      // Force index usage for better performance
      $sql = str_replace(
          "LEFT JOIN {$wpdb->postmeta} as price_meta",
          "LEFT JOIN {$wpdb->postmeta} as price_meta FORCE INDEX (woof_meta_key_value)",
          $sql
      );
      
      return $sql;
  }, 10);

   

Try it please ...

Quote from pentium on January 21, 2026, 14:33

Thank you. We tried that and flushed all caches afterwards however, I was unable to see any improvement when accessing the https://www.pjfallon.ie/shop which throws a 504 error still. The strace shows the stall happening after the process reaches this point:

1768997905.983370 (+     0.000480) sendto(7, "\247\4\0\0\3SELECT SQL_CALC_FOUND_ROWS  vnt_posts.ID\n\t\t\t\t\t FROM vnt_posts  INNER JOIN vnt_postmeta ON ( vnt_posts.ID = vnt_postmeta.post_id )  INNER JOIN vnt_postmeta AS mt1 ON ( vnt_posts.ID = mt1.post_id ) LEFT JOIN vnt_wc_product_meta_lookup wc_product_meta_lookup ON vnt_posts.ID = wc_product_meta_lookup.product_id \n\t\t\t\t\t WHERE 1=1  AND vnt_posts.ID NOT IN (78667) AND ( \n  vnt_posts.ID NOT IN (\n\t\t\t\tSELECT object_id\n\t\t\t\tFROM vnt_term_relationships\n\t\t\t\tWHERE term_taxonomy_id IN (12)\n\t\t\t) \n  AND \n  vnt_posts.ID NOT IN (\n\t\t\t\tSELECT object_id\n\t\t\t\tFROM vnt_term_relationships\n\t\t\t\tWHERE term_taxonomy_id IN (20,928)\n\t\t\t)\n) AND ( \n  ( vnt_postmeta.meta_key = '_price' AND CAST(vnt_postmeta.meta_value AS SIGNED) != '' ) \n  OR \n  ( mt1.meta_key = '_price' AND CAST(mt1.meta_value AS SIGNED) != '0' )\n) AND ((vnt_posts.post_type = 'product' AND (vnt_posts.post_status = 'publish' OR vnt_posts.post_status = 'ywgc-disabled' OR vnt_posts.post_status = 'ywgc-dismissed' OR vnt_posts.post_status = 'ywgc-code-not-valid' OR vnt_posts.post_status = 'acf-disabled')))\n\t\t\t\t\t GROUP BY vnt_posts.ID\n\t\t\t\t\t ORDER BY  wc_product_meta_lookup.total_sales DESC, wc_product_meta_lookup.product_id DESC \n\t\t\t\t\t LIMIT 0, 64", 1195, MSG_DONTWAIT, NULL, 0) = 1195
1768997905.983445 (+     0.000073) poll([{fd=7, events=POLLIN|POLLERR|POLLHUP}], 1, 86400000

Have you any other suggestions?

Thanks

Quote from Alex Dovlatov on January 22, 2026, 12:34

Hello

Thank you for the follow-up. I can see the issue is now showing a different query - this is actually the main WooCommerce shop page query, not just WOOF's price filter query. This indicates the problem is deeper than just the price filter optimization.

Looking at the new strace log, the problematic query is:

• Scanning millions of rows in postmeta tables
• Using LEFT JOIN with wc_product_meta_lookup
• Sorting by total_sales which is very resource-intensive with large datasets
• Multiple INNER JOINs for price meta filters
• Using NOT IN subqueries which are inefficient

Additional steps to resolve this:

1. Add indexes for WooCommerce lookup table:

ALTER TABLE vnt_wc_product_meta_lookup ADD INDEX idx_sales (total_sales, product_id);

2. Check your database size: Can you run this query and tell me the results?

SELECT COUNT(*) FROM vnt_postmeta;
SELECT COUNT(*) FROM vnt_posts WHERE post_type = 'product';

The fact that the first query examined 82+ million rows suggests your database might need cleanup.

3. Temporary solution - disable sorting by popularity: In WooCommerce > Settings > Products > Display, change the default product sorting from "Popularity" to "Default sorting (custom ordering + name)". This will avoid the expensive JOIN with the lookup table.

4. Consider using a proper caching solution: For sites with large product catalogs, you should use:

• Object caching (Redis/Memcached)
• Full page caching
• Query result transients

5. Database cleanup: Your postmeta table seems abnormally large. You might have orphaned meta entries or excessive revisions. Consider:

• Removing orphaned postmeta: https://wordpress.org/plugins/wp-sweep/
• Limiting post revisions
• Cleaning up old transients

Important note: This is no longer just a WOOF issue - this is a WooCommerce performance issue with large databases. The hosting company is correct that the query is slow, but the root cause is the combination of:

• Very large database
• Expensive sorting by popularity
• Complex meta queries
• Lack of proper caching

 

P.S. 

Questions for you:

• What hosting are you on? (Shared/VPS/Dedicated)
• How much RAM is allocated to your database server?
• Do you have Redis or Memcached available?

Reality check: With a database this large, you need:

• Minimum: VPS with at least 8GB RAM + Redis/Memcached object caching
• Recommended: Dedicated server or managed WordPress hosting (Kinsta, WP Engine, etc.)
• MySQL optimization: Properly configured MySQL with increased buffer pool size