Help optimizing a query with 16 subqueries

Question

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

UPDATE

When I change all of the sub query values to use the same sub query like below I get the following SQL error: Unknown column 'product_master.product_id' in 'on clause

I can only assume this is referring to the sub query pulling all of the AVG, MIN and MAXs because it's the only thing I've changed. Though I'm not sure why it's throwing this error now and wasn't when it's all split up into different values.

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) AS average_bid_price,
                        AVG(ebay_archive_listing.buy_it_now_price) AS average_buyout_price,
                        MIN(ebay_archive_listing.current_price) AS lowest_bid_price,
                        MAX(ebay_archive_listing.current_price) AS highest_bid_price,
                        MIN(ebay_archive_listing.buy_it_now_price) AS lowest_buyout_price,
                        MAX(ebay_archive_listing.buy_it_now_price) AS highest_buyout_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
                ),
                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.'
                )
            )

======================= ORIGINAL BELOW ===========================================

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.'
                    )
                )

Answer 1

I understand your need for a faster query. But you should also read this:

http://en.wikipedia.org/wiki/SQL_injection

As for your query, you should at least:

• avoid correlated subqueries if possible (what to do instead, depends on the amount of data, selectivity of the query, the indexes, and the DBMS optimizer)

• retrieve min(), max() and avg() in the same subquery

• provide a full example, because nobody knows what's inside $whereSql, $listingCountJoin and $joinSql.

For a good book on the subject, I suggest Refactoring SQL Applications.

Answer 2

1) Get rid of duplicate queries. by doing select MIN(...),MAX(...),AVG(...) from ... for both categories.

Answer 3

Whilst I'm not sure what your whereSql might entail, it would seem far easier (as well as quicker) to do this sort of thing using simple joins. Don't resort to subqueries until you really need to.

The trick is to exclude the 0-price columns you don't want using a CASE condition to return NULL (which the aggregate functions will ignore), instead of trying to put that condition in a subquery WHERE clause:

SELECT
    p.product_id, p.sku, p.type_id,
    COUNT(l.listing_id) AS listing_count,
    AVG(CASE WHEN l.current_price>0 THEN l.current_price ELSE NULL END) AS average_bid_price,
    MIN(CASE WHEN l.current_price>0 THEN l.current_price ELSE NULL END) AS lowest_bid_price,
    MAX(CASE WHEN l.current_price>0 THEN l.current_price ELSE NULL END) AS highest_bid_price,
    AVG(CASE WHEN l.buy_it_now_price>0 THEN l.buy_it_now_price ELSE NULL END) AS average_buyout_price,
    MIN(CASE WHEN l.buy_it_now_price>0 THEN l.buy_it_now_price ELSE NULL END) AS lowest_buyout_price,
    MAX(CASE WHEN l.buy_it_now_price>0 THEN l.buy_it_now_price ELSE NULL END) AS highest_buyout_price,
    AVG(CASE WHEN l.status_id=2 THEN 100 ELSE 0 END) AS sold_percent
FROM product_master AS p
    JOIN ebay_archive_product_listing_assoc AS pl ON pl.product_id=p.product_id
    JOIN ebay_archive_listing AS l ON l.listing_id=pl.listing_id
GROUP BY p.product_id
WHERE '.$whereSql.'

Answer 4

Something a bit like this is better

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,
                average_bid_price,     
                average_buyout_price,     
                lowest_bid_price,     
                highest_bid_price,     
                lowest_buyout_price,     
                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 LEFT OUTER JOIN
                (
                    SELECT ebay_archive_product_listing_assoc.product_id ,
                        AVG(ebay_archive_listing.current_price) average_bid_price,
                        AVG(ebay_archive_listing.buy_it_now_price) average_buyout_price,
                        MIN(ebay_archive_listing.current_price) lowest_bid_price,
                        MAX(ebay_archive_listing.current_price) highest_bid_price,
                        MIN(ebay_archive_listing.buy_it_now_price) lowest_buyout_price,
                        MAX(ebay_archive_listing.buy_it_now_price) highest_buyout_price
                    FROM ebay_archive_listing
                    INNER JOIN ebay_archive_product_listing_assoc ON (
                        ebay_archive_product_listing_assoc.listing_id = ebay_archive_listing.id 
                    )
                    WHERE '.$whereSql.' AND
                    ebay_archive_listing.current_price > 0
                    GROUP BY ebay_archive_product_listing_assoc.product_id
                ) eal ON eal.product_id = product_master.product_id
            '.$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.'
                )
            )

But a lot more can be done, what are the values of whereSql and joinSql ?