Help optimizing a query with 16 subqueries

Posted by Webnet on Stack Overflow See other posts from Stack Overflow or by Webnet
Published on 2010-05-27T14:10:18Z Indexed on 2010/05/27 14:21 UTC
Read the original article Hit count: 105

Filed under:
|

I have indexes/primaries on all appropriate ID fields for each type. I'm wondering though how I could make this more efficient. It takes a while to load the page with only 15,000 rows and that'll quickly grow to 500k.

The $whereSql variable simply has a few more parameters for the main ebay_archive_listing table.

NOTE: This is all done in a single query because I have ASC/DESC sorting for each subquery value.

NOTE: I've converted some of the sub queries to INNER JOIN's

SELECT
                    product_master.product_id,
                    (
                        SELECT
                            COUNT(listing_id)
                        FROM ebay_archive_product_listing_assoc '.$listingCountJoin.'
                        WHERE ebay_archive_product_listing_assoc.product_id = product_master.product_id) as listing_count,
                    sku,
                    type_id,
                    (
                        SELECT
                            AVG(ebay_archive_listing.current_price)
                        FROM ebay_archive_listing
                        INNER JOIN ebay_archive_product_listing_assoc ON (
                            ebay_archive_product_listing_assoc.listing_id = ebay_archive_listing.id AND
                            ebay_archive_product_listing_assoc.product_id = product_master.product_id
                        )
                        WHERE '.$whereSql.' AND
                        ebay_archive_listing.current_price > 0
                    ) as average_bid_price,
                    (
                        SELECT
                            AVG(ebay_archive_listing.buy_it_now_price)
                        FROM ebay_archive_listing
                        INNER JOIN ebay_archive_product_listing_assoc ON (
                            ebay_archive_product_listing_assoc.listing_id = ebay_archive_listing.id AND
                            ebay_archive_product_listing_assoc.product_id = product_master.product_id
                        )
                        WHERE '.$whereSql.' AND
                        ebay_archive_listing.buy_it_now_price > 0
                    ) as average_buyout_price,
                    (
                        SELECT
                            MIN(ebay_archive_listing.current_price)
                        FROM ebay_archive_listing
                        INNER JOIN ebay_archive_product_listing_assoc ON (
                            ebay_archive_product_listing_assoc.listing_id = ebay_archive_listing.id AND
                            ebay_archive_product_listing_assoc.product_id = product_master.product_id
                        )
                        WHERE '.$whereSql.' AND
                        ebay_archive_listing.current_price > 0
                    ) as lowest_bid_price,
                    (
                        SELECT
                            MAX(ebay_archive_listing.current_price)
                        FROM ebay_archive_listing
                        INNER JOIN ebay_archive_product_listing_assoc ON (
                            ebay_archive_product_listing_assoc.listing_id = ebay_archive_listing.id AND
                            ebay_archive_product_listing_assoc.product_id = product_master.product_id
                        )
                        WHERE '.$whereSql.' AND
                        ebay_archive_listing.current_price > 0
                    ) as highest_bid_price,
                    (
                        SELECT
                            MIN(ebay_archive_listing.buy_it_now_price)
                        FROM ebay_archive_listing
                        INNER JOIN ebay_archive_product_listing_assoc ON (
                            ebay_archive_product_listing_assoc.listing_id = ebay_archive_listing.id AND
                            ebay_archive_product_listing_assoc.product_id = product_master.product_id
                        )
                        WHERE '.$whereSql.' AND
                        ebay_archive_listing.current_price > 0
                    ) as lowest_buyout_price,
                    (
                        SELECT
                            MAX(ebay_archive_listing.buy_it_now_price)
                        FROM ebay_archive_listing
                        INNER JOIN ebay_archive_product_listing_assoc ON (
                            ebay_archive_product_listing_assoc.listing_id = ebay_archive_listing.id AND
                            ebay_archive_product_listing_assoc.product_id = product_master.product_id
                        )
                        WHERE '.$whereSql.' AND
                        ebay_archive_listing.current_price > 0
                    ) as highest_buyout_price,
                    round(((
                            SELECT
                                COUNT(ebay_archive_listing.id)
                            FROM ebay_archive_listing
                        INNER JOIN ebay_archive_product_listing_assoc ON (
                            ebay_archive_product_listing_assoc.listing_id = ebay_archive_listing.id AND
                            ebay_archive_product_listing_assoc.product_id = product_master.product_id
                        )
                        WHERE '.$whereSql.' AND
                            ebay_archive_listing.status_id = 2
                        ) / (
                        SELECT
                            COUNT(listing_id)
                        FROM ebay_archive_product_listing_assoc '.$listingCountJoin.'
                        WHERE ebay_archive_product_listing_assoc.product_id = product_master.product_id ) * 100), 1) as sold_percent
                FROM product_master
                '.$joinSql.'
                WHERE product_master.product_id IN (
                    SELECT
                        product_id
                    FROM ebay_archive_product_listing_assoc
                    INNER JOIN ebay_archive_listing ON (
                        ebay_archive_listing.id = ebay_archive_product_listing_assoc.listing_id AND 
                        '.$whereSql.'
                    )
                )

© Stack Overflow or respective owner

Related posts about mysql

Related posts about optimization