How to optimizate COUNT query with multiple LEFT JOIN
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()