0

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.

2 Answers 2

0

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

7
  • thanks for answer, how can i fix this ?
    – Asadiy4n
    Jul 16, 2020 at 18:41
  • pray, and/or get a bigger server. or, y'know, read what i wrote and ask if you have any more questions or if you don't understand any part of it.
    – mozboz
    Jul 16, 2020 at 18:43
  • To make less use of the meta_key, can i use count the checkbox value in meta_query ?
    – Asadiy4n
    Jul 16, 2020 at 18:51
  • Not sure how you mean use count ? probably not as to do a count the query would still have to do a join against the postmeta table for every separate option
    – mozboz
    Jul 16, 2020 at 19:04
  • Did you understand that you should probably replace 'LIKE' with '=' in each compare value?
    – mozboz
    Jul 16, 2020 at 19:05
0

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

2
  • You can give an example of this?, thanks for help
    – Asadiy4n
    Jul 17, 2020 at 9:41
  • Unfortunately no, ACF and other 3rd party plugins are offtopic, you will need to contact their support and check their documentation for how to configure those plugins. ACF questions are offtopic on this site, and not in this sites scope
    – Tom J Nowell
    Jul 17, 2020 at 11:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.