how to optimize this query (4 mm tables involved)

Question

I am working with a legacy database schema that looks like this:

product_table table has fields:

uid (int, primary key)
name (varchar 50)

category table has fields:

uid (int, primary key)
name (varchar 50)

---

Allright, now the product_table has 4 MM relations 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 the 4 MM tables have the same fields and all relate to the category table)

---

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?

Answer 1

It's unlikely that, at any given moment, you need all of this data simultaneously. You can correct me if I'm wrong, but if this is being used everywhere as sort of a "master" query I would:

1. Break it into smaller queries that use only the tables that are needed in specific operations,

2. Use Select field1, field2 .etc instead of Select *, specifying only the fields you need for specific operations, and

3. Make sure all of the primary keys and foreign keys have indexes.

Answer 2

Well, both fields on the "mm" tables should be indexed, as should the uid field on the product table and the uid on the category table. But my guess would be that all those fields are indexed already.

To make it easier to understand queries against that mess, you might want to consider making the query you posted above into a view as well.

Answer 3

You need to get rid of GROUP BY here.

MySQL is not good in optimizing it.

Rewrite your query as 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 the indexes:

product_table__category_*__mm (uid_local, uid_foreign)

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)