Given a configurable product ID (45535
), I'm getting the IDs of other configurable products that are linked via a simple product. I'm trying to see if this query can be made faster.. Is it possible to make this query any faster?
SELECT l2.`parent_id`
FROM catalog_product_super_link AS l
INNER JOIN catalog_product_super_link AS l2
ON l.`product_id` = l2.`product_id`
WHERE l.`parent_id` = 45535 AND l2.`parent_id` != 45535 GROUP BY l2.`parent_id`;
When I run EXPLAIN
, I get the following
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE l ref UNQ_CATALOG_PRODUCT_SUPER_LINK_PRODUCT_ID_PARENT_ID,IDX_CATALOG_PRODUCT_SUPER_LINK_PARENT_ID,IDX_CATALOG_PRODUCT_SUPER_LINK_PRODUCT_ID IDX_CATALOG_PRODUCT_SUPER_LINK_PARENT_ID 4 const 30 Using temporary; Using filesort
1 SIMPLE l2 ref UNQ_CATALOG_PRODUCT_SUPER_LINK_PRODUCT_ID_PARENT_ID,IDX_CATALOG_PRODUCT_SUPER_LINK_PARENT_ID,IDX_CATALOG_PRODUCT_SUPER_LINK_PRODUCT_ID UNQ_CATALOG_PRODUCT_SUPER_LINK_PRODUCT_ID_PARENT_ID 4 test_db.l.product_id 1 Using where; Using index
Table catalog_product_super_link
has about 42K rows.
Best Answer
I usually avoid direct SQL queries. Hopefully you're building some sort of reporting mechanism to generate some business intelligence. Please don't build a feature for your store based on the following recommendations.
In this case you probably would do well to create an in-memory temporary table which is populated by all configurable links that have 45535 as its' parent.
And then select from this table:
The
GROUP BY
here collapses duplicates - you could probably useDISTINCT
as well. Of course you want to tear down the temporary table afterward:Alternative methods:
Same technique, different methods. Disclaimer: these may not be faster.
Local vars:
Subquery:
Disclaimer:
Query optimization is probably off-topic for Magento.SE - but I spent the better part of 1.0-1.3CE creatively optimizing sales reports for EAV I thought I'd help you out. I'm not a DBA.
In absence of having some sample data from you I've cobbled together the above, largely untested, as to guide you into other methods (besides the self-join) to accomplish a similar result.