优化用于获取USERMETA数据的自定义WordPress SQL查询

Question

我具有以下查询，并且有效。但是，由于其积累，需要疯狂的处理时间。因此，我需要协助以更快地获取此查询。

SQL查询

在查询product_id中应替换为''和pafuderid编号。

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

我需要所有信息，因为我想列出所有用户的名称，姓氏，公司，地址，邮政编码等，以购买已购买的给定产品。因此，查询本身有效，但在过程时间内是杀手。

我可以使用max（何时... ...然后...结束）作为a_name，但我只知道如果使用左JON加入，我只知道该如何成功。

有关如何使此查询更好地运行的任何提示？

Answer 1

wp，您在听吗？WooCommerce，您在听吗？我厌倦了优化您的数据库应用程序。

首先，EAV是一种可怕的模式设计。但是我不会对此大声疾呼。我只是指出可能缺少或成型的索引：

wp_usermeta:  PRIMARY KEY(user_id, meta_key)

没有任何 (191)的。

类似于wp_woocommerce_order_itemmeta。

我可能会有更多的虐待来滥用；请为所使用的表提供SHOW CREATE TABLE。

Answer 2

几年后，与@rickjames交谈后，这是您问题的另一个答案。

1. 您已经从Comma-Join到左连接的查询进行了重构。那很好。您的逗号 - 加入语法省略了结果集中缺少元数据的用户。

2. 如果您还没有这样做，请确保升级MySQL Server，以便它的版本5.7或更高版本。如果您使用Mariadb，请升级它，以便版本10.3或更高版本对于性能很重要。

3. 安装并使用WordPress插件索引WP mysql速度。我和里克（Rick）一起解决了您的问题。它可以使WP_USERMETA（以及其他一些WordPress表）使这些查询更有效。

或，如果您不想使用插件来重新索引表，则可以通过phpmyadmin或其他一些SQL客户端自己执行。（如果您陷入了MySQL的旧版本，这些语句将无法使用。该插件包含用于旧版本的解决方法，该插件有点复杂，无法在stackoverflow答案中解释。）

ALTER TABLE wp_usermeta ENGINE=InnoDB, ROW_FORMAT=Dynamic;
ALTER TABLE wp_usermeta ADD UNIQUE KEY umeta_id (umeta_id);
ALTER TABLE wp_usermeta DROP PRIMARY KEY;
ALTER TABLE wp_usermeta ADD PRIMARY KEY (user_id, meta_key, umeta_id);
ALTER TABLE wp_usermeta DROP KEY user_id;
ALTER TABLE wp_usermeta DROP KEY meta_key;
ALTER TABLE wp_usermeta ADD KEY meta_key (meta_key, user_id);

这组索引允许您的查询更有效地检索那些左加入的meta_value属性。为什么？简而言之，为此目的，表需要(user_id, meta_key)上的索引。在InnoDB中，表中的数据实际上存储在主密钥索引中（称为群集索引）。因此，mySQL可以很快检索您需要的meta_values。

Answer 3

添加里克·詹姆斯（Rick James）描述的索引将使原始查询更快。

，但我设法将其更改为使用左JOIN，这是迄今为止最快的，如果其他人需要它，请在此处显示：

SELECT
  b.order_id, 
  customer_meta.meta_value as customer_id,
  u.display_name as customer_name,
  u.user_email, 
  qty_table.meta_value as qty,
  um_last_name.meta_value as last_name,
  um_first_name.meta_value as first_name,
  um_address1.meta_value as address1,
  um_address2.meta_value as address2,
  um_address_city.meta_value as city,
  um_address_state.meta_value as state,
  um_company.meta_value as company,
  um_email.meta_value as billing_email
FROM wp_woocommerce_order_itemmeta a
  LEFT JOIN wp_woocommerce_order_items b on a.order_item_id = b.order_item_id
  LEFT JOIN wp_postmeta customer_meta on customer_meta.post_id = b.order_id AND customer_meta.meta_key = '_customer_user'
  LEFT JOIN wp_users u on u.ID = customer_meta.meta_value
  LEFT JOIN wp_woocommerce_order_itemmeta qty_table on qty_table.order_item_id = b.order_item_id and qty_table.meta_key = '_qty'
  LEFT JOIN wp_usermeta um_last_name on u.ID = um_last_name.user_id and um_last_name.meta_key = 'last_name'
  LEFT JOIN wp_usermeta um_first_name on u.ID = um_first_name.user_id and um_first_name.meta_key = 'first_name'
  LEFT JOIN wp_usermeta um_address1 on u.ID = um_address1.user_id and um_address1.meta_key = 'billing_address_1'
  LEFT JOIN wp_usermeta um_address2 on u.ID = um_address2.user_id and um_address2.meta_key = 'billing_address_2'
  LEFT JOIN wp_usermeta um_address_city on u.ID = um_address_city.user_id and um_address_city.meta_key = 'billing_city'
  LEFT JOIN wp_usermeta um_address_state on u.ID = um_address_state.user_id and um_address_state.meta_key = 'billing_state'
  LEFT JOIN wp_usermeta um_email on u.ID = um_email.user_id and um_email.meta_key = 'billing_email'
  LEFT JOIN wp_usermeta um_company on u.ID = um_company.user_id and um_company.meta_key = 'billing_company'
WHERE
  a.meta_key = '_product_id' AND a.meta_value = PRODUCT_ID