Slow Query with more 100 products

Question

I'v created function to get all products of a category, and remove product with same meta_value (CIP). All works fine, but if category have more than 100 products my query is very very slow... for 2300 products times to view result are 35" or more...

What is wrong ?

function get_all_product_in_selected_category($atts){
// Get the category
$catsearched = $atts['category_id'];

// Get all product of categoryselected 
$product_args = array(
    'post_type' => 'product',
    'post_status' => 'publish',
    'posts_per_page' => -1,
    'fields' => 'ids',  // Only return product IDs
    'tax_query' => array(
        array(
            'taxonomy' => 'product_cat',
            'field' => 'id',
            'terms' => $catsearched,
            'operator' => 'IN',
        ))
);

$products = get_posts($product_args);

if(count($products) > 0)
{
    // Search CIP by product ID on category
    foreach ( $products as $id )
    {
        $cip = $product_obj['product_cip']=get_post_meta($id,'product_cip');
        //echo 'Cip = '.$cip[0].', ';
        $arrayCip[] = $cip[0];
    }
    //echo '<b>TotalNumberOfCIP = '.count($arrayCip).'</b>';

    // Remove same cip from category
    $result = array_unique($arrayCip);

    //print_r($result);
    //echo '<b>TotalNumberOfUniqueCIP = '.count($result).'</b>';

    // Get productID by CIP
    foreach ($result as $v)
    {
        $product = array
        (
            'post_type' => 'product',
            'posts_per_page'=> -1,
            'meta_query' => array
            (
                array(
                    'key' => 'product_cip',
                    'value' => $v,
                    'compare'=> '='
                )
            ),
            'fields' => 'ids'
        );
        $product_post = get_posts($product);
        //echo 'Uniques CIP = '.$product_post[0].', ';

        $a[] = $product_post[0];
        //echo count($product_post);
    }

    // If products exist, show products
    if(count($a) > 0)
    {
        $product = array
        (
            'post_type' => 'product',
            'posts_per_page'=> -1,
            'meta_key' => 'product_cip',
            'post__in' => $a
        );

        $allproduct = get_posts($product);
        foreach($allproduct as $allp) {
            $arrayall[] = $allp->ID;
        }

        $result = implode(",", $arrayall);

        return $result;
    }
    else
    {
        return 0;
    }
}}

If I only use this :

 // Get all product of categoryselected
$product_args = array(
    'post_type' => 'product',
    'post_status' => 'publish',
    'posts_per_page' => -1,
    'fields' => 'ids',  // Only return product IDs
    'tax_query' => array(
        array(
            'taxonomy' => 'product_cat',
            'field' => 'id',
            'terms' => $catsearched,
            'operator' => 'IN',
        ))
);

$products = get_posts($product_args);

$result = implode(",", $products);

return $result;

Time is very fast ! but I need to filter product by meta_value (CIP) too

Thanks,

EDIT:

I'v modified my function like this:

// Get the category
$catsearched = $atts['category_id'];

// Get all product of categoryselected
$product_args = array(
    'post_type' => 'product',
    'post_status' => 'publish',
    'posts_per_page' => -1,
    'fields' => 'ids',  // Only return product IDs
    'tax_query' => array(
        array(
            'taxonomy' => 'product_cat',
            'field' => 'id',
            'terms' => $catsearched,
            'operator' => 'IN',
        ))
);

$products = get_posts($product_args);

foreach ( $products as $id )
{
    $cip = $product_obj['product_cip']=get_post_meta($id,'product_cip');
    //echo 'Cip = '.$cip[0].', ';
    $arrayCip[] = $cip[0];
}
//echo '<b>TotalNumberOfCIP = '.count($arrayCip).'</b><br>';

$test = array_unique($arrayCip);

//echo '<b>TotalNumberOfUniqueCIP = '.count($test).'</b><br>';

foreach($test as $id)
{
    $array[] = get_post_id_by_meta_key_and_value('product_cip', $id);
}

echo '<b>TotalNumberOfUniqueID = '.count($array).'</b><br>';

$array = implode(",", $products);

return $array;

and added this function:

function get_post_id_by_meta_key_and_value($key, $value) {
global $wpdb;
$meta = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM ".$wpdb->postmeta." WHERE meta_key=%s AND meta_value=%s", $key, $value ) );
if (is_array($meta) && !empty($meta) && isset($meta[0])) {
    $meta = $meta[0];
}
if (is_object($meta)) {
    return $meta->post_id;
}
else {
    return false;
}

}

Problem are same, the foreach($test as $id) { $array[] = get_post_id_by_meta_key_and_value('product_cip', $id); } are very slow !

Answer 1

So you want only the "first" products having a unique product_cip? I think a direct MySQL Query may be quicker:

function get_all_product_in_selected_category($atts){
// Get the category
if(!(isset($atts['category_id']) || !is_numeric($atts['category_id'])){
   return 0;
}
$catsearched = $atts['category_id'];
global $wpdb;
$productssql = "SELECT min($wpdb->posts.ID), meta_value 
                FROM $wpdb->posts 
                INNER JOIN $wpdb->term_relationships 
                           ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id) 
                INNER JOIN $wpdb->term_taxonomy 
                           ON ($wpdb->term_taxonomy.term_taxonomy_id = $wpdb->term_relationships.term_taxonomy_id) 
                INNER JOIN $wpdb->postmeta 
                           ON ($wpdb->posts.ID = $wpdb->postmeta.post_id) 
                WHERE 
                           ($wpdb->postmeta.meta_key = 'product_cip')
                AND ($wpdb->posts.post_type = 'product') 
                AND ($wpdb->post_status = 'publish') 
                AND ($wpdb->term_taxonomy.taxonomy = 'product_cat')
                AND ($wpdb->term_taxonomy.term_id = %d)
                GROUP BY $wpdb->postmeta.meta_value";
$results = $wpdb->get_results($wpdb->prepare($productssql,$catsearched));
$a = array();
if($results){    
     foreach($results as $result){
         $a[] = $result->ID;
     }
     return implode(',',$a);
}
return 0;
}

Obviously didn't test this ;)