如何优化此查询(涉及4毫米表) [英] how to optimize this query (4 mm tables involved)
问题描述
我正在使用如下所示的旧数据库架构:
I am working with a legacy database schema that looks like this:
product_table
表具有字段:
uid
(整数,主键)
name
(varchar 50)
uid
(int, primary key)
name
(varchar 50)
category
表具有字段:
uid
(整数,主键)
name
(varchar 50)
uid
(int, primary key)
name
(varchar 50)
好吧,现在product_table
与类别表有4个MM关系:
Allright, now the product_table
has 4 MM relations to the category table:
product_table__category_1__mm
具有字段:
uid_local
(int,包含product_table.uid
)
uid_foreign
(int,包含category.uid
)
uid_local
(int, contains product_table.uid
)uid_foreign
(int, contains category.uid
)
product_table__category_2__mm
具有字段:
uid_local
(int,包含product_table.uid
)
uid_foreign
(int,包含category.uid
)
uid_local
(int, contains product_table.uid
)uid_foreign
(int, contains category.uid
)
product_table__category_3__mm
具有字段:
uid_local
(int,包含product_table.uid
)
uid_foreign
(int,包含category.uid
)
uid_local
(int, contains product_table.uid
)uid_foreign
(int, contains category.uid
)
product_table__category_4__mm
具有字段:
uid_local
(int,包含product_table.uid
)
uid_foreign
(int,包含category.uid
)
uid_local
(int, contains product_table.uid
)uid_foreign
(int, contains category.uid
)
(是的,所有4个MM表都具有相同的字段,并且都与category
表相关)
(yes, all the 4 MM tables have the same fields and all relate to the category
table)
因此,如果我要拉入所有四个联接并根据用户选择的int值进行过滤,则外观如下所示:
So, if I want to pull in all four joins and filter based on int values selected by the user it would look something like this:
select
product_table.*
from
product_table
inner join product_table__category_1__mm mm_1 on mm_1.uid_local = product_table.uid
inner join category cat_1 on cat_1.uid = mm_1.uid_foreign and cat_1.uid in (7, 8)
inner join product_table__category_2__mm mm_2 on mm_2.uid_local = product_table.uid
inner join category cat_2 on cat_2.uid = mm_2.uid_foreign and cat_2.uid in (63, 52)
inner join product_table__category_3__mm mm_3 on mm_3.uid_local = product_table.uid
inner join category cat_3 on cat_3.uid = mm_3.uid_foreign and cat_3.uid in (84, 12)
inner join product_table__category_4__mm mm_4 on mm_4.uid_local = product_table.uid
inner join category cat_4 on cat_4.uid = mm_4.uid_foreign and cat_4.uid in (16, 33)
group by product_table.uid ### --> in order to get unique results
现在那是一个很大的查询,但是由于它已经被广泛使用,因此我无法更改数据库设计.
Now thats a big query, but I cant change the db design as it is already widely in use.
关于如何使此查询更快的任何想法?您将索引放在哪里?
Any idea on how to make this query faster? Where would you put indexes?
推荐答案
您需要在此处摆脱GROUP BY
.
MySQL
不能很好地对其进行优化.
MySQL
is not good in optimizing it.
将查询重写为:
SELECT *
FROM product_table
WHERE EXISTS
(
SELECT NULL
FROM product_table__category_1__mm mm_1
JOIN category cat_1
ON cat_1.uid = mm_1.uid_foreign
WHERE mm_1.uid_local = product_table.uid
AND mm_1.uid_foreign IN (7, 8)
)
AND
…
创建索引:
product_table__category_*__mm (uid_local, uid_foreign)
或者更好的是,在product_table__category_*__mm
上将它们声明为PRIMARY KEYs
:
or, better, declare them PRIMARY KEYs
on product_table__category_*__mm
:
ALTER TABLE product_table__category_*__mm ADD CONSTRAINT pk_pc*mm_local_foreign (uid_local, uid_foreign)
这篇关于如何优化此查询(涉及4毫米表)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!