如何通过许多JOIN提高查询性能 [英] How to Improve Query Performance with many JOINs
问题描述
我有一个查询(目的是创建视图),该查询使用一些联接来获取每一列.对于添加的每组联接,性能都会迅速下降(呈指数级?).
I have a query (with the purpose of making a view) which is using a few joins to get each column. Performance degrades quickly (exponentially?) for each set of joins added.
什么是使此查询更快的好方法?请在查询中查看评论.
What would be a good approach to make this query faster? Please see comments within the query.
如果有帮助,则使用的是WordPress DB模式.
If it helps, this is using the WordPress DB schema.
这是EXPLAIN的屏幕截图
Here is a screenshot of EXPLAIN
产品表
+--+----+
|id|name|
+--+----+
|1 |test|
+--+----+
元数据表
+----------+--------+-----+
|product_id|meta_key|value|
+----------+--------+-----+
|1 |price |9.99 |
+----------+--------+-----+
|1 |sku |ABC |
+----------+--------+-----+
TERM_RELATIONSHIPS表
+---------+----------------+
|object_id|term_taxonomy_id|
+---------+----------------+
|1 |1 |
+---------+----------------+
|1 |2 |
+---------+----------------+
TERM_TAXONOMY表
+----------------+-------+--------+
|term_taxonomy_id|term_id|taxonomy|
+----------------+-------+--------+
|1 |1 |size |
+----------------+-------+--------+
|2 |2 |stock |
+----------------+-------+--------+
条款表
+-------+-----+
|term_id|name |
+-------+-----+
|1 |500mg|
+-------+-----+
|2 |10 |
+-------+-----+
查询
SELECT
products.id,
products.name,
price.value AS price,
sku.value AS sku,
size.name AS size
FROM products
/* These joins are performing quickly */
INNER JOIN `metadata` AS price ON products.id = price.product_id AND price.meta_key = 'price'
INNER JOIN `metadata` AS sku ON products.id = sku.product_id AND sku.meta_key = 'sku'
/* Here's the part that is really slowing it down - I run this chunk about 5 times with different strings to match */
INNER JOIN `term_relationships` AS tr ON products.id = tr.object_id
INNER JOIN `term_taxonomy` AS tt
ON tr.term_taxonomy_id = tt.term_taxonomy_id AND tt.taxonomy = 'size'
INNER JOIN `terms` AS size
ON tt.term_id = size.term_id
推荐答案
您的性能问题最有可能是由与"term_taxonomy"表的联接引起的.
所有其他联接似乎都使用主键(您可能在上面使用了工作索引).
因此,我的建议是在 term_taxonomy_id 和 term_id 上添加复合索引(或者如果您必须: taxonomy ).像这样:
Your performance issue is most likely caused by the join with the 'term_taxonomy' table.
All other joins seems to use the primary key (where you probobly have working indexes on).
So my suggestion is to add a compound index on term_taxonomy_id and term_id (or if you must: taxonomy). Like this:
CREATE UNIQUE INDEX idx_term_taxonomy_id_taxonomy
ON term_taxonomy( term_taxonomy_id, taxonomy);
希望这会对您有所帮助.
Hope this will help you.
这篇关于如何通过许多JOIN提高查询性能的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!