我有一个查询(目的是创建一个视图),它使用一些连接来获取每一列.对于添加的每组连接,性能会快速降低(指数级?).
什么是使这个查询更快的好方法?请查看查询中的注释.
如果有帮助,这是使用WordPress数据库架构.
这是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 | +-------+-----+
QUERY
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
小智.. 13
您的性能问题很可能是由与'term_taxonomy'表的连接引起的.
所有其他连接似乎都使用主键(您可能在其上有工作索引).
所以我的建议是在term_taxonomy_id 和 term_id上添加复合索引 (或者如果你必须:分类法).像这样:
CREATE UNIQUE INDEX idx_term_taxonomy_id_taxonomy ON term_taxonomy( term_taxonomy_id, taxonomy);
希望这会帮助你.
您的性能问题很可能是由与'term_taxonomy'表的连接引起的.
所有其他连接似乎都使用主键(您可能在其上有工作索引).
所以我的建议是在term_taxonomy_id 和 term_id上添加复合索引 (或者如果你必须:分类法).像这样:
CREATE UNIQUE INDEX idx_term_taxonomy_id_taxonomy ON term_taxonomy( term_taxonomy_id, taxonomy);
希望这会帮助你.