0

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'
)
;
  • Since this is not directly related to WordPress and still very specific, you might have more luck on Database Administrators or CodeReview – kero Jan 23 '18 at 14:46
  • I had considered doing that, but the WordPress database structure is highly relevant in this question, and it's a known pain point for developers. – Philip Downer Jan 23 '18 at 15:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.