使用到wp_postmeta(键/值表)的大量内部联接来改进查询 [英] Improving a query using a lot of inner joins to wp_postmeta, a key/value table
问题描述
我正在使用一个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 |
+----+-------------+-----------+--------+-----------------------------+----------+---------+------------------------+------+----------------------------------------------+
$ b b
推荐答案
似乎你正在尝试获得一个结果集,每个类型 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
是一种称为键/值存储的特殊类型的表。这里发生了什么?有几件事:
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 store. What's going on here? A few things:
- 使用此模式,每个帖子都有一行,
/ code>表和
postmeta
表中的特定属性。 - 您是
LEFT JOIN c> c> $> $>
- 您包含
meta_key
(ON
条件下的'color'= color.meta_key
)。 - 您在
SELECT
子句中使用别名以显示postmeta.meta_value
适当的列名。这里是color.meta_value AS颜色
。
<表的
postmeta
的别名。这里是 postmeta AS颜色
。 - Using this pattern uou get one row for each post, with some columns from the
posts
table and a particular attribute from thepostmeta
table. - You are
LEFT JOIN
ing thepostmeta
table so you still get a row if the attribute is missing. - You are using an alias name for the
postmeta
table. Here it'spostmeta AS color
. - You are including the selector for
meta_key
(here it's'color' = color.meta_key
) in theON
condition of the join. - You are using an alias in your
SELECT
clause to present thepostmeta.meta_value
item with an appropriate column name. Here it'scolor.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.
如果您仍然有性能问题,请尝试在 postmeta上创建复合索引
。如果你正在创建一个WordPress插件,这可能是在插件安装时做的工作。(post_id,meta_key,meta_value)
列中的
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.
这篇关于使用到wp_postmeta(键/值表)的大量内部联接来改进查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!