Help optimizing a query with 16 subqueries
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.'
)
)
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.'
1) Get rid of duplicate queries. by doing select MIN(...),MAX(...),AVG(...) from ... for both categories.
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.
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 ?
精彩评论