Does this query look optimized?

Question

I'm writing a query for an application that needs to list all the products with the number of times they have been purchased.

I came up with this and it works, but I am not too sure how optimized it is. My SQL is really rusty due to my heavy usage of ORM's, But in this case a query is a much more elegant solution.

Can you spot anything wrong (approach wise) with the query?

---

SELECT  products.id, 
        products.long_name AS name, 
        count(oi.order_id) AS sold
FROM    products
LEFT OUTER JOIN 
      ( SELECT * FROM orderitems
        INNER JOIN orders ON orderitems.order_id = orders.id 
        AND orders.paid = 1 ) AS oi 
      ON oi.product_id = products.id
GROUP BY products.id

---

The schema (with relevant fields) looks like this:

*orders*      id, paid
*orderitems*  order_id, product_id
*products*    id

Thanks

UPDATE

This is for MySQL

Answer 1

Does it give you the right answer?

Except for just modifying it to get rid of the SELECT in the inner query, I don't see anything wrong with it.

Answer 2

Well you have "LEFT OUTER JOIN" that can be a performance issue depending on your Database. Last time I remember it caused hell on MySQL, and it doesn't exist in SQLite. I think Oracle can handle it ok, and I guess DB and MSSQL too.

EDIT: If I remember correctly LEFT OUTER JOIN can be orders of magnitude slower on MySQL, but please correct me if I'm outdated here :)

Answer 3

Untested code, but try it:

SELECT  products.id,
    MIN(products.long_name) AS name, 
    count(oi.order_id) AS sold
FROM    (products
LEFT OUTER JOIN orderitemss AS oi ON oi.product_id = products.id)
INNER JOIN orders AS o ON oi.order_id = o.id 
WHERE orders.paid = 1
GROUP BY products.id

I don't know if the parentheses are needed for the LEFT OUTER JOIN, neither if MySQL allows multiple joins, however the MIN(products.long_name) gives just the description, since for every products.id you have only one description.

Perhaps the parentheses need to be around the INNER JOIN.

Answer 4

I'm not sure about the "(SELECT *" ... business.

This executes (always a good start) and I think is equivalent to what was posted.

SELECT  products.id, 
    products.long_name AS name, 
    count(oi.order_id) AS sold
FROM    products
LEFT OUTER JOIN
    orderitems AS oi
        INNER JOIN 
            orders 
            ON oi.order_id = orders.id AND orders.paid = 1
    ON oi.product_id = products.id
GROUP BY products.id

Answer 5

Here a solution for those of us who are nesting impaired. (I get so confused when I start nesting joins)

SELECT  products.id, 
    products.long_name AS name, 
    count(oi.order_id) AS sold
FROM orders 
    INNER JOIN orderitems  AS oi ON oi.order_id = orders.id AND orders.paid = 1
    RIGHT JOIN products ON oi.product_id = products.id
GROUP BY products.id

However, I tested your solution, Mike's and mine on MS SQL Server and the query plans are identical. I can't speak for MySql but if MS SQL Server is anything to go by, you may find the performance of all three solutions equivalent. If that is the case I guess you pick which solution is clearest to you.

Answer 6

Here's a subquery form.

SELECT
  p.id,
  p.long_name AS name,
  (SELECT COUNT(*) FROM OrderItems oi WHERE oi.order_id in
    (SELECT o.id FROM Orders o WHERE o.Paid = 1 AND o.Product_id = p.id)
  ) as sold
FROM Products p

It should perform roughly equivalent to the join form. If it doesn't, let me know.