[FIXED] Optimizing custom Wordpress SQL query for fetching usermeta data

Issue

I have the following query and it works. But it takes insanely long to process due to it's buildup. I'm therefor in the need of assistance to get this query faster.

SQL Query

In the query PRODUCT_ID should be replaced by ' ' and the productID number.

SELECT
    b.order_id,
    customer_meta.meta_value AS customer_id,
    users.user_email,
    qty_table.meta_value AS qty,
    user_meta1.meta_value AS firstname,
    user_meta2.meta_value AS lastname,
    user_meta3.meta_value AS company,
    user_meta4.meta_value AS address,
    user_meta5.meta_value AS city,
    user_meta6.meta_value AS postcode,
    user_meta7.meta_value AS state,
    user_meta8.meta_value AS user_phone
FROM
    wp_woocommerce_order_itemmeta a,
    wp_woocommerce_order_items b,
    wp_postmeta customer_meta,
    wp_users users,
    wp_woocommerce_order_itemmeta qty_table,
    wp_usermeta user_meta1,
    wp_usermeta user_meta2,
    wp_usermeta user_meta3,
    wp_usermeta user_meta4,
    wp_usermeta user_meta5,
    wp_usermeta user_meta6,
    wp_usermeta user_meta7,
    wp_usermeta user_meta8
WHERE
    a.meta_key = '_product_id'
    AND a.meta_value = PRODUCT_ID
    AND a.order_item_id = b.order_item_id
    AND customer_meta.meta_key = '_customer_user'
    AND customer_meta.post_id = b.order_id
    AND user_meta1.meta_key = 'first_name'
    AND user_meta1.user_id = users.id
    AND user_meta2.meta_key = 'last_name'
    AND user_meta2.user_id = users.id
    AND user_meta3.meta_key = 'billing_company'
    AND user_meta3.user_id = users.id
    AND user_meta4.meta_key = 'billing_address_1'
    AND user_meta4.user_id = users.id
    AND user_meta5.meta_key = 'billing_city'
    AND user_meta5.user_id = users.id
    AND user_meta6.meta_key = 'billing_postcode'
    AND user_meta6.user_id = users.id
    AND user_meta7.meta_key = 'billing_state'
    AND user_meta7.user_id = users.id
    AND user_meta8.meta_key = 'billing_phone'
    AND user_meta8.user_id = users.id
    AND users.ID = customer_meta.meta_value
    AND qty_table.meta_key = '_qty'
    AND qty_table.order_item_id = b.order_item_id
ORDER BY user_meta3.meta_value ASC

I need all the information, since I want to list all the users with their Firstname, lastname, company, address, postcode, etc for a given product which has been bought. So the query in itself works, but is a killer in process time.

I could use max( CASE WHEN ... ... THEN ...END ) as a_name but I only know how to do it successfully if I use a left join.

Any tips on how to get this query to run better?

Solution

WP, are you listening? WooCommerce, are you listening? I am getting tired of optimizing your database app.

First and foremost, EAV is a terrible schema design. But I won't rant about that. I'll just point out the index(es) that are probably missing or mal-formed:

wp_usermeta:  PRIMARY KEY(user_id, meta_key)

Without any (191) tacked on.

Similarly for wp_woocommerce_order_itemmeta.

I may have more abuse to dish out; please provide SHOW CREATE TABLE for the tables being used.

Answered By - Rick James