Structure of subqueries in mySQL for optimal performance

Question

In MS SQL Server, this query runs normally:

SELECT column1
    , (SELECT RelatedColumn1 FROM tbl_related WHERE record_id=a1.record_id) AS pseudocolumn1
    , (SELECT RelatedColumn2 FROM tbl_related WHERE record_id=a1.record_id) AS pseudocolumn2
    , (SELECT RelatedColumn3 FROM tbl_related WHERE record_id=a1.record_id) AS pseudocolumn3
    , (SELECT RelatedColumn4 FROM tbl_related WHERE record_id=a1.record_id) AS pseudocolumn4
     ... [20 or more subqueries here] ...
FROM tbl_primary a1

In mySQL, however, this runs TERRIBLY slow--and the performance continues to degrade as you add more subqueries. I'm sure it has to do with the order of operations under the hood, but my question is how to optimize this query? How do I pivot all the values from the second table into columns in the result set? Hoping this doesn't have to be accomplished with multiple JOIN statements (because I don't understand JOINS very well)...?

Answer 1

I would suggest you using a LEFT JOIN to the related table. Because if you use a JOIN then if the tbl_related has no rows for that id then that row will be exclude from the result.

SELECT
    column1,
    tbl_related.RelatedColumn1,
    tbl_related.RelatedColumn2,
    tbl_related.RelatedColumn3,
    tbl_related.RelatedColumn4,
    tbl_related.RelatedColumn5
    .....
FROM
    tbl_primary a1
LEFT JOIN tbl_related 
    ON tbl_related.record_id=a1.record_id

You know:

SELECT
    (SELECT RelatedColumn1 FROM tbl_related WHERE record_id=a1.record_id) 
    AS pseudocolumn1

can cause problems in the future. What happens if one of your sub queries returns more then one value? You will probably get an exception saying that the sub query can not return more the one value. So if you are planing to do sub queries like this in the future. At least have a TOP 1 on them so that the query do not crash. Like this:

SELECT
    (SELECT TOP 1 RelatedColumn1 FROM tbl_related WHERE record_id=a1.record_id) 
    AS pseudocolumn1