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?
Best Answer
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,
becomes something like
with
filter3
containing some of the flags you have and0x...
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_tablePlease provide
EXPLAIN SELECT ...
.Please don't use
LEFT JOIN
when aJOIN
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.