I use following code to add custom query to woocommerce products when page loaded:

add_action( 'woocommerce_product_query', '_fn_test', 10, 1 );
    function _fn_test($q){
        $meta_query = $q->get( 'meta_query' );
        
        $meta_query[] = array(
            'key'       => '_stock_status',
            'value'     => 'outofstock',
            'compare'   => '!='
        );
        $meta_query[] = array(
            'key'       => 'custom_stock', // this is my custom meta option in product_meta table
            'value'     => '0',
            'compare'   => '!='
        );
        
        $q->set( 'meta_query', $meta_query );
    }

but it reduce the speed a lot in shop page, is there any problems in function? how can I speed it up?

CodePudding user response：

It's difficult to say without more information about your specific setup, but here are a few things you could try to improve the performance of the function:

1. Make sure that you have proper indexes set up on the _stock_status and custom_stock columns in the wp_postmeta table in your database. This can help the database query that retrieves the products to be faster.
2. Make sure that you are not running any other slow functions or queries on the woocommerce_product_query hook. If there are other functions hooked to this hook that are running slowly, it could be causing a performance issue.
3. If you are using a caching plugin, make sure that it is properly configured and that the product query is being cached correctly. This can help to improve the performance of the shop page.

You could also try using the pre_get_posts hook instead of the woocommerce_product_query hook. To do this, you will need to make the following changes:

Replace the add_action call with a call to add_action using the pre_get_posts hook:

    remove_action( 'woocommerce_product_query', '_fn_test', 10, 1 );
    add_action( 'pre_get_posts', '_fn_test', 10, 1 );

Modify the function to check whether the query being modified is a WooCommerce product query:

function _fn_test( $q ) {
    // Check if this is a WooCommerce product query
    if ( ! $q->is_main_query() || ! $q->is_post_type_archive( 'product' ) ) {
        return;
    }

    // Add the meta query to the product query
    $meta_query = $q->get( 'meta_query' );
    $meta_query[] = array(
        'key'       => '_stock_status',
        'value'     => 'outofstock',
        'compare'   => '!='
    );
    $meta_query[] = array(
        'key'       => 'custom_stock', // this is my custom meta option in product_meta table
        'value'     => '0',
        'compare'   => '!='
    );
    $q->set( 'meta_query', $meta_query );
}

There may be underlying issues from other plugins/themes hooking onto the woocommerce_product_query action, but even if that isn't the case it's not a good idea to adda meta query comparing a keys value unless it's indexed thouroughly, and if caching isn't functioning correctly it will need to do the long lookups for every single prodcut call.

If the pre_get_posts query still doesn't show an improvement you might be best to pre-populate either valid or invalid product ID's for a direct IN query instead of a potentially un-indexed meta query. To do this, you could first use a custom SQL query to retrieve the list of product IDs that you want to exclude, and then use the post__not_in parameter in the product query to exclude those IDs.

For example:

// Use a custom SQL query to retrieve the product IDs to exclude
global $wpdb;
$exclude_ids = $wpdb->get_col( "
    SELECT post_id
    FROM {$wpdb->prefix}postmeta
    WHERE meta_key = '_stock_status' AND meta_value = 'outofstock'
    OR meta_key = 'custom_stock' AND meta_value = '0'
" );

// Modify the product query to exclude the product IDs
add_action( 'pre_get_posts', '_fn_test', 10, 1 );
function _fn_test( $q ) {
    // Check if this is a WooCommerce product query
    if ( ! $q->is_main_query() || ! $q->is_post_type_archive( 'product' ) ) {
        return;
    }

    // Exclude the product IDs from the query
    $q->set( 'post__not_in', $exclude_ids );
}

CodePudding user response：

I usually prefer the wp_cache_* functions to cache the results of the query allowing you to avoid running the query on every page load and instead use a cached version of the results, which should be much faster.

Other things that you could do:-

1.Consider using a more efficient way to perform the query, such as using the WP_Query class or custom MySQL query.

2.Optimize your database(make sure that your indexes are properly set up to support query).

3.Consider using a plugin or service to optimize your site's performance, such as W3 Total Cache or Cloudflare.

Note:-There may be other factors too affecting the performance of your site, such as the number and size of images, the number of plugins you have installed, and the complexity of your theme. Identifying and addressing these issues can also help improve the performance of your site.