How to optimizate COUNT query with multiple LEFT JOIN

Question

I have query that select filters for current category and show how many products in each filter are Atm i have 20k products and 10k filters for test that take around 1sec. to execute that is not to good for me is there anything i can do to optimize this query ?

Example of my query with selected filters (pf tables are added dynamically depend of filter groups )

SELECT SQL_NO_CACHE sd.filter_group_id,
        fgd.name AS group_name,
        pf.filter_id AS filter_id,
        fd.name,
        COUNT(DISTINCT p2c.product_id) AS total
    FROM oc_product_to_category p2c 
    LEFT JOIN oc_product_filter pf5 ON pf5.product_id = p2c.product_id 
    LEFT JOIN oc_product_filter pf8 ON pf8.product_id = p2c.product_id 
    LEFT JOIN oc_product_filter pf10 ON pf10.product_id = p2c.product_id 
    LEFT JOIN oc_product_filter pf3 ON pf3.product_id = p2c.product_id 
    LEFT JOIN oc_product_filter pf9 ON pf9.product_id = p2c.product_id 
    LEFT JOIN oc_product_filter pf6 ON pf6.product_id = p2c.product_id 
    LEFT JOIN oc_product p ON p.product_id = p2c.product_id
    LEFT JOIN oc_product_filter pf ON pf.product_id = p2c.product_id
    LEFT JOIN oc_filter f ON pf.filter_id = f.filter_id
    LEFT JOIN oc_filter_description fd ON pf.filter_id = fd.filter_id
    LEFT JOIN oc_filter_group_description fgd
          ON fd.filter_group_id = fgd.filter_group_id
    LEFT JOIN oc_sd_filter sd
          ON sd.category_id = p2c.category_id AND sd.status = 1
     WHERE p2c.category_id = '64' 
     AND sd.filter_group_id = fd.filter_group_id 
     AND p.status = 1
         AND pf5.filter_id IN (33) 
         AND pf8.filter_id IN (47,141,143,144,145) 
         AND pf10.filter_id IN (118,120,121,122,123,128,129,130,134,138,157,163) 
         AND pf3.filter_id IN (9,11) 
         AND pf9.filter_id IN (57,58,59,60,61,94,95) 
         AND pf6.filter_id IN (116)
        GROUP BY fd.filter_id, fd.filter_group_id
        ORDER BY sd.sort_order ASC, 
        (CASE WHEN fgd.custom_order = 0 THEN f.sort_order END) ASC, 
        (CASE WHEN fgd.custom_order = 1 THEN COUNT(p2c.product_id) END) DESC

Here are my table structure

        CREATE TABLE `oc_product` (
      `product_id` int(11) NOT NULL,
      `model` varchar(64) NOT NULL,
      `sku` varchar(64) NOT NULL,
      `upc` varchar(12) NOT NULL,
      `ean` varchar(14) NOT NULL,
      `jan` varchar(13) NOT NULL,
      `isbn` varchar(17) NOT NULL,
      `mpn` varchar(64) NOT NULL,
      `location` varchar(128) NOT NULL,
      `quantity` int(4) NOT NULL DEFAULT 0,
      `manufacturer_id` int(11) NOT NULL,
      `price` decimal(15,4) NOT NULL DEFAULT 0.0000,
      `tax_class_id` int(11) NOT NULL,
      `date_available` date NOT NULL DEFAULT '0000-00-00',
      `sort_order` int(11) NOT NULL DEFAULT 0,
      `status` tinyint(1) NOT NULL DEFAULT 0,
      `date_added` datetime NOT NULL,
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;


    ALTER TABLE `oc_product`
      ADD PRIMARY KEY (`product_id`),
      ADD KEY `model` (`model`),
      ADD KEY `manufacturer_id` (`manufacturer_id`),
      ADD KEY `sort_order` (`sort_order`),
      ADD KEY `status` (`status`) USING BTREE;

        CREATE TABLE `oc_product_to_category` (
      `product_id` int(11) NOT NULL,
      `category_id` int(11) NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

    ALTER TABLE `oc_product_to_category`
      ADD PRIMARY KEY (`product_id`,`category_id`),
      ADD KEY `category_id` (`category_id`);

        CREATE TABLE `oc_product_filter` (
      `product_id` int(11) NOT NULL,
      `filter_id` int(11) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    ALTER TABLE `oc_product_filter`
      ADD PRIMARY KEY (`product_id`,`filter_id`);


        CREATE TABLE `oc_sd_filter` (
      `id` int(11) NOT NULL,
      `category_id` int(11) NOT NULL,
      `filter_group_id` int(11) NOT NULL,
      `status` int(11) NOT NULL,
      `sort_order` int(11) NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

    -
    ALTER TABLE `oc_sd_filter`
      ADD PRIMARY KEY (`id`),
      ADD KEY `filter-category` (`category_id`,`filter_group_id`);



    CREATE TABLE `oc_filter` (
      `filter_id` int(11) NOT NULL,
      `filter_group_id` int(11) NOT NULL,
      `sort_order` int(3) NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

    ALTER TABLE `oc_filter`
      ADD PRIMARY KEY (`filter_id`);


        CREATE TABLE `oc_filter_description` (
      `filter_id` int(11) NOT NULL,
      `language_id` int(11) NOT NULL,
      `filter_group_id` int(11) NOT NULL,
      `name` varchar(64) NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

    ALTER TABLE `oc_filter_description`
      ADD PRIMARY KEY (`filter_id`,`language_id`),
      ADD KEY `filter` (`filter_group_id`);


        CREATE TABLE `oc_filter_group_description` (
      `filter_group_id` int(11) NOT NULL,
      `language_id` int(11) NOT NULL,
      `name` varchar(64) NOT NULL,
      `level` int(11) NOT NULL,
      `custom_order` int(11) NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

    ALTER TABLE `oc_filter_group_description`
      ADD PRIMARY KEY (`filter_group_id`,`language_id`);

UPDATE:

pf10.filter_id IN (118,120,121,122,123,128,129,130,134,138,157,163)

pf10 represents filter_group

IN (118,120.....) represents selected filters for current filter_group

for each selected filter that is in different filter_group i do LEFT JOIN, then all filters from same group go in same IN()

UPDATE 2

After few test done i found that in WHERE caluse

 AND sd.filter_group_id = fd.filter_group_id
 AND p.status = 1

Increase time a lot, if i move them on the LEFT JOIN like this

LEFT JOIN oc_product p ON p.product_id = p2c.product_id AND p.status = 1
LEFT JOIN oc_sd_filter sd ON sd.category_id = p2c.category_id AND sd.status = 1 AND sd.filter_group_id = fd.filter_group_id

Executing time become around 0.0600 but results are not same

How to handle this problem or do i need to change query at all?

Answer 1

I would attempt to get rid of most of the JOINs by having some bit strings in a few INTs, then do boolean tests. For example,

AND pf3.filter_id IN (9,11) 

becomes something like

AND (p.filter3 & 0x...) != 0

with filter3 containing some of the flags you have and 0x... having 2 bits that represent cases 9 and 11. (I may be able to describe this better if I had some understanding of what "9" represents and how the flags are grouped.)

That would eliminate many of the JOINs.

oc_product_to_category will perform better with InnoDB. See this for more discussion: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

Please provide EXPLAIN SELECT ....

Please don't use LEFT JOIN when a JOIN is what is meant.

Though it works to mix MyISAM and InnoDB, it is probably better to use only InnoDB.

I may have more advice; see what you can do with these tips, then I can make another pass.