使用大量内部连接改进查询 wp_postmeta，一个键/值表

2021-12-05 00:00:00 sql key-value database mysql wordpress entity-attribute-value 实体属性值

我正在使用一个正在执行以下查询的 wordpress 网站，但我看到此查询正在执行许多内部联接，并且该网站需要很长时间才能加载并出现故障，我一直在尝试创建一个查询同样的结果，但还没有成功

am working with a wordpress website that is performing the following query, but I see this query is doing many inner joins and the website takes long to load and goes down a lot, and I have been trying to create a query that produces the same result but with no success yet

我想知道有什么更好的方法可以做到这一点

I would like to know what could be a better way to do this

SELECT *
FROM wp_posts
INNER JOIN wp_postmeta color ON wp_posts.ID = color.post_id 
INNER JOIN wp_postmeta transmission ON wp_posts.ID = transmission.post_id 
INNER JOIN wp_postmeta model ON wp_posts.ID = model.post_id 
INNER JOIN wp_postmeta brand ON wp_posts.ID = brand.post_id 

AND color.meta_key = 'color' 
AND color.meta_value = 'red' 
AND transmission.meta_key = 'transmission' 
AND transmission.meta_value = 'auto' 
AND model.meta_key = 'model' 
AND model.meta_value = 'model' 
AND brand.meta_key = 'brand' 
AND brand.meta_value = 'brand'

AND wp_posts.post_status = 'publish'
AND wp_posts.post_type = 'car'
ORDER BY wp_posts.post_title

这是解释输出.

+----+-------------+-----------+--------+-----------------------------+----------+---------+------------------------+------+----------------------------------------------+
| id | select_type | table         | type   | possible_keys               | key      | key_len | ref                          | rows | Extra                                        |
+----+-------------+-----------+--------+-----------------------------+----------+---------+------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | color         | ref    | post_id,meta_key            | meta_key | 768     | const                        |  629 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | wp_posts      | eq_ref | PRIMARY,type_status_date,ID | PRIMARY  | 8       | tmcdb.color.post_id          |    1 | Using where                                  |
|  1 | SIMPLE      | brand         | ref    | post_id,meta_key            | post_id  | 8       | tmcdb.wp_posts.ID            |    4 | Using where                                  |
|  1 | SIMPLE      | transmission  | ref    | post_id,meta_key            | post_id  | 8       | tmcdb.color.post_id          |    4 | Using where                                  |
|  1 | SIMPLE      | model         | ref    | post_id,meta_key            | post_id  | 8       | tmcdb.transmission.post_id   |    4 | Using where                                  |
+----+-------------+-----------+--------+-----------------------------+----------+---------+------------------------+------+----------------------------------------------+

此处为 Wordpress 架构.

推荐答案

您似乎正在尝试获取一个结果集，每个帖子类型为 car.看来你想在帖子中显示每辆车的各种属性，这些都藏在postmeta中.

It seems you are trying to obtain a result set with one row per post of type car. It seems you want to display various attributes of each car in the post, and those are stashed away in postmeta.

专业提示:切勿在软件中使用 SELECT * ，除非您完全知道为什么要这样做.特别是对于包含大量 JOIN 操作的查询，SELECT * 返回大量无意义和冗余的列.

Pro tip: Never use SELECT * in software unless you absolutely know why you're doing it. Especially with queries containing lots of JOIN operations, SELECT * returns lots of pointless and redundant columns.

有一个关于 WordPress postmeta 表的查询设计技巧.如果您想获取特定属性，请执行以下操作:

There's a query design trick to know for the WordPress postmeta table. If you want to get a particular attribute, do this:

 SELECT p.ID, p.post_title,
        color.meta_value AS color
   FROM wp_posts AS p
   LEFT JOIN wp_postmeta AS color ON p.ID = color.post_id AND 'color' = color.meta_key
  WHERE p.post_status = 'publish'
    AND /* etc etc */

在做你想做的事情时理解这种模式非常重要.这种模式是必需的，因为 postmeta 是一种特殊类型的表，称为 key-value 或 实体属性值 存储.这里发生了什么?几点:

It's super-important to understand this pattern when doing what you're trying to do. This pattern is required because postmeta is a peculiar type of table called a key-value or entity-attribute-value store. What's going on here? A few things:

  1. 使用此模式，您可以为每个帖子获取一行，其中一些列来自 posts 表，以及来自 postmeta 表的特定属性.
  2. 您正在 LEFT JOIN 加入 postmeta 表，因此如果缺少该属性，您仍然可以获得一行.
  3. 您正在使用 postmeta 表的别名.这是postmeta AS color.
  4. 您在连接的 ON 条件中包含了 meta_key 的选择器(这里是 'color' = color.meta_key).
  5. 您在 SELECT 子句中使用别名来显示具有适当列名的 postmeta.meta_value 项目.这是color.meta_value AS color.
  1. Using this pattern uou get one row for each post, with some columns from the posts table and a particular attribute from the postmeta table.
  2. You are LEFT JOINing the postmeta table so you still get a row if the attribute is missing.
  3. You are using an alias name for the postmeta table. Here it's postmeta AS color.
  4. You are including the selector for meta_key (here it's 'color' = color.meta_key) in the ON condition of the join.
  5. You are using an alias in your SELECT clause to present the postmeta.meta_value item with an appropriate column name. Here it's color.meta_value AS color.

一旦您习惯了使用这种模式，您就可以将其堆叠起来，通过一系列LEFT JOIN 操作，获得许多不同的属性，就像这样.

Once you get used to employing this pattern, you can stack it up, with a cascade of LEFT JOIN operations, to get lots of different attributes, like so.

     SELECT wp_posts.ID, wp_posts.post_title, wp_posts.whatever,
            color.meta_value        AS color,
            transmission.meta_value AS transmission,
            model.meta_value        AS model,
            brand.meta_value        AS brand
       FROM wp_posts

  LEFT JOIN wp_postmeta  AS color 
         ON wp_posts.ID = color.post_id        AND color.meta_key='color'

  LEFT JOIN wp_postmeta  AS transmission
         ON wp_posts.ID = transmission.post_id AND transmission.meta_key='transmission'

  LEFT JOIN wp_postmeta  AS model
         ON wp_posts.ID = model.post_id        AND model.meta_key='model'

  LEFT JOIN wp_postmeta  AS  brand
         ON wp_posts.ID = brand.post_id        AND brand.meta_key='brand'

      WHERE wp_posts.post_status = 'publish'
        AND wp_posts.post_type = 'car'
   ORDER BY wp_posts.post_title

我对这个查询做了很多缩进，以便更容易地看到模式.您可能更喜欢不同的缩进样式.

I've done a bunch of indenting on this query to make it easier to see the pattern. You may prefer a different indenting style.

很难知道为什么您的问题中的查询会出现性能问题.这可能是因为所有的 INNER JOIN 操作都得到了组合爆炸，然后被过滤了.但无论如何，您显示的查询可能没有返回任何行.

It's hard to know why you were having performance problems with the query in your question. It's possibly because you were getting a combinatorial explosion with all the INNER JOIN operations that was then filtered. But at any rate the query you showed was probably returning no rows.

如果您仍然遇到性能问题，请尝试在 (post_id, meta_key, meta_value) 列的 postmeta 上创建复合索引.如果您要创建 WordPress 插件，这可能是插件安装时要做的工作.

If you are still having performance trouble, try creating a compound index on postmeta on the (post_id, meta_key, meta_value) columns. If you're creating a WordPress plugin, that's probably a job to do at plugin installation time.

相关文章