CODEWITHSUNDEEP
mysqlsqlmagento
James
James

Reputation: 3283

3rd Nested Mysql Select Query Causes Huge Performance Issues

I'm doing some SQL queries on a Magento database to get some customer order information.

The query I want to run is as follows

SELECT * FROM catalog_category_product
WHERE product_id IN (
                    SELECT product_id FROM sales_flat_order_item
                    WHERE product_type = 'configurable'
                    AND order_id IN ( 
                                        SELECT entity_id FROM sales_flat_order
                                        WHERE customer_id = 12657
                                        AND state = 'complete')
)

Basically what this is doing, is getting all of the entity_id (order id's) for a customer, inserting those into a query which gets the product_ids from those orders, and then from that returning the category_ids for those products.

When I run this the query takes a very long time to run, I have killed it after waiting 2 minutes+ of it running.

If I replace the 3rd nested query with the hard coded calues in an array like so, then the query returns in about 5 seconds:

SELECT * FROM catalog_category_product
WHERE product_id IN (
                    SELECT product_id FROM sales_flat_order_item
                    WHERE product_type = 'configurable'
                    AND order_id IN ( 
13446, 13579, 13840, 14361, 14383, 14497, 14798, 16357, 17299, 17332, 18338, 18542, 18755, 19786, 20006
)
)

I'm not too familiar with MySQL performance so I'm not sure if I'm pushing the boundaries.

If I run the 3rd nested query it will return in less than a second.

SELECT entity_id FROM sales_flat_order
                                        WHERE customer_id = 12657
                                        AND state = 'complete'

If I take those values and put them in the 2nd Nested Queries 'IN' then it the top query will return in ~5 seconds.

Why does combining those cause it to blow out?

Upvotes: 0

Views: 283

Answers (2)

BateTech
BateTech

Reputation: 6526

I'm not familiar with the database structure, but try changing to use joins instead of nested in statements. This is assuming a one-to-many relationship between the sales_flat_order and sales_flat_order_item tables, and a one-to-one relationship between the sales_flat_order_item and catalog_category_product tables.

SELECT DISTINCT ccp.* --this will return all columns for the catalog_category_product table
--Or if you only wanted the "category_ids for those products", then use this instead: SELECT DISTINCT ccp.category_id  
FROM sales_flat_order sfo
INNER JOIN sales_flat_order_item sfoi ON sfo.entity_id = sfoi.order_id 
INNER JOIN catalog_category_product ccp ON ccp.product_id = sfoi.product_id
WHERE sfo.customer_id = 12657
AND sfo.state = 'complete'
AND sfoi.product_type = 'configurable'

Upvotes: 2

Tim Burch
Tim Burch

Reputation: 1103

Sub-queries should be used only when absolutely necessary, as they necessarily (or almost always) diminish performance. In this case you could get the same results with the following query, and I'm guessing the performance will be much improved:

SELECT ccp.*
FROM catalog_category_product ccp
    INNER JOIN sales_flat_order_item sfoi ON ccp.product_id = sfoi.product_id
    INNER JOIN sales_flat_order sfo ON sfoi.order_id = sfo.entity_id
WHERE sfoi.product_type = 'configurable'
    AND sfo.customer_id = 12657
    AND sfo.state = 'complete';

Upvotes: 2

Related Questions