WP_Query with many meta_query arguments taking a long time to load

Question

I have a search form withwp_queryargument There are many checkbox in this form

The problem start here, it takes a long time to load when you select all the options, this query takes almost a minute to complete. Has anyone had this issue before?

I used these codes in HTML

<form action="<?php echo site_url() ?>/wp-admin/admin-ajax.php" method="POST" id="filter">
<label> <input type="checkbox" name="real_time_antivirus" /> Real-time Antivirus </label></br>
.
.
.
<label> <input type="checkbox" name="adware_prevention" /> adware_prevention </label></br>
<button>Apply filter</button>
<input type="hidden" name="action" value="myfilter">

in functions.php

add_action('wp_ajax_myfilter', 'misha_filter_function'); // wp_ajax_{ACTION HERE} 
add_action('wp_ajax_nopriv_myfilter', 'misha_filter_function');
 
function misha_filter_function(){
    $args = array(
        'posts_per_page' =>-1,
    );
    $args['meta_query'] = array( 'relation'=>'AND' );
    // Antivirus_featured_scaning
    if( isset( $_POST['real_time_antivirus'] ) && $_POST['real_time_antivirus'] == 'on' )
        $args['meta_query'][] = array(
            'key' => 'antivirus_antivirus_featured_scaning',
            'value' => 'real_time_antivirus',
            'compare' => 'LIKE'
        );
    if( isset( $_POST['manual_virus_scanning'] ) && $_POST['manual_virus_scanning'] == 'on' )
        $args['meta_query'][] = array(
            'key' => 'antivirus_antivirus_featured_scaning',
            'value' => 'manual_virus_scanning',
            'compare' => 'LIKE'
        );
    if( isset( $_POST['usb_virus_scan'] ) && $_POST['usb_virus_scan'] == 'on' )
        $args['meta_query'][] = array(
            'key' => 'antivirus_antivirus_featured_scaning',
            'value' => 'usb_virus_scan',
            'compare' => 'LIKE'
        );
    if( isset( $_POST['registry_startup_scan'] ) && $_POST['registry_startup_scan'] == 'on' )
        $args['meta_query'][] = array(
            'key' => 'antivirus_antivirus_featured_scaning',
            'value' => 'registry_startup_scan',
            'compare' => 'LIKE'
        );
    if( isset( $_POST['auto_virus_scanning'] ) && $_POST['auto_virus_scanning'] == 'on' )
        $args['meta_query'][] = array(
            'key' => 'antivirus_antivirus_featured_scaning',
            'value' => 'auto_virus_scanning',
            'compare' => 'LIKE'
        );
    if( isset( $_POST['scheduled_scan'] ) && $_POST['scheduled_scan'] == 'on' )
        $args['meta_query'][] = array(
            'key' => 'antivirus_antivirus_featured_scaning',
            'value' => 'scheduled_scan',
            'compare' => 'LIKE'
        );
    // antivirus_featured_threat
    if( isset( $_POST['anti_spyware'] ) && $_POST['anti_spyware'] == 'on' )
        $args['meta_query'][] = array(
            'key' => 'antivirus_antivirus_featured_threat',
            'value' => 'anti_spyware',
            'compare' => 'LIKE'
        );
    if( isset( $_POST['anti_worm'] ) && $_POST['anti_worm'] == 'on' )
        $args['meta_query'][] = array(
            'key' => 'antivirus_antivirus_featured_threat',
            'value' => 'anti_worm',
            'compare' => 'LIKE'
        );
    if( isset( $_POST['anti_trojan'] ) && $_POST['anti_trojan'] == 'on' )
        $args['meta_query'][] = array(
            'key' => 'antivirus_antivirus_featured_threat',
            'value' => 'anti_trojan',
            'compare' => 'LIKE'
        );
    if( isset( $_POST['anti_rootkit'] ) && $_POST['anti_rootkit'] == 'on' )
        $args['meta_query'][] = array(
            'key' => 'antivirus_antivirus_featured_threat',
            'value' => 'anti_rootkit',
            'compare' => 'LIKE'
        );
    if( isset( $_POST['anti_phishing'] ) && $_POST['anti_phishing'] == 'on' )
        $args['meta_query'][] = array(
            'key' => 'antivirus_antivirus_featured_threat',
            'value' => 'anti_phishing',
            'compare' => 'LIKE'
        );
    if( isset( $_POST['anti_spam'] ) && $_POST['anti_spam'] == 'on' )
        $args['meta_query'][] = array(
            'key' => 'antivirus_antivirus_featured_threat',
            'value' => 'anti_spam',
            'compare' => 'LIKE'
        );
    if( isset( $_POST['email_protection'] ) && $_POST['email_protection'] == 'on' )
        $args['meta_query'][] = array(
            'key' => 'antivirus_antivirus_featured_threat',
            'value' => 'email_protection',
            'compare' => 'LIKE'
        );
    if( isset( $_POST['chat_im_protection'] ) && $_POST['chat_im_protection'] == 'on' )
        $args['meta_query'][] = array(
            'key' => 'antivirus_antivirus_featured_threat',
            'value' => 'chat_im_protection',
            'compare' => 'LIKE'
        );
    if( isset( $_POST['adware_prevention'] ) && $_POST['adware_prevention'] == 'on' )
        $args['meta_query'][] = array(
            'key' => 'antivirus_antivirus_featured_threat',
            'value' => 'adware_prevention',
            'compare' => 'LIKE'
        );
 

    $query = new WP_Query( $args );

Thanks for any help.

Answer 1

Yeah I can imagine that query is going to be a doozy.

The main reason for that is that every extra condition against a post meta value might be causing an extra SQL JOIN between the wp_posts table and the wp_posts table, and JOINS can get exponentially expensive. I'm surprised it even comes back in a minute if you select all those options. And this problem will get worse as the size of your wp_postmeta table grows.

Saying that, it's not clear why you're using LIKE here instead of = for the compare value, unless you really do need to match strings that contain the value parameter? That might make a big difference in the speed as LIKE is more expensive than = in SQL.

Other than that, because of the way this query works internally in WP_Query you might have a hard time making it go any faster. You might be at the point where you need a different data structure, or to do something more custom away from using WP_Query and the key-value structure of the wp_postmeta table. Or you could look at limiting the number of these options that users can pick.

HTH

Answer 2

The query is very slow because it's searching for posts by their post meta values. Using meta_query is fundamentally slow, and very heavy and expensive on the database server. It gets even slower as the number of posts in the database rises. This is why these queries run quickly when the site is small but get slower over time.

If you want to store data to filter or search for posts, use a custom taxonomy instead.

• Post meta is optimised for finding values when you already know the post ID.
• Taxonomies are optimised for finding posts when you already know the values

If you're using ACF or Field Manager, these plugins can be told to use taxonomies for those fields