MySQL Forums
Forum List  »  Optimizer & Parser

How to optimizate COUNT query with multiple LEFT JOIN
Posted by: Ivan Stoqnov
Date: June 01, 2021 01:19AM

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 ?

I found that in WHERE caluse

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

increase executing time a lot if u 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 ?


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=InnoDB 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`);

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()

Options: ReplyQuote


Subject
Views
Written By
Posted
How to optimizate COUNT query with multiple LEFT JOIN
129
June 01, 2021 01:19AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.