0

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.

2

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.

CREATE TABLE cpsl_memory ENGINE=MEMORY
SELECT * FROM catalog_product_super_link WHERE parent_id = 45535;

And then select from this table:

SELECT product_id FROM cpsl_memory 
WHERE parent_id != 45535
GROUP BY product_id;

The GROUP BY here collapses duplicates - you could probably use DISTINCT as well. Of course you want to tear down the temporary table afterward:

DROP TABLE cpsl_memory;

Alternative methods:

Same technique, different methods. Disclaimer: these may not be faster.

Local vars:

SET @instmt = (SELECT GROUP_CONCAT(DISTINCT product_id SEPARATOR ',') FROM catalog_product_super_link WHERE parent_id=45535);
SELECT * FROM catalog_product_super_link WHERE parent_id NOT IN (@instmt);

Subquery:

SELECT * FROM catalog_product_super_link cpsl
WHERE cpsl.parent_id <> 45535
AND cpsl.product_id IN (
    SELECT product_id FROM catalog_product_super_link cpslsq WHERE parent_id=45535;
)

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.

| improve this answer | |

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.