wordpress custom query is too slow

Question

I am trying to create a custom filter with the custom table in woocommerce. there is approx 24 000 products that makes the custom filter very slow. How to make it faster?

here is my code:

function custom_wpquery( $query ){
    if(isset($_GET['min-price'])){
    // the main query
    global $wp_the_query;
 global $wpdb;
    if ( 'product' === $query->get( 'post_type' ) ) {

        $rr = g($_GET['min-price'],$_GET['max-price']);

        foreach($rr as $rrr){

           $fabricc = $wpdb->get_results("SELECT * FROM CWF_posts WHERE post_title = '".$rrr."' AND post_type = 'product'");

           foreach($fabricc as $fabriccc){
        $cID[] = $fabriccc->ID;    
           }

        }

        //print_r();exit;
      //  foreach($cID as $cIDD){
          //  $cat= wp_get_post_terms( $dd->ID, 'product_cat' );
       // }
          //$dd = get_page_by_title( $rrr, OBJECT, 'product' );

         //   $include[]=$dd->ID;
           // 
           // $c[] = $cat[0]->slug;
            //$c2[] = $cat[1]->slug;
       $query->set('post__in', $cID); 
    }
    }
}
add_filter( 'pre_get_posts', 'custom_wpquery' );

Thanks

Please don't create queries by concatenation of strings. You open yourself up to SQL Injection attacks. Why not just return the `id`s, instead of the whole row? Do you have an index on `post-title` and one on `post_type`? Those changes should stop you scanning the whole table and retrieving more data than you need. — Dragonthoughts, Jul 12 '18 at 14:29

score 1 · Answer 1 · answered Jul 12 '18 at 14:44

You can create a multiple-index on fields that you are querying... Since your one field is constant I advise that convert your SQL to

SELECT * FROM CWF_posts WHERE post_type = 'product' AND post_title = '".$rrr."'

and create an index on post_type and post_title with this command on MySQL

CREATE INDEX type_title_index on CWF_posts (post_type, post_title)

also you can check

Understanding multiple column indexes in MySQL query

https://dev.mysql.com/doc/refman/8.0/en/create-index.html

wordpress custom query is too slow

1 Answers1