CODEWITHSUNDEEP
mysqlsql
Hakan B.
Hakan B.

Reputation: 2379

How to optimize query containing two identical subqueries except for aggregate function?

I'm trying to write a straightforward query that generates a list of books ranked by two things: most recent review and total number of reviews. To calculate the most recent review date for each book, I use a subquery and the MIN aggregate function. To calculate the total number of reviews for each book, I use the exact same subquery but use the COUNT aggregate function instead.

This seems inefficient. Is there a way to consolidate these subqueries in to one call and still have access to both aggregate values in the HAVING and ORDER BY clauses of the query?

Here is the psuedo-query:

SELECT DISTINCT
    (SELECT MIN(review.pub_date)
        FROM review
        WHERE book.id = review.book_id
        AND ...
        AND ...
        AND ...
    ) AS most_recent_date,
    (SELECT COUNT(review.pub_date)
        FROM review
        WHERE book.id = review.book_id
        AND ...
        AND ...
        AND ...
    ) AS review_count,
    book.id, book.pub_date, book.title, book.author
FROM book
INNER JOIN review
    ON (review.book_id = book.id)
WHERE (
    ...
)
HAVING (most_recent_date > '$DATE')
ORDER BY review_count DESC

I tried consolidating them into one and moving the aggregate functions from the subquery in to the HAVING and ORDER BY clauses, but I was unable to retrieve any results this way.

Thanks in advance.

Upvotes: 1

Views: 145

Answers (1)

Barranka
Barranka

Reputation: 21047

I don't think you need subqueries to do this:

select 
    book.id, book.pub_date, book.title, book.author, 
    min(review.pub_date) as most_recent_date, -- are you sure you want "min"? ("max" makes more sense to me) 
    count(review.pub_date) as review_count
from book
    left join review on book.id = review.book_id
where ... -- Define here any conditions, including those inside the subquery
group by book.id
having most_recent_date > ?
order by review_count desc

Other way to optimize this is to create temp tables with your desired results and then pull the results to your query.

Upvotes: 4

Related Questions