0

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

enter image description here

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?

1
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.

8
  • i add EXPLAIN in main topic also more info under UPDATE, also i chnage oc_product_to_category to InnoDB and time decrease with 0.050, when i change LEFT JOIN to JOIN time is increase with 0.500 and become around 1.500-600, i do not understand how to do test for boolean and how to mkae this AND (p.filter3 & 0x...) != 0
    – Ivan
    May 31 '21 at 8:36
  • @Ivan - LEFT JOIN vs JOIN -- did you get the same results? Did the EXPLAIN change?
    – Rick James
    May 31 '21 at 14:51
  • @Ivan - not quite the same - stackoverflow.com/questions/67754349/…
    – Rick James
    May 31 '21 at 14:58
  • LEFT JOIN vs JOIN result is same and EXPLAIN is same, only time is increased, main tipic updated with more testing info
    – Ivan
    Jun 1 '21 at 6:56
  • This might help: oc_product_filter: INDEX(filter_id).
    – Rick James
    Jun 1 '21 at 16:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.