How to optimize this query (4mm tables involved)
I am working with a legacy database schema that looks like this:
product_table
The table has fields:
uid
(int, primary key) name
(varchar 50)
category
the table has fields:
uid
(int, primary key) name
(varchar 50)
That's right, now product_table
has a 4 MM relation to the category table:
product_table__category_1__mm
has fields:
uid_local
(int, contains product_table.uid
) uid_foreign
(int, contains category.uid
)
product_table__category_2__mm
has fields:
uid_local
(int, contains product_table.uid
) uid_foreign
(int, contains category.uid
)
product_table__category_3__mm
has fields:
uid_local
(int, contains product_table.uid
) uid_foreign
(int, contains category.uid
)
product_table__category_4__mm
has fields:
uid_local
(int, contains product_table.uid
) uid_foreign
(int, contains category.uid
)
(yes, all 4MM tables have the same fields and all refer to the table category
)
So, if I want to leverage all four connections and filter based on the int values selected by the user, it looks 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 this is a big request, but I cannot change the design of the db as it is already widely used.
Any idea on how to make this request faster? Where would you put the indexes?
source to share
You need to get rid of GROUP BY
here.
MySQL
doesn't help optimize it.
Rewrite your query like this:
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
…
Create indexes:
product_table__category_*__mm (uid_local, uid_foreign)
or, better, declare them PRIMARY KEYs
to product_table__category_*__mm
:
ALTER TABLE product_table__category_*__mm ADD CONSTRAINT pk_pc*mm_local_foreign (uid_local, uid_foreign)
source to share
It is unlikely that at any moment you will need all of this data at the same time. You can correct me if I'm wrong, but if this is used everywhere as a kind of "master" query, I would:
-
Break it down into smaller queries that only use the tables needed for certain operations,
-
Use Select1 field, field2.etc instead of Select *, specifying only those fields that are required for certain operations, and
-
Make sure all primary keys and foreign keys are indexed.
source to share
Well, both fields in the "mm" tables need to be indexed, as does the uid field in the product table and uid in the category table. But my guess is that all of these fields are already indexed.
To make it easier to understand the queries about this mess, you might consider making the query you posted above in the view.
source to share