Help optimizing a query with 16 subqueries
- by Webnet
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.'
)
)
