4
\$\begingroup\$

I have a database with these tables, where each mapping table represents a "many-to-one" relationship between the table on the right and the table on the left (click to enlarge):

Database Diagram

I am selecting a single item from product and then adding on all of the other attributes through joins. Here is my current SQL:

SELECT 
    p.id, 
    p.name, 
    p.price, 
    p.description,
    GROUP_CONCAT(DISTINCT c.name SEPARATOR ",") AS categories,
    GROUP_CONCAT(og.options SEPARATOR ";") AS options 
FROM 
    product p 
    LEFT JOIN product_category pc ON p.id = pc.product_id 
    LEFT JOIN category c ON c.id = pc.category_id 
    LEFT JOIN product_optiongroup pog ON p.id = pog.product_id 
    LEFT JOIN (
        SELECT 
            og.id AS id, 
            CONCAT(
                og.name, 
                ":", 
                GROUP_CONCAT(oi.value)
            ) AS options 
        FROM 
            optiongroup og 
            LEFT JOIN optiongroup_optionitem ogoi ON og.id = ogoi.optiongroup_id 
            LEFT JOIN optionitem oi ON oi.id = ogoi.optionitem_id
        GROUP BY og.id
    ) AS og ON og.id = pog.optiongroup_id 
WHERE 
    p.id = 1 -- This is the input

My desired output is something like:

id    name    price    description    categories     options
-------------------------------------------------------------------------------------------
1     Big Hat 10.00    A good hat.    Hats,Clothing  Color:Black,White;Style:Baseball,Sun

My concern is, would this be a terrible pain to maintain? Are there simpler solutions? Also, does it need to be optimized & how? I don't have much experience with SQL. The output format isn't set in stone; I'm working solo.

\$\endgroup\$
  • \$\begingroup\$ Is there a specific reason you are using LEFT JOIN so much? Do you expect products to not have references in those tables a lot? LEFT JOIN are quite expensive and should be used only when needed \$\endgroup\$ – Phrancis Apr 28 '16 at 14:56
  • \$\begingroup\$ @Phrancis I don't expect it to happen a lot, but I do expect it to happen. In particular, there are products with no options. But there aren't any products with no categories. \$\endgroup\$ – 4castle Apr 28 '16 at 14:59
1
\$\begingroup\$

Don't LEFT JOIN unless you really need to

In a comment I asked:

Is there a specific reason you are using LEFT JOIN so much? Do you expect products to not have references in those tables a lot? LEFT JOIN are quite expensive and should be used only when needed – Phrancis

To which you responded:

I don't expect it to happen a lot, but I do expect it to happen. In particular, there are products with no options. But there aren't any products with no categories. – 4castle

That being said, you probably ought to build into your script some checks for that. In plain English, something like this might make sense. You can use if/else blocks to make it work.

  1. Check with only a left join on your options. This should cover a large amount of cases, based on your response.

  2. Should that return no rows for an existing product, then try with more left joins to see if something else might be missing.

  3. Should that still return no rows, or invalid data, throw an error.


Aliases

There are a few points from your code that I think can be improved. For instance FROM product p is not a good alias (consider the other aliases as well), as aliases should at least give a hint as to what they are referring to, and p really doesn't. FROM product AS prod would be better in this case.

Also notice the addition of AS keyword which is good practice. It's less of a big deal with table aliases, but it can cause issues in particular with column aliases if it is missing, so it's really best to just include it.

\$\endgroup\$
  • \$\begingroup\$ Thanks! That's really helpful. I'll see what I can do about reducing the left joins. Would an inner join be better for the categories and such? Are they faster? Also, will that change the ordering of when it looks at WHERE p.id = 1? I would think it should be analyzed first to speed things up, and I guess I thought that was a feature of left join (that it happens last). \$\endgroup\$ – 4castle Apr 28 '16 at 20:47
  • \$\begingroup\$ Inner join is normally faster because it doesn't have to scan the whole index/table and return null if no match is found. As for ordering, you should not rely on implicit ordering and just specify the order in an ORDER BY clause, that is if the order matters. When no order by clause is specified it usually orders by the primary key value, from my experience, but your mileage may vary. \$\endgroup\$ – Phrancis Apr 28 '16 at 21:10
  • \$\begingroup\$ It really depends how the SQL engine decides to optimize it. I think in many cases it will go to the "where" clause to find a value to search for first, but it varies and is somewhat unpredictable what the engine will decide to do when you run a query... \$\endgroup\$ – Phrancis Apr 28 '16 at 21:54
  • \$\begingroup\$ That being said, usually if the value you are searching in your "where" (or "join") clause is referencing an indexed column, that will be really fast on that table, no matter what \$\endgroup\$ – Phrancis Apr 28 '16 at 21:56
  • \$\begingroup\$ Thanks! (Sorry I deleted my comment thinking it was a stupid question, thanks for the help though) \$\endgroup\$ – 4castle Apr 28 '16 at 21:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.