How can I improve this query where multiple joins on the same table appears to be affecting performance

Question

The below query is used to apply multiple filter values when searching the product database on an eCommerce website.

When you use more than 7 filters, MySQL server goes down.

• Is there any chance to join positions_categories_links once?
• Should I consider changing the database structure?

Each product has one eshop_cats category (many2one) and up to 20 categories with multiple relations (many2many).

Table eshop_pos has 40,000 records and contains the Products.

Table eshop_cats has 340 records and contains the Main Categories.

Table categories has 6000 records and contains the Duplex Categories.

Table positions_categories_links has 360,000 records and contains the key between Products and Categories.

---

This is my query:

SELECT COUNT(DISTINCT eshop_pos.id)
FROM eshop_pos
INNER JOIN eshop_cats t1 ON eshop_pos.eshopcatid = t1.id
    AND t1.active = 1
INNER JOIN positions_categories_links t2 ON t2.pos_id = eshop_pos.id
INNER JOIN categories t3 ON t3.id = t2.cat_id
    AND t3.active = 1
    AND t3.section_id = 62021
INNER JOIN positions_categories_links t4 ON t4.pos_id = eshop_pos.id
INNER JOIN categories t5 ON t5.id = t4.cat_id
    AND t5.active = 1
    AND t5.section_id = 62023
INNER JOIN positions_categories_links AS duplex_links_51 ON duplex_links_51.pos_id = eshop_pos.id
    AND duplex_links_51.cat_id = 51
    AND duplex_links_51.value IN (2984)
INNER JOIN positions_categories_links AS duplex_links_52 ON duplex_links_52.pos_id = eshop_pos.id
    AND duplex_links_52.cat_id = 52
    AND duplex_links_52.value IN (3003)
INNER JOIN positions_categories_links AS duplex_links_3904 ON duplex_links_3904.pos_id = eshop_pos.id
    AND duplex_links_3904.cat_id = 3904
    AND duplex_links_3904.value IN (3941)
INNER JOIN positions_categories_links AS duplex_links_4462 ON duplex_links_4462.pos_id = eshop_pos.id
    AND duplex_links_4462.cat_id = 4462
    AND duplex_links_4462.value IN (4465)
INNER JOIN positions_categories_links AS duplex_links_4466 ON duplex_links_4466.pos_id = eshop_pos.id
    AND duplex_links_4466.cat_id = 4466
    AND duplex_links_4466.value IN (4468)
INNER JOIN positions_categories_links AS duplex_links_4472 ON duplex_links_4472.pos_id = eshop_pos.id
    AND duplex_links_4472.cat_id = 4472
    AND duplex_links_4472.value IN (4473)
INNER JOIN positions_categories_links AS duplex_links_4974 ON duplex_links_4974.pos_id = eshop_pos.id
    AND duplex_links_4974.cat_id = 4974
    AND duplex_links_4974.value IN (4978)
INNER JOIN positions_categories_links AS duplex_links_4979 ON duplex_links_4979.pos_id = eshop_pos.id
    AND duplex_links_4979.cat_id = 4979
    AND duplex_links_4979.value IN (4982)
INNER JOIN positions_categories_links AS duplex_links_4984 ON duplex_links_4984.pos_id = eshop_pos.id
    AND duplex_links_4984.cat_id = 4984
    AND duplex_links_4984.value IN (4986)

I can not run EXPLAIN of the query on the server. However, it works well my local laptop:

Answer 1

Slightly altered formatting of your query. I also changed alias references from your "duplex_links" references to an abbreviated alias "PCL" (from your Position_categories_links table).. shorter and correlated helps table references (at least to me and probably others).

As for your tables / indexes, I would suggest the following table/indexes if they do not already exist. In this case, I have all covering indexes for your query meaning that since the columns used to satisfy all the join criteria are part of the index, the sql database does not have to go to the underlying actual data pages to confirm additional details, thus helping improve performance.

Table                       Index
eshop_pos                   (id, eshopcatid)
eshop_cats                  (id, active)
positions_categories_links  (pos_id, cat_id, value)
categories                  (id, active, section_id)

I also like to show indentation correlation between where the joins are so you know how to get from one table/alias to the next level.. You can directly see hierarchy where things come from.

SELECT 
      COUNT(DISTINCT eshop_pos.id) 
   FROM 
      eshop_pos 
         inner join eshop_cats t1 
            on eshop_pos.eshopcatid = t1.id 
           AND t1.active = 1 

         inner join positions_categories_links t2 
            on eshop_pos.id = t2.pos_id

            inner join categories t3 
               on t2.cat_id = t3.id
              and t3.active = 1 
              and t3.section_id = 62021 

         inner join positions_categories_links t4 
            on eshop_pos.id = t4.pos_id

            inner join categories t5 
               on t4.cat_id = t5.id
              and t5.active = 1 
              and t5.section_id = 62023 

         INNER JOIN positions_categories_links AS PCL51 
            ON eshop_pos.id = PCL51.pos_id
           AND PCL51.cat_id = 51 
           and PCL51.value in (2984) 

         INNER JOIN positions_categories_links AS PCL52 
            ON eshop_pos.id = PCL52.pos_id
           AND PCL52.cat_id = 52 
           and PCL52.value in (3003) 

         INNER JOIN positions_categories_links AS PCL3904 
            ON eshop_pos.id = PCL3904.pos_id
           AND PCL3904.cat_id = 3904 
           and PCL3904.value in (3941) 

         INNER JOIN positions_categories_links AS PCL4462
            ON eshop_pos.id = PCL4462.pos_id
           AND PCL4462.cat_id = 4462 
           and PCL4462.value in (4465) 

         INNER JOIN positions_categories_links AS PCL4466 
            ON eshop_pos.id = PCL4466.pos_id
           AND PCL4466.cat_id = 4466 
           and PCL4466.value in (4468) 

Now, having proper indexes to help optimize the query is one thing, but constantly doing a count on such multiple criteria might be overkill. If you have a known specific detail level such as specific category as you do here definitely helps.

(removed 'or' version which does not guarantee all criteria)