We're using WooCommerce and WooCommerce subscriptions with approximately 23K users. While this question doesn't require that a user be using those plugins, I'm generally wondering how to better structure my WordPress SQL queries for performance.
I'm assuming that using sub-queries is not the most performant way to go about this.
What I'm looking to obtain is:
- All users registered since a specific date
- Who do not have a user_activation_key
- And who are not active subscribers
I've started reading several online tutorials but I'm having a difficult time wrapping my head around joins and the like. Can anyone point me in the best direction for these types of queries where we're looking to combine information for several tables, and are likely reliant on information from other tables?
SELECT COUNT(*) FROM rswp_users WHERE user_registered >= '2007-01-17 00:00:00' AND user_activation_key = '' AND ID NOT IN ( SELECT DISTINCT meta_value FROM rswp_postmeta WHERE post_id IN ( SELECT ID FROM rswp_posts WHERE post_type = 'shop_subscription' AND post_status = 'wc-active' ) AND meta_key = '_customer_user' ) ;