CODEWITHSUNDEEP
mysqlsqldatabaseperformancesubquery
Octavient
Octavient

Reputation: 603

Structure of subqueries in mySQL for optimal performance

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)...?

Upvotes: 4

Views: 201

Answers (3)

Darren
Darren

Reputation: 70728

You should be using the JOIN clause 

 SELECT a1.column1,
        rt.RelatedColumn
 FROM tbl_primary a1
 LEFT JOIN tbl_related ON a1.record_id = rt.record_id

Upvotes: 2

Arion
Arion

Reputation: 31239

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

Upvotes: 5

Paul Grimshaw
Paul Grimshaw

Reputation: 21034

SQL 101:

SELECT 
    column1,
    r.RelatedColumn
FROM tbl_primary a1
JOIN tbl_related r
ON r.record_id = a1.recordId

Upvotes: 2

Related Questions