如何优化此查询(涉及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屋！