Custom post type – Huge DB – $query->set

I display some custom tables (backend) for my custom posts type.
I have created many upstream filters, let’s take the following example :

First custom post type : bank_accounts

Second custom post type : customers

(Dumb example)

For more simplicity I use external tables to wordpress that are related to it. (Plugin: Advanced Custom Fields: Custom Database Tables)

The data are saved in wordpress (wp_post and wp_postmeta) and wp_bank_accounts & wp_customers. (Approximately 3 million records)

I would like to display all “premium” bank_accounts that belong to women.

Currently I use the pre_get_posts action.

I use wpdb to retrieve all my post_id:

$query_filter = $wpdb->get_results(
“SELECT wp_bank_accounts.post_id
FROM wp_bank_accounts
INNER JOIN wp_customers ON wp_bank_accounts.customer_id = wp_customers.post_id
WHERE wp_customers.sex LIKE ‘female’
AND wp_bank_acccounts.premium = ‘true'”
);

Then, I add everything in an array:

$post_ids_list = array();

foreach($query_filter as $filter) {

$post_ids_list[] = $filter->post_id;

}

Then, I use $query->set like :

$query->set(‘post__in’, $post_ids_list);
$query->set(‘orderby’, ‘post__in’);

And everything works fine! But $query->set is horribly slow… (like 60′ sometimes more)

What I’ve already done :

Transient with redis (nice but all first queries are slow)
array_slice $post_ids_list based on get_query_var( ‘paged’), to take only the ids from the page. The problem is that the pagination is incorrect. 🙁

What are the alternatives to query->set for my case? I would like to keep the backend wordpress table display and its pagination …

Thx in advance ! 🙂

Read more here:: set” rel=nofollow>Custom post type – Huge DB – $query->set

Leave a Reply

Your email address will not be published. Required fields are marked *